Sequelize 中的一对一、一对多、多对多

  最近做了写公司中的基建工作,其中涉及到了 node 项目中使用 sequelize,同时有多对多的关系需要处理,便写了个关于 sequelize 处理多对多关系的练手示例,liuxy0551/sequelize-association,目前仅涉及查询,createupdate暂未进行。

  sql 语句 点此查看

一、一对一

  一对一的举例是:一个中国公民 (Chinese) 只有一个身份证号 (IDNumber) ,具体实现如下:

1
2
3
4
5
6
const models = initModels(sequelize)
const { Chinese, IDNumber } = models

// 建立关系
Chinese.hasOne(IDNumber, { foreignKey: 'id', sourceKey: 'IDNumberId', as: 'IDNumberInfo' })
IDNumber.belongsTo(Chinese, { foreignKey: 'id', targetKey: 'IDNumberId' })
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// service 层实现
async getChinese (ctx) {
try {
const { offset, limit, page, pageSize } = getPage(ctx.query)
const { count, rows } = await DB.Chinese.findAndCountAll({
where: getWhere(),
attributes: {
exclude: getExclude(),
},
include: [
{
model: DB.IDNumber,
as: "IDNumberInfo",
required: false,
where: getWhere(),
attributes: {
exclude: getExclude(),
},
},
],
offset,
limit,
})
return setCtxBody(200, rows, '成功', { total: count, page, pageSize })
} catch (error) {
return setCtxBody(500, error, '系统错误')
}
}

  接口返回的结果如下:

  sequelize 自动生成的 sql 语句如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
`Chinese`.`id`,
`Chinese`.`IDNumberId`,
`Chinese`.`name`,
`Chinese`.`createdAt`,
`Chinese`.`updatedAt`,
`IDNumberInfo`.`id` AS `IDNumberInfo.id`,
`IDNumberInfo`.`number` AS `IDNumberInfo.number`,
`IDNumberInfo`.`address` AS `IDNumberInfo.address`,
`IDNumberInfo`.`createdAt` AS `IDNumberInfo.createdAt`,
`IDNumberInfo`.`updatedAt` AS `IDNumberInfo.updatedAt`
FROM
`Chinese` AS `Chinese`
LEFT OUTER JOIN `IDNumber` AS `IDNumberInfo` ON `Chinese`.`IDNumberId` = `IDNumberInfo`.`id`
AND `IDNumberInfo`.`isDeleted` = 0
WHERE
`Chinese`.`isDeleted` = 0
LIMIT 0,
10;

二、一对多

  一对多的举例是:一个省份 (Province) 有多个市 (City),具体实现如下:

1
2
3
4
5
6
const models = initModels(sequelize)
const { Province, City } = models

// 建立关系
Province.hasMany(City, { as: 'cityList' })
City.belongsTo(Province, { foreignKey: 'id' })
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// service 层实现
async getProvinceList (ctx) {
try {
const { offset, limit, page, pageSize } = getPage(ctx.query)
const { count, rows } = await DB.Province.findAndCountAll({
where: getWhere(),
attributes: {
exclude: getExclude(),
},
include: [
{
model: DB.City,
as: 'cityList',
required: false,
where: getWhere(),
attributes: {
exclude: getExclude(['ProvinceId']),
},
}
],
offset,
limit,
})
return setCtxBody(200, rows, '成功', { total: count, page, pageSize })
} catch (error) {
return setCtxBody(500, error, '系统错误')
}
}

  接口返回的结果如下:

  sequelize 自动生成的 sql 语句如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
`Province`.*,
`cityList`.`id` AS `cityList.id`,
`cityList`.`provinceId` AS `cityList.provinceId`,
`cityList`.`name` AS `cityList.name`,
`cityList`.`createdAt` AS `cityList.createdAt`,
`cityList`.`updatedAt` AS `cityList.updatedAt`
FROM
(
SELECT
`Province`.`id`,
`Province`.`name`,
`Province`.`createdAt`,
`Province`.`updatedAt`
FROM
`Province` AS `Province`
WHERE
`Province`.`isDeleted` = 0
LIMIT 0,
10
) AS `Province`
LEFT OUTER JOIN `City` AS `cityList` ON `Province`.`id` = `cityList`.`ProvinceId`
AND `cityList`.`isDeleted` = 0;

三、多对多

  多对多的举例是:一部电影 (Movie) 有多个演员 (Actor), 一个演员 (Actor) 参演多部电影 (Movie),具体实现如下:

1
2
3
4
5
6
const models = initModels(sequelize)
const { Movie, Actor, MovieActor } = models

