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.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;
    
       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)
    

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

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

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

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

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

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

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

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

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

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

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

    "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 "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;
                    "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在联表查询上的基本用法了

    2020.3.24补充

    1. 模型(表)之间的关联关系

    1.1 模型关系概述

    数据库中的表之间存在一定的关联关系,表之间的关系基于主/外键进行关联、创建约束等。关系表中的数据分为1对1(1:1)、1对多(1:M)、多对多(N:M)三种关联关系。

    Sequelize中建立关联关系,通过调用模型(源模型)的belongsTohasOnehasManybelongsToMany方法,再将要建立关系的模型(目标模型)做为参数传入即可。这些方法会按以下规则创建关联关系:

  • hasOne - 与目标模型建立1:1关联关系,关联关系(外键)存在于目标模型中。详见:Model.hasOne()
  • belongsTo - 与目标模型建立1:1关联关系,关联关系(外键)存在于源模型中。详见:Model.belongsTo()
  • hasMany - 与目标模型建立1:N关联关系,关联关系(外键)存在于目标模型中。详见:Model.hasMany()
  • belongsToMany - 与目标模型建立N:M关联关系,会通过sourceIdtargetId创建交叉表。详见:Model.belongsToMany()
  • 1.2 定义关系模型

    为了能够清楚说明模型关系的定义及关系模型的使用,我们定义如下4个模型对象:

  • 用户(User)-与其它模型存在1:11:NN:M
  • 用户登录信息(UserCheckin)-与User存在1:1关系
  • 用户地址(UserAddress)-与User存在N:1关系
  • 角色(Role)-与User存在N:M关系
  • 这几个模型的E-R结构如下:

    2. 关系/关联相关的API

    2.1 综合介绍

    在Sequelize中创建关联通过调用模型()的 belongsTo / hasOne / hasMany / belongsToMany方法完成,并且为这个方法第一个参数提供另一个模型(目标)。各种方法以下规则创建关联:

  • hasOne - 添加外键到目标模型,并以单数关系混入到源模型
  • belongsTo - 为当前模型添加外键,并以单数关系混入到源模型
  • hasMany - 添加外键到目标模型,并以复数关系混入到源模型
  • belongsToMany - 为连接的表创建N:M关系并以复数关系混入到源模型。会通过sourceIdtargetId创建交叉表。
  • 在创建关系时,可以通过as选项指定别名。这在对一模型引用两次,或者对关联模型使用定义之外的名称时非常有用。

    User.hasMany(Picture)
    User.belongsTo(Picture, { as: 'ProfilePicture', constraints: false })
    user.getPictures() // 获取所有图片
    user.getProfilePicture() // 仅获取主图
    User.findAll({
      where: ...,
      include: [
        { model: Picture }, // 加载所有图片
        { model: Picture, as: 'ProfilePicture' }, // 加载主图,名称拼写必须与关联关系中命名相同
    

    要完全控制通过Sequlize 添加的外键列,可以使用foreignKey选项。选项值可以是表示名称的字符串或类似使用sequelize.define进行模型定义时对象。

    User.hasMany(Picture, { foreignKey: 'uid' })

    这样外键列会使用uid代替默认的userId

    User.hasMany(Picture, {
      foreignKey: {
        name: 'uid',
        allowNull: false
    

    指定uid列不能为NULL。在大多数情况下,这将覆盖的外键约束,这sequelize自动创建的,这在外键禁用时非常有用。

    当匹配关联模型时,可限制只匹配部分模型。这些查询条件与在find/findAll中的使用方式相同。如,只查找'jpg'格式的图片:

    user.getPictures({
      where: {
        format: 'jpg'
    

    2.2 Model.hasOne() - 拥有一个

    Model.hasOne(target, [options])

    创建当前模型(源)到目标模型之间的关系,外键会被添加到目标模型中。

    名称类型说明 target Model [options] object [options.hooks=false] boolean 设置为 true 时,会在关联模型删除时执行 before-/afterDestroy 钩子方法 [options.as] string 当前模型(源)的别名,单数形式。如果你为一个表创建多次关联,或者不想使用定义模型时使用的名称,那么就应该为模型指定一个别名。 [options.foreignKey] string | object 目标表中的外键名或相当于定义外键列的对象 (语法参考 Sequelize.define )。使用对象时,应该添加一个name来设置列名。默认的外键命名规为源模型名+源模型主键名 [options.onDelete='SET NULL | CASCADE'] string 如果外允许空则 SET NULL,其它则 CASCADE [options.onUpdate='CASCADE'] string [options.constraints=true] boolean 是否在删除或更新时启用外键约束

    2.3 Model.belongsTo() - 属于

    Model.belongsTo(target, [options])

    创建当前模型(源)到目标模型之间的关系,外键会被添加到源模型中。

    名称类型说明 target Model [options] object [options.hooks=false] boolean 设置为 true 时,会在关联模型删除时执行 before-/afterDestroy 钩子方法 [options.as] string 当前模型(源)的别名,单数形式。如果你为一个表创建多次关联,或者不想使用定义模型时使用的名称,那么就应该为模型指定一个别名。 [options.foreignKey] string | object 目标表中的外键名或相当于定义外键列的对象 (语法参考 Sequelize.define )。使用对象时,应该添加一个name来设置列名。默认的外键命名规为源模型名+源模型主键名 [options.scope] object 键/值 集合,用于目标的创建和查找操作(sqlite 不支持 N:M) [options.onDelete='SET NULL | NO ACTION'] string 如果外允许空则 SET NULL,其它则 CASCADE [options.onUpdate='CASCADE'] string [options.constraints=true] boolean 是否在删除或更新时启用外键约束

    2.4 Model.hasMany() - 拥有多个

    Model.hasMany(target, [options])

    创建当前模型(源)到目标模型之间的 1:m 的关系,外键会被添加到目标模型中。

    名称类型说明 target Model [options] object [options.hooks=false] boolean 设置为 true 时,会在关联模型删除时执行 before-/afterDestroy 钩子方法 [options.as] string 当前模型(源)的别名,单数形式。如果你为一个表创建多次关联,或者不想使用定义模型时使用的名称,那么就应该为模型指定一个别名。 [options.foreignKey] string | object 目标表中的外键名或相当于定义外键列的对象 (语法参考 Sequelize.define )。使用对象时,应该添加一个name来设置列名。默认的外键命名规为源模型名+源模型主键名 [options.targetKey] string 用于关联目标表的字段名。默认为目标表的主键。 [options.onDelete='SET NULL | NO ACTION'] string 如果外允许空则 SET NULL,其它则 CASCADE [options.onUpdate='CASCADE'] string [options.constraints=true] boolean 是否在删除或更新时启用外键约束
    Model.belongsToMany(target, [options])

    创建连接表的 N:M 的关系

    User.belongsToMany(Project, { through: 'UserProjects' })
    Project.belongsToMany(User, { through: 'UserProjects' })

    定义中指定需要through时,sequelize会尝试自动生成名字,但生成的名字并不一定符合逻辑。

    你通过自定义属性定义一个模型,它的属性可以用两种方式添加/设置关联。

    var UserProjects = sequelize.define('UserProjects', {
      started: Sequelize.BOOLEAN
    User.belongsToMany(Project, { through: UserProjects })
    Project.belongsToMany(User, { through: UserProjects })
    jan.addProject(homework, { started: false }) // homework 工程还未开始
    jan.setProjects([makedinner, doshopping], { started: true}) // shopping和dinner 两种方式都会启动
    

    如果你想设置多个目标实例,但是有不同的属性,这时必须在实例上设置属性:

    p1.UserProjects = {
      started: true
    user.setProjects([p1, p2], {started: false}) 

    类似的,使用自定义属性连接表时,这些属性将做为一个对象的名称:

    user.getProjects().then(function (projects) {
      var p1 = projects[0]
      p1.UserProjects.started // Is this project started yet?
    名称类型说明
    target
    Model
    [options]
    object
    [options.hooks=false]
    boolean
    设置为 true 时,会在关联模型删除时执行 before-/afterDestroy 钩子方法
    [options.through]
    Model | string | object
    在N:M 的关联中,用于连接源 和 目标 表的名称
    [options.through.model]
    Model
    用于连接 N:M 关系的模型
    [options.through.scope]
    object
    用于建立关联的键/值集合,并通过模型查找默认值。
    [options.through
    .unique=true] boolean 设置为 true时,唯一键会从使用的外键中生成