大家可以前往 Sequelize中文文档，查看 Sequelize不同版本【5.x、4.x】的文档
本文档分多个篇章，难易程度从低到高，学习此篇章之前，务必确保自己已经掌握 node.js、express、es6语法、mysql等关系型数据库的sql语法等
多对多
在多对多关系中，必须要额外一张关系表来将2个表进行关联，这张表可以是单纯的一个关系表，也可以是一个实际的模型（含有自己的额外属性来描述关系）
以下例子可能不恰当，但是不要较真，跟着思路走就对了。
举个栗子： 假设一个学生（id）可以有多个老师（id），一个老师（id）也可以对应多个学生（id）那么这种模式就是多对多
模型定义
我这里为了方便理解就不把hobbys表的模型拎出去了
import Sequelize from 'sequelize'
import sequelize from './../connection'
const Student = sequelize.define('student', {
username: Sequelize.STRING,
});
const Teacher = sequelize.define('teacher', {
username: Sequelize.STRING,
});
//定义联系表
const StuTeacher = sequelize.define('StuTeacher',
{
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
type: {
type: Sequelize.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 V5.9.4 的一对一，一对多，多对多已经介绍完毕，接下来就 Sequelize V5.9.4 的一些细节补充 。
网友评论