在EggJS中使用Sequelize做联表查询

在EggJS中使用Sequelize做联表查询先在app目录下新建一个目录为model,里面用来存放所有的数据库里面定义的表的实例对象内容。 在该项目中,student表和info表是存在一对一关系的,一个学生有一条专属信息。 classes与student是一对多的关系,一个班级有多个学生,多个学生组成一个班级。 在st…

【转发请注明出处】 个人博客地址:小猴的代码笔记

1.EggJS引用Sequelize

  1. 安装sequelize依赖和mysql驱动

    cnpm i egg-sequelize mysql2 -S
    
  2. 启用sequelize插件

    在config/plugin.js里面添加

    sequelize: {
        enable: true,
        package: 'egg-sequelize',
    },
    
  3. 配置数据库

    在config/config.default.js里面添加

    config.sequelize = {
        dialect: 'mysql',  // 表示使用mysql
        host: '127.0.0.1', // 连接的数据库主机地址
        port: 3306, // mysql服务端口
        database: 'demo', // 数据库名
        username: 'root',  // 数据库用户名
        password: 'root', // 数据库密码
        define: {  // model的全局配置
          	timestamps: true,   // 添加create,update,delete时间戳
          	paranoid: true,   // 添加软删除
          	freezeTableName: true,  // 防止修改表名为复数
          	underscored: false  // 防止驼峰式字段被默认转为下划线
        },
        timezone: '+8:00',  // 由于orm用的UTC时间,这里必须加上东八区,否则取出来的时间相差8小时
        dialectOptions: {  // 让读取date类型数据时返回字符串而不是UTC时间
            dateStrings: true,
            typeCast(field, next) {
                if (field.type === "DATETIME") {
                    return field.string();
                }
                return next();
            }
        }
    };
    

