在EggJS中使用Sequelize做联表查询

最新推荐文章于 2021-07-21 15:20:48 发布

IT 哈

最新推荐文章于 2021-07-21 15:20:48 发布

阅读量354

点赞数

分类专栏：前端

原文链接：https://www.jianshu.com/p/078087c69b77

版权

前端专栏收录该内容

268 篇文章 9 订阅

订阅专栏

1.EggJS引用Sequelize

安装sequelize依赖和mysql驱动

cnpm i egg-sequelize mysql2 -S

启用sequelize插件

在config/plugin.js里面添加

sequelize: {
    enable: true,
    package: 'egg-sequelize',
},

配置数据库

在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

刚开始使用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，里面用来存放所有的数据库里面定义的表的实例对象内容。

数据库表的内容如下：

/*
 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

根据数据表的结构，我们确定关系并写好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;
}

总结一下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)

其他的数据库所允许的类型参考：[数据类型](https://link.juejin.im?target=%255Bhttps%3A%2F%2Fdemopark.github.io%2Fsequelize-docs-Zh-CN%2Fmodels-definition.html%23%25E6%2595%25B0%25E6%258D%25AE%25E7%25B1%25BB%25E5%259E%258B%255D(https%3A%2F%2Fdemopark.github.io%2Fsequelize-docs-Zh-CN%2Fmodels-definition.html%23%25E6%2595%25B0%25E6%258D%25AE%25E7%25B1%25BB%25E5%259E%258B))

字段属性值

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

表与表的关联性

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

一对一

在该项目中，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属性，其为对方表的对应主键名

一对多

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

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

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

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

多对多

分析多对多关系，一个学生有多门课，一个课有多个学生，那我们可以用一个中间表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在联表查询上的基本用法了

转自https://www.jianshu.com/p/078087c69b77

IT 哈

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
在EggJS中使用Sequelize做联表查询

1.EggJS引用Sequelize安装sequelize依赖和mysql驱动cnpm i egg-sequelize mysql2 -S启用sequelize插件在config/plugin.js里面添加sequelize: { enable: true, package: 'egg-sequelize',},配置数据库在config/config.default.js里面添加 config.sequelize = { dialect: 'm
复制链接

扫一扫