// 建立关系
Movie.belongsToMany(Actor, { through: MovieActor, as: 'actorList' })
Actor.belongsToMany(Movie, { through: MovieActor, as: 'movieList' })
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
// service 层实现
async getMovieListWithActors (ctx) {
try {
const { offset, limit, page, pageSize } = getPage(ctx.query)
const { count, rows } = await DB.Movie.findAndCountAll({
where: getWhere(),
attributes: {
exclude: getExclude()
},
include: [
{
model: DB.Actor,
as: 'actorList',
required: false,
where: getWhere(),
attributes: {
exclude: getExclude(),
},
through: { attributes: [] }
}
],
offset,
limit,
})
return setCtxBody(200, rows, '成功', { total: count, page, pageSize })
} catch (error) {
return setCtxBody(500, error, '系统错误')
}
}

async getActorListWithMovies (ctx) {
try {
const { offset, limit, page, pageSize } = getPage(ctx.query)
const { count, rows } = await DB.Actor.findAndCountAll({
where: getWhere(),
attributes: {
exclude: getExclude()
},
include: [
{
model: DB.Movie,
as: 'movieList',
required: false,
where: getWhere(),
attributes: {
exclude: getExclude(['MovieActor']),
},
through: { attributes: [] }
}
],
offset,
limit,
})
return setCtxBody(200, rows, '成功', { total: count, page, pageSize })
} catch (error) {
return setCtxBody(500, error, '系统错误')
}
}

  接口返回的结果如下:

  sequelize 自动生成的 sql 语句如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 多对多 getActorListWithMovies
SELECT
`Actor`.*,
`movieList`.`id` AS `movieList.id`,
`movieList`.`name` AS `movieList.name`,
`movieList`.`createdAt` AS `movieList.createdAt`,
`movieList`.`updatedAt` AS `movieList.updatedAt`
FROM
(
SELECT
`Actor`.`id`,
`Actor`.`name`,
`Actor`.`createdAt`,
`Actor`.`updatedAt`
FROM
`Actor` AS `Actor`
WHERE
`Actor`.`isDeleted` = 0
LIMIT 0,
10
) AS `Actor`
LEFT OUTER JOIN (
`MovieActor` AS `movieList->MovieActor`
INNER JOIN `Movie` AS `movieList` ON `movieList`.`id` = `movieList->MovieActor`.`MovieId`
) ON `Actor`.`id` = `movieList->MovieActor`.`ActorId`
AND `movieList`.`isDeleted` = 0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 多对多 getMovieListWithActors
SELECT
`Movie`.*,
`actorList`.`id` AS `actorList.id`,
`actorList`.`name` AS `actorList.name`,
`actorList`.`createdAt` AS `actorList.createdAt`,
`actorList`.`updatedAt` AS `actorList.updatedAt`
FROM
(
SELECT
`Movie`.`id`,
`Movie`.`name`,
`Movie`.`createdAt`,
`Movie`.`updatedAt`
FROM
`Movie` AS `Movie`
WHERE
`Movie`.`isDeleted` = 0
LIMIT 0,
10
) AS `Movie`
LEFT OUTER JOIN (
`MovieActor` AS `actorList->MovieActor`
INNER JOIN `Actor` AS `actorList` ON `actorList`.`id` = `actorList->MovieActor`.`ActorId`
) ON `Movie`.`id` = `actorList->MovieActor`.`MovieId`
AND `actorList`.`isDeleted` = 0;

四、注意事项

  1、在使用关联关系进行查询时,请求参数中不要使用raw: trueraw默认为 false,此时 sequelize 会自动拼接一些参数,设为 true 的话,会丢失参数导致数据结构错乱,如下图:

  2、一对多时会出现返回的结果包含了大驼峰写法的关联 id 及值,可通过include attributes exclude将该字段过滤;
  3、多对多时,一般不需要展示关联表的字段,可通过include through attributes将关联表字段过滤,如下:

1
2
3
4
5
6
7
8
9
const { count, rows } = await DB.Actor.findAndCountAll({
...
include: [
{
...
through: { attributes: [] }
}
]
})


  4、建立关联关系时,可在app/utils/mysql/db.js中进行,引入的 initModels 中导出了所有 model 层,可参考:app/utils/mysql/db.js
  5、多对多的关联查询可以分解为以下四条 sql 进行,在数据量大的时候能减少查询时间,也是简化 sql 语句的方法:

  • 查询电影表的总数量 total
  • 查询电影表前十条数据
  • 拿着上述十条数据去关联表查询关联数据,对关联的演员信息进行去重
  • 拿着上述去重后的演员信息在演员表中查询,由代码拼装数据再从接口返回

五、占坑文章

  1、预先加载,参考 Sequelize 中文文档 - 预先加载

六、参考文章

  练习过程中较多的参考了以下文章中提到的内容,在此感谢:

[1] Sequelize 中文文档 - 关联
[2] Sequelize 英文官网 Many-To-Many relationships

以上

随笔标题:Sequelize 中的一对一、一对多、多对多

随笔作者:刘先玉

发布时间:2021年09月21日 - 18:53:17

最后更新:2021年09月21日 - 18:53:17

原文链接:https://liuxianyu.cn/article/sequelize-association.html