2.定义Model

  1. 刚开始使用egg-init构建的Egg项目是没有app/model目录的,初始的项目结构如下:

    itzishu
    ├── README.md
    ├── app
    │   ├── controller
    │   │   └── home.js
    │   └── router.js
    ├── appveyor.yml
    ├── config
    │   ├── config.default.js
    │   └── plugin.js
    ├── package.json
    └── test
        └── app
            └── controller
                └── home.test.js
    

    先在app目录下新建一个目录为model,里面用来存放所有的数据库里面定义的表的实例对象内容。

  2. 数据库表的内容如下:

    /* Navicat Premium Data Transfer Source Server : 系统数据库3306 Source Server Type : MySQL Source Server Version : 50725 Source Host : localhost:3306 Source Schema : demo Target Server Type : MySQL Target Server Version : 50725 File Encoding : 65001 Date: 12/05/2019 15:11:37 */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for classes
    -- ----------------------------
    DROP TABLE IF EXISTS `classes`;
    CREATE TABLE `classes` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      `createdAt` datetime DEFAULT NULL,
      `updatedAt` datetime DEFAULT NULL,
      `deletedAt` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of classes
    -- ----------------------------
    BEGIN;
    INSERT INTO `classes` VALUES (1, '软件工程1601', '2019-05-12 13:11:43', '2019-05-12 13:11:47', NULL);
    INSERT INTO `classes` VALUES (2, '网络工程1601', '2019-05-12 13:12:10', '2019-05-12 13:12:13', NULL);
    COMMIT;
    
    -- ----------------------------
    -- Table structure for info
    -- ----------------------------
    DROP TABLE IF EXISTS `info`;
    CREATE TABLE `info` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      `age` int(11) NOT NULL,
      `sex` tinyint(255) NOT NULL DEFAULT '1' COMMENT '1为男,0为女',
      `studentId` int(11) NOT NULL,
      `createdAt` datetime DEFAULT NULL,
      `updatedAt` datetime DEFAULT NULL,
      `deletedAt` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of info
    -- ----------------------------
    BEGIN;
    INSERT INTO `info` VALUES (1, '许仙', 23, 1, 1, '2019-05-12 13:25:58', '2019-05-12 13:26:01', NULL);
    INSERT INTO `info` VALUES (2, '白素贞', 20, 0, 2, '2019-05-12 13:26:41', '2019-05-12 13:26:46', NULL);
    INSERT INTO `info` VALUES (3, '法海', 22, 1, 3, '2019-05-12 13:27:20', '2019-05-12 13:27:22', NULL);
    INSERT INTO `info` VALUES (4, '小青', 18, 0, 4, '2019-05-12 13:27:48', '2019-05-12 13:27:51', NULL);
    INSERT INTO `info` VALUES (5, '金如意', 20, 0, 5, '2019-05-12 13:28:34', '2019-05-12 13:28:37', NULL);
    INSERT INTO `info` VALUES (6, '景松', 23, 1, 6, '2019-05-12 13:30:07', '2019-05-12 13:30:10', NULL);
    COMMIT;
    
    -- ----------------------------
    -- Table structure for lession
    -- ----------------------------
    DROP TABLE IF EXISTS `lession`;
    CREATE TABLE `lession` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      `createdAt` datetime DEFAULT NULL,
      `updatedAt` datetime DEFAULT NULL,
      `deletedAt` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of lession
    -- ----------------------------
    BEGIN;
    INSERT INTO `lession` VALUES (1, '计算机网络', '2019-05-12 13:12:32', '2019-05-12 13:12:35', NULL);
    INSERT INTO `lession` VALUES (2, 'Java程序设计', '2019-05-12 13:12:50', '2019-05-12 13:12:52', NULL);
    INSERT INTO `lession` VALUES (3, '软件项目管理', '2019-05-12 13:13:07', '2019-05-12 13:13:10', NULL);
    INSERT INTO `lession` VALUES (4, '网络安全', '2019-05-12 13:13:22', '2019-05-12 13:13:25', NULL);
    COMMIT;
    
    -- ----------------------------
    -- Table structure for lession_student
    -- ----------------------------
    DROP TABLE IF EXISTS `lession_student`;
    CREATE TABLE `lession_student` (
      `lessionId` int(11) NOT NULL,
      `studentId` int(11) NOT NULL,
      `createdAt` datetime DEFAULT NULL,
      `updatedAt` datetime DEFAULT NULL,
      `deletedAt` datetime DEFAULT NULL,
      PRIMARY KEY (`lessionId`,`studentId`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of lession_student
    -- ----------------------------
    BEGIN;
    INSERT INTO `lession_student` VALUES (1, 1, '2019-05-12 13:20:35', '2019-05-12 13:20:40', NULL);
    INSERT INTO `lession_student` VALUES (1, 2, '2019-05-12 13:20:51', '2019-05-12 13:20:53', NULL);
    INSERT INTO `lession_student` VALUES (1, 3, '2019-05-12 13:21:02', '2019-05-12 13:21:05', NULL);
    INSERT INTO `lession_student` VALUES (1, 4, '2019-05-12 13:21:15', '2019-05-12 13:21:19', NULL);
    INSERT INTO `lession_student` VALUES (1, 5, '2019-05-12 13:21:29', '2019-05-12 13:21:32', NULL);
    INSERT INTO `lession_student` VALUES (1, 6, '2019-05-12 13:21:43', '2019-05-12 13:21:45', NULL);
    INSERT INTO `lession_student` VALUES (2, 1, '2019-05-12 13:23:10', '2019-05-12 13:23:13', NULL);
    INSERT INTO `lession_student` VALUES (2, 3, '2019-05-12 13:23:28', '2019-05-12 13:23:31', NULL);
    INSERT INTO `lession_student` VALUES (2, 4, '2019-05-12 13:23:40', '2019-05-12 13:23:43', NULL);
    INSERT INTO `lession_student` VALUES (2, 5, '2019-05-12 13:23:54', '2019-05-12 13:23:57', NULL);
    INSERT INTO `lession_student` VALUES (3, 1, '2019-05-12 13:24:21', '2019-05-12 13:24:24', NULL);
    INSERT INTO `lession_student` VALUES (3, 4, '2019-05-12 13:24:39', '2019-05-12 13:24:42', NULL);
    INSERT INTO `lession_student` VALUES (4, 2, '2019-05-12 13:24:59', '2019-05-12 13:25:03', NULL);
    INSERT INTO `lession_student` VALUES (4, 6, '2019-05-12 13:25:12', '2019-05-12 13:25:15', NULL);
    COMMIT;
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `number` varchar(12) NOT NULL COMMENT '学号',
      `password` varchar(32) NOT NULL,
      `classId` int(11) NOT NULL,
      `createdAt` datetime DEFAULT NULL,
      `updatedAt` datetime DEFAULT NULL,
      `deletedAt` datetime DEFAULT NULL,
      PRIMARY KEY (`id`,`number`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    BEGIN;
    INSERT INTO `student` VALUES (1, '160101', '202cb962ac59075b964b07152d234b70', 1, '2019-05-12 13:16:09', '2019-05-12 13:16:12', NULL);
    INSERT INTO `student` VALUES (2, '160201', '202cb962ac59075b964b07152d234b70', 2, '2019-05-12 13:16:32', '2019-05-12 13:16:35', NULL);
    INSERT INTO `student` VALUES (3, '160102', '202cb962ac59075b964b07152d234b70', 1, '2019-05-12 13:17:17', '2019-05-12 13:17:21', NULL);
    INSERT INTO `student` VALUES (4, '160103', '202cb962ac59075b964b07152d234b70', 1, '2019-05-12 13:17:51', '2019-05-12 13:17:54', NULL);
    INSERT INTO `student` VALUES (5, '160104', '202cb962ac59075b964b07152d234b70', 1, '2019-05-12 13:18:13', '2019-05-12 13:18:16', NULL);
    INSERT INTO `student` VALUES (6, '160202', '202cb962ac59075b964b07152d234b70', 2, '2019-05-12 13:18:36', '2019-05-12 13:18:39', NULL);
    COMMIT;
    
    SET FOREIGN_KEY_CHECKS = 1;
    
    

    其中,各个表之间存在联系为:

    • student与info存在一对一关系
    • classes与student存在一对多关系
    • student与lession存在多对多关系,中间表为lession_student
  3. 根据数据表的结构,我们确定关系并写好model目录下相关文件

    • student.js
    module.exports = app => {
        const { STRING, INTEGER } = app.Sequelize;
    
        const Student = app.model.define('student', {
            id: {
                type: INTEGER,
                autoIncrement: true,
                primaryKey: true
            },
            number: {
                type: STRING,
                allowNull: false,
            },
            password: {
                type: STRING(32),
                allowNull: false
            },
            classId: {
                type: INTEGER,
                allowNull: false
            }
        });
    
        Student.associate = function (){
            // 与Info存在一对多关系,所以是hasOne()
            app.model.Student.hasOne(app.model.Info, {foreignKey: 'studentId'});
            // 与Classes存在多对一关系,所以使用belongsTo()
            app.model.Student.belongsTo(app.model.Classes, {foreignKey: 'classId', targetKey: 'id'});
            // 与Lessison存在多对多关系,使用belongsToMany()
            app.model.Student.belongsToMany(app.model.Lession, {
                through: app.model.LessionStudent,
                foreignKey: 'studentId',
                otherKey: 'lessionId'
            });
        }
    
        return Student;
    }
    
    • info.js
    module.exports = app => {
        const { STRING, INTEGER, BOOLEAN } = app.Sequelize;
    
        const Info = app.model.define('info', {
            id: {
                type: INTEGER,
                autoIncrement: true,
                primaryKey: true
            },
            name: {
                type: STRING(50),
                allowNull: false,
            },
            age: {
                type: INTEGER,
                allowNull: false
            },
            sex: {
                type: BOOLEAN,
                allowNull: false,
                get() {
                    if ( this.getDataValue('sex') ){
                        return '男';
                    }else {
                        return '女';
                    }
                }
            },
            studentId: {
                type: INTEGER,
                allowNull: false
            }
        });
    
        Info.associate = function (){
            app.model.Info.belongsTo(app.model.Student, {foreignKey: 'studentId', targetKey: 'id'});
        }
    
        return Info;
    }
    

    这里注意下,在sex字段中,有一个get(){}方法,因为在数据表里面,sex字段存了1或0 ,1为男0为女,为了直接返回”男”或”女”,这里使用get方法在找到数据后先做了处理,那返回给调用的函数的数据就是我们设置的值

    • classes.js
    module.exports = app => {
        const { STRING, INTEGER, BOOLEAN } = app.Sequelize;
    
        const Classes = app.model.define('classes', {
            id: {
                type: INTEGER,
                autoIncrement: true,
                primaryKey: true
            },
            name: {
                type: STRING(50),
                allowNull: false,
            },
            age: {
                type: INTEGER,
                allowNull: false
            },
            sex: {
                type: BOOLEAN,
                allowNull: false,
                get() {
                    if ( this.getDataValue('sex') ){
                        return '男';
                    }else {
                        return '女';
                    }
                }
            },
            studentId: {
                type: INTEGER,
                allowNull: false
            }
        });
    
        Classes.associate = function (){
            // classes与student是一对多关系,所以这里使用hasMany()
            app.model.Classes.hasMany(app.model.Student, {foreignKey: 'classId', targetKey: 'id'});
        }
    
        return Classes;
    }
    
    • lession.js
    module.exports = app => {
        const { INTEGER, STRING } = app.Sequelize;
    
        const Lession = app.model.define('lession', {
            id: {
                type: INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            name: {
                type: STRING,
                allowNull: false
            }
        });
    
        Lession.associate = function(){
            // 与student表是多对多关系
            app.model.Lession.belongsToMany(app.model.Student, {
                through: app.model.LessionStudent,
                foreignKey: 'lessionId',
                otherKey: 'studentId'
            });
        }
    
        return Lession;
    }
    
    • lession-student.js
    module.exports = app => {
        const { INTEGER } = app.Sequelize;
    
        const LessionStudent = app.model.define('lession_student', {
            lessionId: {
                type: INTEGER,
                primaryKey: true
            },
            studentId: {
                type: INTEGER,
                primaryKey: true
            }
        });
    
        LessionStudent.associate = function(){
    
        }
    
        return LessionStudent;
    }
    
  4. 总结一下Model定义的内容

    • 针对MYSQL常用的字段类型

    字段类型从 app.Sequelize 获取,对应名字如下

    Sequelize.STRING                      // VARCHAR(255)
    Sequelize.STRING(1234)                // VARCHAR(1234)
    Sequelize.STRING.BINARY               // VARCHAR BINARY
    Sequelize.TEXT                        // TEXT
    Sequelize.TEXT('tiny')                // TINYTEXT
    
    Sequelize.INTEGER                     // INTEGER
    Sequelize.BIGINT                      // BIGINT
    Sequelize.BIGINT(11)                  // BIGINT(11)
    
    Sequelize.FLOAT                       // FLOAT
    Sequelize.FLOAT(11)                   // FLOAT(11)
    Sequelize.FLOAT(11, 12)               // FLOAT(11,12)
    
    Sequelize.DOUBLE                      // DOUBLE
    Sequelize.DOUBLE(11)                  // DOUBLE(11)
    Sequelize.DOUBLE(11, 12)              // DOUBLE(11,12)
    
    Sequelize.DECIMAL                     // DECIMAL
    Sequelize.DECIMAL(10, 2)              // DECIMAL(10,2)
    
    Sequelize.DATE                        // DATETIME 针对 mysql / sqlite, TIMESTAMP WITH TIME ZONE 针对 postgres
    Sequelize.DATE(6)                     // DATETIME(6) 针对 mysql 5.6.4+. 小数秒支持多达6位精度
    Sequelize.DATEONLY                    // DATE 不带时间.
    Sequelize.BOOLEAN                     // TINYINT(1)
    

    其他的数据库所允许的类型参考:数据类型

    • 字段属性值
    属性名 类型 默认值 说明 说明
    type Any 数据类型
    primaryKey Boolean false 主键
    autoIncrement Boolean false 自增
    allowNull Boolean false 是否允许为空
    defaultValue Any 默认值
    field String 字段名 自定义字段名
    unique Any 约束
    • 表与表的关联性

    在sequelize中,表与表之间用代码来说存在三种关联关系:一对一,一对多,多对多

    1. 一对一

      在该项目中,student表和info表是存在一对一关系的,一个学生有一条专属信息。

      在student.js中,使用了hasOne()方法,第一个参数为关联的模型对象Info,第二个参数为一个对象,其包含一个属性为foreginKey为对应的信息表中studentId字段

      在info.js中,使用了belongsTo()方法,第一个参数为关联的模型对象Student, 第二个参数也是一个对象,其有两个属性,foreginKey为info表中的”studentId”字段,第二个参数targetKey为student表中的”id”字段

      总结: hasOne()和belongsTo()第一个参数为本表关联的另外一个表的Model实例,第二个参数中,都有foreginKey属性,对hasOne来说,这个属性值是对方表与自己Id对应的字段,对belongsTo来说,这个属性值是本表上的与对方表id对应的字段名。belongsTo比hasOne多了个targetKey属性,其为对方表的对应主键名

    2. 一对多

      classes与student是一对多的关系,一个班级有多个学生,多个学生组成一个班级。

      在student.js中,使用了belongsTo(),在classes.js中,使用了hasMany(),发现hasMany()与belongsTo()所需要的参数是类似的,但是这里注意,hasMany()里面的foreginKey值是对方表的classesId。结合第上面”一对一”的分析,我们可以总结出:

      has开头的方法中,foreginKey属性值从对方的表上找,如果有targetKey的值则是自己的主键;

      belongs开头的方法中,foreginKey属性值在自身表上找,targetKey属性值则是对方表上

    3. 多对多

      分析多对多关系,一个学生有多门课,一个课有多个学生,那我们可以用一个中间表lession-student.js做这个联系。

      在student.js中,我们使用了belongsToMany()方法,lession.js文件中也是如此,通过该方法的参数内容,可以发现其多了一个through属性,其值是中间表的Model实例。根据上面的规律,belongs开头的方法里面foreginKey找自己,otherKey找其他,所以很容易理解。

    总结: 在Model的实例里面,重写Model的associate方法,将关联的关系放到里面。

    一对一的方法有:hasOne(Model, {foreignKey:对方,})belongsTo(Model,{foreignKey:自己,targetKey:对方})

    一对多的方法有: hasMany(Model,{foreignKey:对方, targetKey:自己})belongsTo(Model,{foreignKey:自己,targetKey:对方})

    多对多的方法有: belongsToMany(Model,{through:Model, targetKey:自己, otherKey:对方})

3.联表查询

  • 一对一

    在controller里面如下写

    // 获取学生信息 通过一对多的联系
        async info(){
            const { ctx, app } = this;
            let result = await app.model.Student.findAll({
              include: {
                model: app.model.Info
              }
            });
            ctx.body = result;
        }
    

    获取到的值如下:

    [
    		// 第一个学生
        {
            "id": 1,
            "number": "160101",
            "password": "202cb962ac59075b964b07152d234b70",
            "classId": 1,
            "createdAt": "2019-05-12 13:16:09",
            "updatedAt": "2019-05-12 13:16:12",
            "deletedAt": null,
            "info": {  // 联表查到的信息
                "sex": "男",
                "id": 1,
                "name": "许仙",
                "age": 23,
                "studentId": 1,
                "createdAt": "2019-05-12 13:25:58",
                "updatedAt": "2019-05-12 13:26:01",
                "deletedAt": null
            }
        },
      	// 第二个学生
        {
            "id": 2,
            "number": "160201",
            "password": "202cb962ac59075b964b07152d234b70",
            "classId": 2,
            "createdAt": "2019-05-12 13:16:32",
            "updatedAt": "2019-05-12 13:16:35",
            "deletedAt": null,
            "info": {
                "sex": "女",
                "id": 2,
                "name": "白素贞",
                "age": 20,
                "studentId": 2,
                "createdAt": "2019-05-12 13:26:41",
                "updatedAt": "2019-05-12 13:26:46",
                "deletedAt": null
            }
        },
        {
            "id": 3,
            "number": "160102",
            "password": "202cb962ac59075b964b07152d234b70",
            "classId": 1,
            "createdAt": "2019-05-12 13:17:17",
            "updatedAt": "2019-05-12 13:17:21",
            "deletedAt": null,
            "info": {
                "sex": "男",
                "id": 3,
                "name": "法海",
                "age": 22,
                "studentId": 3,
                "createdAt": "2019-05-12 13:27:20",
                "updatedAt": "2019-05-12 13:27:22",
                "deletedAt": null
            }
        },
        {
            "id": 4,
            "number": "160103",
            "password": "202cb962ac59075b964b07152d234b70",
            "classId": 1,
            "createdAt": "2019-05-12 13:17:51",
            "updatedAt": "2019-05-12 13:17:54",
            "deletedAt": null,
            "info": {
                "sex": "女",
                "id": 4,
                "name": "小青",
                "age": 18,
                "studentId": 4,
                "createdAt": "2019-05-12 13:27:48",
                "updatedAt": "2019-05-12 13:27:51",
                "deletedAt": null
            }
        },
        {
            "id": 5,
            "number": "160104",
            "password": "202cb962ac59075b964b07152d234b70",
            "classId": 1,
            "createdAt": "2019-05-12 13:18:13",
            "updatedAt": "2019-05-12 13:18:16",
            "deletedAt": null,
            "info": {
                "sex": "女",
                "id": 5,
                "name": "金如意",
                "age": 20,
                "studentId": 5,
                "createdAt": "2019-05-12 13:28:34",
                "updatedAt": "2019-05-12 13:28:37",
                "deletedAt": null
            }
        },
        {
            "id": 6,
            "number": "160202",
            "password": "202cb962ac59075b964b07152d234b70",
            "classId": 2,
            "createdAt": "2019-05-12 13:18:36",
            "updatedAt": "2019-05-12 13:18:39",
            "deletedAt": null,
            "info": {
                "sex": "男",
                "id": 6,
                "name": "景松",
                "age": 23,
                "studentId": 6,
                "createdAt": "2019-05-12 13:30:07",
                "updatedAt": "2019-05-12 13:30:10",
                "deletedAt": null
            }
        }
    ]
    
  • 一对多

    // 获取班级名为 软件工程1601 的班级学生
        async student(){
          const { ctx, app } = this;
          let result = await app.model.Classes.findAll({
            where: {
              name: '软件工程1601'
            },
            include: {
              model: app.model.Student
            }
          })
          ctx.body = result;
        }
    

    获取数据如下:

    [
        {
            "id": 1,
            "name": "软件工程1601",
            "createdAt": "2019-05-12 13:11:43",
            "updatedAt": "2019-05-12 13:11:47",
            "deletedAt": null,
            "students": [
                {
                    "id": 1,
                    "number": "160101",
                    "password": "202cb962ac59075b964b07152d234b70",
                    "classId": 1,
                    "createdAt": "2019-05-12 13:16:09",
                    "updatedAt": "2019-05-12 13:16:12",
                    "deletedAt": null
                },
                {
                    "id": 3,
                    "number": "160102",
                    "password": "202cb962ac59075b964b07152d234b70",
                    "classId": 1,
                    "createdAt": "2019-05-12 13:17:17",
                    "updatedAt": "2019-05-12 13:17:21",
                    "deletedAt": null
                },
                {
                    "id": 4,
                    "number": "160103",
                    "password": "202cb962ac59075b964b07152d234b70",
                    "classId": 1,
                    "createdAt": "2019-05-12 13:17:51",
                    "updatedAt": "2019-05-12 13:17:54",
                    "deletedAt": null
                },
                {
                    "id": 5,
                    "number": "160104",
                    "password": "202cb962ac59075b964b07152d234b70",
                    "classId": 1,
                    "createdAt": "2019-05-12 13:18:13",
                    "updatedAt": "2019-05-12 13:18:16",
                    "deletedAt": null
                }
            ]
        }
    ]
    
  • 多对多

    从学生获取课程信息

    // 获取学生的选课内容
        async lession(){
          const { ctx, app } = this;
          let result = await app.model.Student.findAll({
            where:{
              id: 1,
            },
            include: [
              {model: app.model.Info},
              {model: app.model.Lession}
            ]
          });
          ctx.body = result;
        }
    

    这里的话,注意include的值为一个数组了,这样可以多个联表获取数据

    数据如下:

    [
        {
            "id": 1,
            "number": "160101",
            "password": "202cb962ac59075b964b07152d234b70",
            "classId": 1,
            "createdAt": "2019-05-12 13:16:09",
            "updatedAt": "2019-05-12 13:16:12",
            "deletedAt": null,
            "info": {
                "sex": "男",
                "id": 1,
                "name": "许仙",
                "age": 23,
                "studentId": 1,
                "createdAt": "2019-05-12 13:25:58",
                "updatedAt": "2019-05-12 13:26:01",
                "deletedAt": null
            },
            "lessions": [
                {
                    "id": 1,
                    "name": "计算机网络",
                    "createdAt": "2019-05-12 13:12:32",
                    "updatedAt": "2019-05-12 13:12:35",
                    "deletedAt": null,
                    "lession_student": {
                        "lessionId": 1,
                        "studentId": 1,
                        "createdAt": "2019-05-12 13:20:35",
                        "updatedAt": "2019-05-12 13:20:40",
                        "deletedAt": null
                    }
                },
                {
                    "id": 2,
                    "name": "Java程序设计",
                    "createdAt": "2019-05-12 13:12:50",
                    "updatedAt": "2019-05-12 13:12:52",
                    "deletedAt": null,
                    "lession_student": {
                        "lessionId": 2,
                        "studentId": 1,
                        "createdAt": "2019-05-12 13:23:10",
                        "updatedAt": "2019-05-12 13:23:13",
                        "deletedAt": null
                    }
                },
                {
                    "id": 3,
                    "name": "软件项目管理",
                    "createdAt": "2019-05-12 13:13:07",
                    "updatedAt": "2019-05-12 13:13:10",
                    "deletedAt": null,
                    "lession_student": {
                        "lessionId": 3,
                        "studentId": 1,
                        "createdAt": "2019-05-12 13:24:21",
                        "updatedAt": "2019-05-12 13:24:24",
                        "deletedAt": null
                    }
                }
            ]
        }
    ]
    

    从课程获取选课学生:

    // 获取某个课的参课学生
        async lessionStudent(){
          const { ctx, app } = this;
          let result = await app.model.Lession.findAll({
            where:{
              name: '网络安全'
            },
            include: {
              model: app.model.Student,
              include: {
                model: app.model.Info
              }
            }
          });
          ctx.body = result;
        }
    

    这里注意,在include的下面又有一个include,第二个include是相对Student表的

    数据如下:

    [
        {
            "id": 4,
            "name": "网络安全",
            "createdAt": "2019-05-12 13:13:22",
            "updatedAt": "2019-05-12 13:13:25",
            "deletedAt": null,
            "students": [
                {
                    "id": 2,
                    "number": "160201",
                    "password": "202cb962ac59075b964b07152d234b70",
                    "classId": 2,
                    "createdAt": "2019-05-12 13:16:32",
                    "updatedAt": "2019-05-12 13:16:35",
                    "deletedAt": null,
                    "lession_student": {
                        "lessionId": 4,
                        "studentId": 2,
                        "createdAt": "2019-05-12 13:24:59",
                        "updatedAt": "2019-05-12 13:25:03",
                        "deletedAt": null
                    },
                    "info": {
                        "sex": "女",
                        "id": 2,
                        "name": "白素贞",
                        "age": 20,
                        "studentId": 2,
                        "createdAt": "2019-05-12 13:26:41",
                        "updatedAt": "2019-05-12 13:26:46",
                        "deletedAt": null
                    }
                },
                {
                    "id": 6,
                    "number": "160202",
                    "password": "202cb962ac59075b964b07152d234b70",
                    "classId": 2,
                    "createdAt": "2019-05-12 13:18:36",
                    "updatedAt": "2019-05-12 13:18:39",
                    "deletedAt": null,
                    "lession_student": {
                        "lessionId": 4,
                        "studentId": 6,
                        "createdAt": "2019-05-12 13:25:12",
                        "updatedAt": "2019-05-12 13:25:15",
                        "deletedAt": null
                    },
                    "info": {
                        "sex": "男",
                        "id": 6,
                        "name": "景松",
                        "age": 23,
                        "studentId": 6,
                        "createdAt": "2019-05-12 13:30:07",
                        "updatedAt": "2019-05-12 13:30:10",
                        "deletedAt": null
                    }
                }
            ]
        }
    ]
    

4. 总结

用时4小时,调试加数据库设置,代码编写,查文档。允许我偷个懒,不想总结了,仔细阅读内容,基本上可以了解Sequelize在联表查询上的基本用法了

今天的文章在EggJS中使用Sequelize做联表查询分享到此就结束了,感谢您的阅读。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/17317.html

(0)
编程小号编程小号

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注