一，多对多，中间表设置外键关联
例如，角色表是roles和权限是permissions，中间表是role_permissions，中间表需要设置外键关联
// Roles的model实现
module.exports = app => {
const DataTypes = app.Sequelize;
const Model = app.model.define('roles', {
id: {
type: DataTypes.INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true
},
...
}, {
tableName: 'roles'
});
Model.associate = function() {
// 和权限表关联
Model.belongsToMany(app.model.Permissions, {
through: app.model.RolePermissions, // 中间表
foreignKey: 'roleId',
otherKey: 'permissionId',
});
// 和用户表关联
Model.belongsToMany(app.model.Users, {
through: app.model.UserRoles, // 中间表
foreignKey: 'roleId',
otherKey: 'userId',
});
}
return Model;
};
角色roles列表查询时，用include关联权限表，角色下带出各自的权限码
const { page, pageSize, roleName } = query;
const tableQuery = {
where: {},
distinct: true, //这一句可以去重，主表是14条，count就是14, 有时解决获取总数量的问题，需要distinct，raw一起使用
include: [
{
model: ctx.model.Permissions,
as: 'permissions',
// where: {},
attributes: ['id', 'permissionName', 'permissionCode', 'type', 'parentId'],
through: { // 注意：添加后，不会再返回关联的其他数据
attributes: []
}
},
{
model: ctx.model.Users,
as: 'users',
// where: {},
attributes: ['id', 'username', 'deptName'],
through: { // 添加后，不会再返回关联的其他数据
attributes: []
}
}
],
offset: (page - 1) * pageSize,
limit: pageSize
};
if (roleName) {
tableQuery.where.roleName = {
[Op.substring]: roleName
};
}
try {
const { count, rows } = await model.findAndCountAll(tableQuery);
ctx.success({ total: count, list: rows, page, pageSize });
} catch (error) {
ctx.error(4002, error);
}
多对多表关联时，给角色添加多个权限的关联数据，直接使用setXXX方法
// 角色的权限配置
async joinPermissions() {
const { ctx, app } = this;
const reqBody = ctx.request.body;
const rule = {
id: { // 角色id
type: 'number',
required: true
},
permissionIds: {
type: 'array',
required: true
}
};
ctx.valid(rule, reqBody);
// 全量更新，简单粗暴，每次都会在中间表重新生成记录，之前的记录会删除
try {
const rolesTarget = await ctx.model.Roles.findByPk(reqBody.id);
// 如果设置了关联关系 belongsToMany，会自动生成setXXXX,getXXX方法
await rolesTarget.setPermissions(reqBody.permissionIds);
ctx.success({ id: reqBody.id });
} catch (error) {
ctx.error(4002, error);
}
// 增量更新 看下面方法
}
上面的角色添加多个权限还可以 增量 更新中间表，有兴趣看的可以看下面的方法，其实是比较生硬地先查中间表的全部，然后过滤已经添加的，剩下的重新添加进中间表
const permissionIds = reqBody.permissionIds.map(item => Number(item));
const options = {
middleModel: 'RolePermissions', // 中间model名称
oneIdName: 'roleId', // 一对多，一在中间表的key
moreIdName: 'permissionId', // 一对多，多在中间表的key
reqIds: permissionIds, // 一对多 ，多的id数组集合
id: reqBody.id, // 一对多，一方的id
}
// 作增量更新中间表，需要先过滤部分数据，前端的permissionIds是全量提交的
const result = await updateMiddleTable(ctx, options);
if (result.isOk) {
ctx.success({ id: reqBody.id });
} else {
ctx.error(4002, '请检查权限id是否不存在');
}
-------------------------------增量更新中间表的方法--------------------------
async function updateMiddleTable(ctx, options) {
// const { ctx, app } = this; 如果整合在help可以用这句
// 开启事务
const transaction = await ctx.model.transaction();
try {
const { id, reqIds, middleModel, oneIdName, moreIdName } = options
const middleModelTable = ctx.model[middleModel];
const oldMiddleData = await middleModelTable.findAll({
raw: true,
where: {
roleId: id
}
});
console.log("🚀 ~ updateMiddleTable ~ oldMiddleData:", oldMiddleData)
const addMiddleIds = []
const removeMiddleIds = []
reqIds.forEach(item => {
let index = -1
if (oldMiddleData && oldMiddleData.length > 0) {
index = oldMiddleData.findIndex(it => it[moreIdName] === item)
}
console.log("🚀 ~ RolesController ~ joinPermissions ~ index:", item, index)
if (index === -1) { // 不存在则新增
addMiddleIds.push(item)
} else { // 存在则是不用新增
// 把保留的剔除，剩下是需要删除的
oldMiddleData.splice(index, 1)
}
})
oldMiddleData.forEach(item => { // 剩下是需要删除的
removeMiddleIds.push(item.id)
})
console.log("🚀 ~ RolesController ~ joinPermissions ~ removeMiddleIds:", removeMiddleIds)
console.log("🚀 ~ RolesController ~ joinPermissions ~ addMiddleIds:", addMiddleIds)
if (removeMiddleIds.length > 0) {
await middleModelTable.destroy({
where: {
id: removeMiddleIds
}
});
}
if (addMiddleIds.length > 0) {
for (let i = 0; i < addMiddleIds.length; i++) {
let middleItem = {}
middleItem[oneIdName] = id
middleItem[moreIdName] = addMiddleIds[i]
await middleModelTable.create(middleItem);
}
}
await transaction.commit(); // 提交事务
return {
isOk: true
}
} catch (error) {
await transaction.rollback(); // 回滚事务
return {
isOk: false,
error
}
}
}
多级关联查询时，include嵌套include，例如查询用户的权限，需要通过用户的角色再关联到权限，如下：
async getUserInfo() {
const { ctx, app } = this;
const model = ctx.model.User;
const id = ctx.helper.tokenInfo.userId;
try {
const user = await model.findOne({
attributes: {
exclude: ['deleted_at']
},
where: { id },
include: [
{
model: ctx.model.Roles,
required: false, // 解决：添加了where条件，当关联数据为空时，导致整个结果为null
where: {
type: 'admin'
},
attributes: ['id', 'roleName' ],
through: {
attributes: []
},
include: [
{
model: ctx.model.Permissions,
required: false,
attributes: ['id', 'permissionName', 'permissionCode', 'parentId'],
through: {
attributes: []
}
}
]
}
]
});
let allPermissions = []
if (user) {
// 用户多个角色下的权限重组
user.dataValues.roles.forEach(item => {
allPermissions = allPermissions.concat(item.permissions)
})
delete user.dataValues.roles;
const permissions = ctx.helper.getUniqueAry(allPermissions) // 数组对象去重，返回数组
user.dataValues.permissions = permissions.map(item => item.permissionCode)
ctx.success(user);
} else {
ctx.error(4002, '找不到该用户信息');
}
} catch (error) {
ctx.error(4002, error);
}
}
二，多对多，大家都共用一个中间表
注意：多个表共用同一个中间表时，该中间表不用设置外键
例如标签和文章，还可以标签和课程，用type区分是不同的中间表。下面是中间表字段
然而model上声明关联关系时需要注意，用scope区分：
// 文章的model中
Model.associate = function () {
Model.belongsToMany(model.Tags, {
through: {
model: model.ResourceTag,
unique: false,
scope: {
type: 'resource'
}
},
foreignKey: 'resourceId',
// otherKey: 'tagId',
constraints: false
});
};
// 课程course的model中
Model.associate = function () {
Model.belongsToMany(model.Tags, {
through: {
model: model.ResourceTag,
unique: false,
scope: {
type: 'course'
}
},
foreignKey: 'resourceId',
// otherKey: 'tagId',
constraints: false
});
};
1，列表上关联查询时，也是使用include关联相关表就好，例如查询文章列表和各自对应的标签是什么
async list() {
const { ctx, app, service } = this;
const query = ctx.query;
const rule = {
page: { type: 'number', required: true },
pageSize: { type: 'number', required: true }
};
if (!isNaN(query.page)) {
query.page = +query.page;
}
if (!isNaN(query.pageSize)) {
query.pageSize = +query.pageSize;
}
ctx.valid(rule, ctx.query);
const search = {
order: [
['releaseTime', 'DESC'],
['created_at', 'DESC'],
],
distinct: true,
include: [
{
model: ctx.model.Tags,
attributes: ['label', 'id', 'moduleId', 'parentId', 'typeId'],
through: {
attributes: []
}
}
],
attributes: {
exclude: ['deleted_at', 'content']
},
limit: query.pageSize,
offset: (query.page - 1) * query.pageSize,
where: {}
};
try {
const { rows, count } = await model.findAndCountAll(search);
ctx.success({
page: query.page,
pageSize: query.pageSize,
list: rows,
total: count
});
} catch (error) {
ctx.error(4002, error);
}
2，查询文章的所有标签 分组统计数量，并倒序返回(查询标签，include文章表)
async tagsCountlist() {
const { ctx, app } = this;
const query = ctx.query;
const rule = {
page: {
type: 'number',
required: true
},
pageSize: {
type: 'number',
required: true
},
label: {
type: 'string',
required: true
}
};
if (!isNaN(query.page)) {
query.page = +query.page;
}
if (!isNaN(query.pageSize)) {
query.pageSize = +query.pageSize;
}
ctx.valid(rule, query);
const model = ctx.model.Tags;
const { page, pageSize } = query;
const tableQuery = {
where: {},
order: [
['updated_at', 'DESC']
],
offset: (page - 1) * pageSize,
limit: pageSize,
include: [
{
model: ctx.model.Resources,
where: {},
attributes: [],
through: { // 去掉后， resources 数据不会再返回
attributes: []
}
}
];
};
if (label) {
tableQuery.where.label = {
[Op.substring]: label
};
}
try {
// 下面count查询速度较快，但实现不了按更新时间 倒序返回
// const res = await model.count({
// attributes: ['id', 'label'],
// group: 'id',
// ...tableQuery
// })
// console.log('res', res)
// TODO该查询时间比上面的count长一些
const includeObj = tableQuery.include[0]
delete includeObj.through // 去掉through属性，不再返回resources相关信息
tableQuery.include = [{ ...includeObj } ]
const res = await model.findAll({
subQuery: false, // 将嵌套查询转换为子查询，避免多次查询。
attributes: ['id', 'label', [fn('COUNT', col('resources.id')), 'resourceCount']],
group: 'id',
...tableQuery
})
ctx.success(res)
// 返回结果： [ {id: 111111, label: '热门', resourceCount: 100, ... }
} catch (error) {
ctx.error(4002, error);
}
}
