egg、sequelize联表查询

数据库设计

use egg_test;
create table `user`(
    `id` int(10) unsigned auto_increment comment '',
    `username` varchar(20) not null comment '',
    `createdAt` int(10) unsigned comment '',
    `updatedAt` int(10) unsigned comment '',
    primary key(id)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';
create table `user_info`(
    `id` int(10) unsigned auto_increment comment '',
    `age` tinyint(4) unsigned default 0 comment '',
    `address` varchar(50) default '' comment '',
    `userId` int(10) unsigned not null comment '',
    `createdAt` int(10) unsigned comment '',
    `updatedAt` int(10) unsigned comment '',
    primary key(id)   
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表信息';
create table `family`(
    `id` int(10) unsigned auto_increment comment '',
    `name` varchar(20) not null comment '',
    `gender` tinyint(1) unsigned comment '0:man,1:woman:',
    `relationship` varchar(10) comment '',
    `createdAt` int(10) unsigned comment '',
    `updatedAt` int(10) unsigned comment '',
    primary key(id)     
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='家庭成员';
create table `teacher_lesson_relation`(
    `id` int(10) unsigned auto_increment comment '',
    `lessonId` int(10) unsigned not null comment '',
    `teacherId` int(10) unsigned not null comment '', 
    `createdAt` int(10) unsigned comment '',
    `updatedAt` int(10) unsigned comment '',
    primary key(id)       
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='课程和老师的关系';
create table `lesson`(
    `id` int(10) unsigned auto_increment comment '',
    `name` varchar(50) not null comment '',
    `createdAt` int(10) unsigned comment '',
    `updatedAt` int(10) unsigned comment '',
    primary key(id)     
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='课程';
create table `teacher`(
    `id` int(10) unsigned auto_increment comment '',
    `name` varchar(50) not null comment '',
    `createdAt` int(10) unsigned comment '',
    `updatedAt` int(10) unsigned comment '',
    primary key(id)  
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='老师';

一对一hasOne

model: user

module.exports = app => {
    const { INTEGER, STRING, TEXT } = app.Sequelize;
    const User = app.model.define('User', {
        id: {
            type: INTEGER.UNSIGNED,
            autoIncrement: true,
            primaryKey: true
        },
        username: {
            type: STRING(20),
            allowNull: false
        }
    }, {
        freezeTableName: true,
        tableName: 'user'
    });
    User.associate = function() {
        app.model.User.hasOne(app.model.Info, { foreignKey: 'userId' });
        app.model.User.hasMany(app.model.Family, { foreignKey: 'userId', targetKey: 'id' });
    }
    return User;
}

用的是:app.model.User.hasOne(app.model.Info, { foreignKey: ‘userId’ });
model: info

module.exports = app => {
    const { INTEGER, STRING, TEXT } = app.Sequelize;
    const Info = app.model.define('Info', {
        id: {
            type: INTEGER.UNSIGNED,
            autoIncrement: true,
            primaryKey: true
        },
        age: {
            type: INTEGER.UNSIGNED
        },
        address: {
            type: STRING(50)
        }
    }, {
        freezeTableName: true,
        tableName: 'user_info'
    });
    Info.associate = function() {
        app.model.Info.belongsTo(app.model.User, { foreignKey: 'userId', targetKey: 'id' });
    }
    return Info;
}

controller

  async getUserListInclueInfo() {
    let result = await this.app.model.User.findAll({
      include: [
        {
          model: this.app.model.Info
        }
      ]
    });
    let result2 = await this.app.model.User.findAll({
      include: [
        {
          model: this.app.model.Info,
          where: {
            age: {
              [this.app.Sequelize.Op.gt]: 15
            }
          }
        }
      ]
    });
    let result3 = await this.app.model.User.findAll({
      include: [
        {
          model: this.app.model.Info,
          where: {
            age: {
              [this.app.Sequelize.Op.gt]: 15
            }
          },
          required: false
        }
      ]
    });    
    this.ctx.body = {
      'no_where': result,
      'has_where': result2,
      'where_has_required_false': result3
    };
  }

结果

{
    "no_where": [
        {
            "id": 1,
            "username": "user1",
            "createdAt": 0,
            "updatedAt": 0,
            "Info": {
                "id": 1,
                "age": 15,
                "address": "address1",
                "createdAt": 0,
                "updatedAt": 0,
                "userId": 1
            }
        },
        {
            "id": 2,
            "username": "user2",
            "createdAt": 0,
            "updatedAt": 0,
            "Info": {
                "id": 2,
                "age": 18,
                "address": "address",
                "createdAt": 0,
                "updatedAt": 0,
                "userId": 2
            }
        },
        {
            "id": 3,
            "username": "user3",
            "createdAt": 0,
            "updatedAt": 0,
            "Info": null
        }
    ],
    "has_where": [
        {
            "id": 2,
            "username": "user2",
            "createdAt": 0,
            "updatedAt": 0,
            "Info": {
                "id": 2,
                "age": 18,
                "address": "address",
                "createdAt": 0,
                "updatedAt": 0,
                "userId": 2
            }
        }
    ],
    "where_has_required_false": [
        {
            "id": 2,
            "username": "user2",
            "createdAt": 0,
            "updatedAt": 0,
            "Info": {
                "id": 2,
                "age": 18,
                "address": "address",
                "createdAt": 0,
                "updatedAt": 0,
                "userId": 2
            }
        },
        {
            "id": 1,
            "username": "user1",
            "createdAt": 0,
            "updatedAt": 0,
            "Info": null
        },
        {
            "id": 3,
            "username": "user3",
            "createdAt": 0,
            "updatedAt": 0,
            "Info": null
        }
    ]
}

中间穿插了include中添加where条件,以及当联的表存在required的影响

一对多 hasMany

在一个model

module.exports = app => {
    const { INTEGER, STRING, TEXT } = app.Sequelize;
    const Family = app.model.define('Family', {
        id: {
            type: INTEGER.UNSIGNED,
            autoIncrement: true,
            primaryKey: true
        },
        userId: {
            type: INTEGER.UNSIGNED,
            allowNull: false
        },
        name: {
            type: STRING(50),
            allowNull: false
        },
        gender: {
            type: INTEGER.UNSIGNED,
            defaultValue: 0
        },
        relationship: {
            type: STRING(20)
        }
    }, {
        freezeTableName: true,
        tableName: 'family'
    });
    Family.associate = function() {
        app.model.Family.belongsTo(app.model.User, { foreignKey: 'userId', targetKey: 'id' });
    }
    return Family;
}

用到建立关系的是 app.model.User.hasMany(app.model.Family, { foreignKey: ‘userId’, targetKey: ‘id’ });
controller

  async hasMany() {
    let result = await this.app.model.User.findAll({
      include: {
        model: this.app.model.Family
      }
    });
    this.ctx.body = result;
  }

结果

[
    {
        "id": 1,
        "username": "user1",
        "createdAt": 0,
        "updatedAt": 0,
        "Families": [
            {
                "id": 1,
                "userId": 1,
                "name": "tom",
                "gender": 0,
                "relationship": "father",
                "createdAt": 0,
                "updatedAt": 0
            },
            {
                "id": 2,
                "userId": 1,
                "name": "lily",
                "gender": 1,
                "relationship": "mother",
                "createdAt": 0,
                "updatedAt": 0
            }
        ]
    },
    {
        "id": 2,
        "username": "user2",
        "createdAt": 0,
        "updatedAt": 0,
        "Families": []
    },
    {
        "id": 3,
        "username": "user3",
        "createdAt": 0,
        "updatedAt": 0,
        "Families": []
    }
]

一对多 belongsTo

用到是 app.model.Family.belongsTo(app.model.User, { foreignKey: ‘userId’, targetKey: ‘id’ });

controller

  async belongsTo() {
    let result = await this.app.model.Family.findAll({
      include: {
        model: this.app.model.User
      }
    });
    this.ctx.body = result;
  }

结果

[
    {
        "id": 1,
        "userId": 1,
        "name": "tom",
        "gender": 0,
        "relationship": "father",
        "createdAt": 0,
        "updatedAt": 0,
        "User": {
            "id": 1,
            "username": "user1",
            "createdAt": 0,
            "updatedAt": 0
        }
    },
    {
        "id": 2,
        "userId": 1,
        "name": "lily",
        "gender": 1,
        "relationship": "mother",
        "createdAt": 0,
        "updatedAt": 0,
        "User": {
            "id": 1,
            "username": "user1",
            "createdAt": 0,
            "updatedAt": 0
        }
    }
]

多对多belongsToMany

model:lesson

module.exports = app => {
    const { INTEGER, STRING, TEXT } = app.Sequelize;
    const Lesson = app.model.define('Lesson', {
        id: {
            type: INTEGER.UNSIGNED,
            autoIncrement: true,
            primaryKey: true
        },
        name: {
            type: STRING(20)
        }
    }, {
        freezeTableName: true,
        tableName: 'lesson'
    });
    Lesson.associate = function() {
        app.model.Lesson.belongsToMany( app.model.Teacher,{ through: app.model.TeacherLessonRelation, foreignKey: 'lessonId', otherKey: 'teacherId'});
    }
    return Lesson;
}

model: teacher

module.exports = app => {
    const { INTEGER, STRING, TEXT } = app.Sequelize;
    const Teacher = app.model.define('Teacher', {
        id: {
            type: INTEGER.UNSIGNED,
            autoIncrement: true,
            primaryKey: true
        },
        name: {
            type: STRING(20),
            allowNull: false
        }
    }, {
        freezeTableName: true,
        tableName: 'teacher'
    });
    Teacher.associate = function() {

    }
    return Teacher;
}

model: teacher-lesson-relation

module.exports = app => {
    const { INTEGER, STRING, TEXT } = app.Sequelize;
    const TeacherLessonRelation = app.model.define('TeacherLessonRelation', {
        id: {
            type: INTEGER.UNSIGNED,
            autoIncrement: true,
            primaryKey: true
        },
        lessonId: {
            type: INTEGER.UNSIGNED,
            allowNull: false
        },
        teacherId: {
            type: INTEGER.UNSIGNED,
            allowNull: false
        }
    }, {
        freezeTableName: true,
        tableName: 'teacher_lesson_relation'
    });
    TeacherLessonRelation.associate = function() {

    }
    return TeacherLessonRelation;
}

controller

  async belongsToMany() {
    let result = await this.app.model.Lesson.findAll({
      include: {
        model: this.app.model.Teacher
      }
    });
    this.ctx.body = result;
  }

结果

[
    {
        "id": 1,
        "name": "高数",
        "createdAt": null,
        "updatedAt": null,
        "Teachers": [
            {
                "id": 1,
                "name": "zengwe",
                "createdAt": null,
                "updatedAt": null,
                "TeacherLessonRelation": {
                    "id": 1,
                    "lessonId": 1,
                    "teacherId": 1,
                    "createdAt": null,
                    "updatedAt": null
                }
            },
            {
                "id": 2,
                "name": "tom",
                "createdAt": null,
                "updatedAt": null,
                "TeacherLessonRelation": {
                    "id": 2,
                    "lessonId": 1,
                    "teacherId": 2,
                    "createdAt": null,
                    "updatedAt": null
                }
            }
        ]
    },
    {
        "id": 2,
        "name": "地理",
        "createdAt": null,
        "updatedAt": null,
        "Teachers": [
            {
                "id": 3,
                "name": "edward",
                "createdAt": null,
                "updatedAt": null,
                "TeacherLessonRelation": {
                    "id": 3,
                    "lessonId": 2,
                    "teacherId": 3,
                    "createdAt": null,
                    "updatedAt": null
                }
            }
        ]
    },
    {
        "id": 4,
        "name": "政治",
        "createdAt": null,
        "updatedAt": null,
        "Teachers": []
    },
    {
        "id": 5,
        "name": "历史",
        "createdAt": null,
        "updatedAt": null,
        "Teachers": []
    }
]

中间表可以不填写,可以按自动的规则使用,创建符合格式的中间表就可以了

额外

1:在参数中include是可以在里面继续嵌套include的;
2:当一个表中两个字段同时外联同一张表时

如 app.model.User.hasOne(app.model.Info, { as:’userInfo’ foreignKey: ‘userId’ });
app.model.User.hasOne(app.model.Info, { as: ‘moreInfo’ foreignKey: ‘otherId’ });

可以添加as来添加别称,使用时在include中也要使用相同的别称,就可以正常使用了

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页