大家可以前往 Sequelize中文文档,查看 Sequelize不同版本【5.x、4.x】的文档
本文档分多个篇章,难易程度从低到高,学习此篇章之前,务必确保自己已经掌握 node.js、express、es6语法、mysql等关系型数据库的sql语法等
一对多
以下例子可能不恰当,但是不要较真,跟着思路走就对了。
举个栗子: 假设一个人(id)可以有多个爱好(id),那么这种模式就是一对多
模型定义
我这里为了方便理解就不把hobbys表的模型拎出去了
import Sequelize from 'sequelize'
import sequelize from './../connection'
const User = sequelize.define('user', {
username: Sequelize.STRING,
gender: {
type: Sequelize.INTEGER,
defaultValue: 1,
},
age: {
type: Sequelize.INTEGER,
defaultValue: 20
},
headImg: {
type: Sequelize.STRING,
defaultValue: 'img.png'
}
});
const Hobby = sequelize.define('hobby',
{
hobbyName: {
type: Sequelize.CHAR(20),
allowNull: false
}
}
);
//User的实例对象将拥有getHobby、setHobby、addHobby、createHobby、removeHobby、hasHobby方法
let Hobbies = User.hasMany(Hobby, {as: 'Hobbies'});//让user实例可以调用getHobbies 和 setHobbies
//Hobby的实例对象将拥有getUser、setUser、createUser方法
Hobby.belongsTo(User);
User.sync({force: true}).then(d=> {//表同步完成后注释掉
console.log('users表同步完成');
Hobby.sync({force: true}).then(d=> {
console.log('Hobby表同步完成');
});
});
export default {User,Hobby,Hobbies}
关系操作
增加数据
方法一
User.Controller.js
insertUserHobby: async (req, res, next) => {
let {username, hobbyName} = req.body;
let user = await User.User.create({username});
let hobby = await user.createHobby({hobbyName});
res.send({
code: 200,
hobby
})
},
方法二
User.Controller.js
insertUserHobbies: async (req, res, next) => {
let {username, hobbyName} = req.body;
let user = await User.User.create({username});
let hobby1 = await User.Hobby.create({hobbyName});//插入一条hobby数据,此时该条数据的外键userId为空
let hobby2 = await User.Hobby.create({hobbyName: '篮球'});//这里我就不接收了,直接赋值了
hobby1 = await user.addHobby(hobby1);//使用user的属性id值再更新该条hobby数据,设置好外键,完成关系建立
hobby2 = await user.addHobby(hobby2);//新增多个爱好
res.send({
code: 200,
hobby1,hobby2
})
},
修改数据
User.Controller.js
updateUserHobbies: async (req, res, next)=>{
let {id, hobbyName} = req.body;
let user = await User.User.findOne({where: {id}});
let upHobby = await User.Hobby.create({hobbyName});
let upHobby2 = await User.Hobby.create({hobbyName: '爱好2'});
let updateHobby = await user.setHobbies([upHobby, upHobby2]);
res.send({
code: 200,
data: '修改成功',
updateHobby
})
},
删除数据 实际上是将关联的userId设置为null
User.Controller.js
deleteUserHobbies: async (req, res, next)=>{
let {id} = req.body;
let user = await User.User.findOne({where: {id}});//通过id查询用户
let deleteHobby = await user.setHobbies([]);
res.send({
code: 200,
data: '删除成功',
deleteHobby
})
},
查询数据
查询user的所有满足条件的hobby数据。
User.Controller.js
getHobbies : async (req, res, next)=>{
let {id} = req.query;
let user = await User.User.findByPk(id);
let Hobbies = await user.getHobbies({
attributes: ['hobbyName','userId']
});
res.send({
code: 200,
hobbies
})
}
postman
{
"code": 200,
"hobbies": [
{
"hobbyName": "练习生",
"userId": 4
},
{
"hobbyName": "篮球",
"userId": 4
}
]
}
查询所有满足条件的hobby,同时获取hobby属于哪个user。
User.Controller.js
getUserHobbies: async (req, res, next)=>{
let {id} = req.query;
let hobbies = await User.Hobby.findAll({
where: {userId: id},
include: [{
model: User.User,
attributes: ['username']
}],
attributes: ['hobbyName','userId']
});
res.send({code: 200, hobbies})
}
postman
{
"code": 200,
"hobbies": [
{
"hobbyName": "练习生",
"userId": 4,
"user": {
"username": "Mjhu"
}
},
{
"hobbyName": "篮球",
"userId": 4,
"user": {
"username": "Mjhu"
}
}
]
}
查询所有满足条件的user,同时获取该user所有满足条件的hobby。
User.Controller.js
getUserInfo: async (req, res, next)=>{
let {id} = req.query;
let userAndHobbies = await User.User.findOne({
where: {id},
attributes: ['username','gender'],
include: [{
association: User.Hobbies,
attributes: ['hobbyName']
}]
});
res.send({code: 200, userAndHobbies})
}
postman
{
"code": 200,
"userAndHobbies": {
"username": "Mjhu",
"gender": 1,
"Hobbies": [
{
"hobbyName": "练习生"
},
{
"hobbyName": "篮球"
}
]
}
}
好了,到此为止,一对多的关系就已经结束了,下一章节我们会介绍 [Sequelize V5.9.4 中数据表的关系:多对多