Sequelize V6.20.1 MVC模式（六 | 多对多）

大家可以前往 Sequelize中文文档，查看 Sequelize不同版本【5.x、4.x】的文档

本文档分多个篇章，难易程度从低到高，学习此篇章之前，务必确保自己已经掌握 node.js、express、es6语法、mysql等关系型数据库的sql语法等

多对多

在多对多关系中，必须要额外一张关系表来将2个表进行关联，这张表可以是单纯的一个关系表，也可以是一个实际的模型（含有自己的额外属性来描述关系）

以下例子可能不恰当，但是不要较真，跟着思路走就对了。
举个栗子：假设一个学生（id）可以有多个老师（id），一个老师（id）也可以对应多个学生（id）那么这种模式就是多对多

模型定义

我这里为了方便理解就不把关联表的模型拎出去了

import {Sequelize, DataTypes} from 'sequelize'
import sequelize from './../connection'

const Student = sequelize.define('student', {
    username: DataTypes.STRING,
});
const Teacher = sequelize.define('teacher', {
    username: DataTypes.STRING,
});
//定义联系表
const StuTeacher = sequelize.define('StuTeacher',
    {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        type: {
            type: DataTypes.INTEGER,
            allowNull: false
        }
    }
);

//Student的实例拥有getTeachers、setTeachers、addTeacher、addTeachers、createTeacher、removeTeacher、hasTeacher方法
Student.belongsToMany(Teacher, {through: StuTeacher});
//Teacher的实例拥有getStudents、setStudents、addStudent、addStudents、createStudent、removeStudent、hasStudent方法
Teacher.belongsToMany(Student,{through: StuTeacher});

sequelize.sync({force: true}).then(d=> {//所有表初始化完成后注释掉
    console.log('所有表初始化完成');
});

export default {StuTeacher,Student,Teacher}

对应数据库

所有表

stuteachers表结构

增加数据

方法一

insertUser: async (req, res, next) => {
        let {studentName, teacherName} = req.body;
        let teacher = await User.Teacher.create({username: teacherName});
        let data = await teacher.createStudent({username: studentName},{through: {type: 0}});//type是stuteachers表设计时的必填项，所以写上
        res.send({
            code: 200,
            data
        })
    },

方法二

insertUser: async (req, res, next) => {
        let {studentName, teacherName} = req.body;
        let teacher = await User.Teacher.create({username: teacherName});
        let student = await User.Student.create({username: studentName});
        let data = await teacher.addStudent(student,{through: {type: 1}});
        res.send({
            code: 200,
            data
        })
    },

方法三 批量添加

insertUser: async (req, res, next) => {
        // let {studentName, teacherName} = req.body;
        //下面我直接传递数据，不用接收到的
        let teacher = await User.Teacher.create({username: '数学老师'});//插入teacher表中一条数据
        let student1 = await User.Student.create({username: '路星河'});//插入students表中，一条数据
        let student2 = await User.Student.create({username: '简单'});
        let data = await teacher.addStudents([student1,student2],{through: {type: 1}});//将插入的students和teacher在stuteacher表中进行联系
        res.send({
            code: 200,
            data
        })
    }

修改数据

原有的基础上新增

addStudents 在新增之前，会检查stuteachers表中是否存在当前的联系，如果存在不做添加，反之新增联系

updateUser: async (req, res, next)=>{
        // let {id, hobbyName} = req.body;
        //下面我直接传递数据，不用接收到的
        let teacher = await User.Teacher.findOne({where: {id: 5}});
        let student1 = await User.Student.create({username: '班长'});
        let student2 = await User.Student.create({username: '团支书'});
        let data = await teacher.addStudents([student1, student2],{through: {type: 1}});
        res.send({
            code: 200,
            data
        })
    }

删除原有的，并重新设置

setStudents 在全部删除之前，会先检查stuteachers表中是否存在当前的联系，如果存在则不做处理，反之则会删除，并重新添加联系

updateUser: async (req, res, next)=>{
        // let {id, hobbyName} = req.body;
        //下面我直接传递数据，不用接收到的
        let teacher = await User.Teacher.findOne({where: {id: 5}});
        let student1 = await User.Student.findOne({where: {id: 1}});
        let student2 = await User.Student.findOne({where: {id: 7}});
        let student3 = await User.Student.findOne({where: {id: 8}});
        let data = await teacher.setStudents([student1, student2, student3],{through: {type: 0}});
        res.send({
            code: 200,
            data
        })
    }

删除数据

deleteUser: async (req, res, next)=>{
        // let {id} = req.body;
        //下面我直接传递数据，不用接收到的
        let teacher = await User.Teacher.findOne({where: {id: 4}});
        let student = await User.Student.findOne({where: {id: 2}});
        //删除一个
        let data = await teacher.removeStudent(student);
        //删除所有
        let data = await teacher.setStudents([]);
        res.send({
            code: 200,
            data
        })
    }

查询数据

第一种：查询teacher所有满足条件的student。

getUserInfo: async (req, res, next)=>{
        let {id} = req.query; //id=5
        let teacher = await User.Teacher.findOne({where: {id},attributes: ['username','id']});
        let data = await teacher.getStudents({attributes: ['username']});
        res.send({code: 200, data})
    }

postman

{
    "code": 200,
    "data": [
        {
            "username": "团支书",
            "StuTeacher": {
                "id": 6,
                "type": 0,
                "createdAt": "2019-07-10T08:54:46.000Z",
                "updatedAt": "2019-07-10T09:05:17.000Z",
                "studentId": 8,
                "teacherId": 5
            }
        },
        {
            "username": "余淮",
            "StuTeacher": {
                "id": 7,
                "type": 0,
                "createdAt": "2019-07-10T08:58:29.000Z",
                "updatedAt": "2019-07-10T09:05:17.000Z",
                "studentId": 1,
                "teacherId": 5
            }
        }
    ]
}

第二种：查询所有满足条件的teacher，同时获取每个teacher所有满足条件的student

getUserInfo: async (req, res, next)=>{
        let {id} = req.query;
        let data = await User.Teacher.findOne({where: {id},attributes: ['username','id'],include: [
                {
                    model: User.Student,
                    attributes: ['username', 'id'],
                    through: {
                        attributes: ['id'],
                    }
                }
            ]});
        res.send({code: 200, data})
    }

postman

{
    "code": 200,
    "data": {
        "username": "数学老师",
        "id": 5,
        "students": [
            {
                "username": "团支书",
                "id": 8,
                "StuTeacher": {
                    "id": 6
                }
            },
            {
                "username": "余淮",
                "id": 1,
                "StuTeacher": {
                    "id": 7
                }
            }
        ]
    }
}

第三种：查询所有满足条件的student，同时获取每个student所在的teacher。

getUserInfo: async (req, res, next)=>{
        let {id} = req.query;
        let data = await User.Student.findOne({where: {id},attributes: ['username','id'],include: [
                {
                    model: User.Teacher,
                    attributes: ['username', 'id'],
                    through: {
                        attributes: ['id'],
                    }
                }
            ]});
        res.send({code: 200, data})
    }

postman

{
    "code": 200,
    "data": {
        "username": "团支书",
        "id": 8,
        "teachers": [
            {
                "username": "张平",
                "id": 4,
                "StuTeacher": {
                    "id": 16
                }
            },
            {
                "username": "数学老师",
                "id": 5,
                "StuTeacher": {
                    "id": 6
                }
            }
        ]
    }
}

好了，到此为止 Sequelize V6.20.1 的一对一，一对多，多对多 已经介绍完毕，接下来就 Sequelize V6.20.1 的一些细节补充。