我正在尝试为用户添加'角色' -每个用户可以有多个角色，角色不限于一定数量的用户-他们可以分配给尽可能多的用户需要。我遵循了一些教程，阅读了相当多的stackoverflow问题/答案，但似乎仍然无法使其工作。
user.model.js
'use strict';
const { Model } = require('sequelize');
const PROTECTED_ATTRIBUTES = ['password'];
module.exports = (sequelize, DataTypes) => {
class User extends Model {
toJSON() {
// hide protected fields
const attributes = { ...this.get() };
// eslint-disable-next-line no-restricted-syntax
for (const a of PROTECTED_ATTRIBUTES) {
delete attributes[a];
}
return attributes;
}
static associate(models) {
User.belongsToMany(models.Role, {
through: 'UserRoles',
as: 'roles',
foreignKey: 'roleId'
});
}
}
User.init({
name: {
type: DataTypes.STRING,
allowNull: false,
unique: true
},
email: {
type: DataTypes.STRING,
allowNull: false,
unique: true
},
password: {
type: DataTypes.STRING,
allowNull: false
},
lastLoginAt: {
type: DataTypes.DATE,
allowNull: true
},
lastIPAddress: {
type: DataTypes.STRING,
allowNull: true
}
}, {
sequelize,
modelName: 'User',
paranoid: true,
tableName: 'users'
});
return User;
};
role.model.js
'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
class Role extends Model {
static associate(models) {
Role.belongsToMany(models.User, {
through: 'UserRoles',
as: 'users',
foreignKey: 'userId'
});
}
};
Role.init({
roleName: DataTypes.STRING
}, {
sequelize,
modelName: 'Role',
});
return Role;
};
userroles.model.js
'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
class UserRoles extends Model {
static associate(models) {
// define association here
}
};
UserRoles.init({
userId: DataTypes.INTEGER,
roleId: DataTypes.INTEGER
}, {
sequelize,
modelName: 'UserRoles',
});
return UserRoles;
};
user.controller.js
async function getUsers(req, res, next) {
const name = req.query.name;
const condition = name ? { name: { [Op.like]: `%${name}%` } } : null;
User.findAll({ where: condition, include: { model: Role, as: 'roles'} })
.then(data => {
res.send(data);
})
.catch(err => {
const error = new createError(500, 'Some error occurred while retrieving users.');
return next(error);
});
}
查询返回一个'roles'数组，但其中没有任何内容，即使UserRoles表有一个分配给现有用户和现有角色的条目。
它生成的查询，如果有帮助的话:
SELECT "User"."id", "User"."name", "User"."email", "User"."password", "User"."lastLoginAt", "User"."lastIPAddress", "User"."createdAt", "User"."updatedAt", "User"."deletedAt", "roles"."id" AS "roles.id", "rol
es"."roleName" AS "roles.roleName", "roles"."createdAt" AS "roles.createdAt", "roles"."updatedAt" AS "roles.updatedAt", "roles->UserRoles"."userId" AS "roles.UserRoles.userId", "roles->UserRoles"."roleId" AS "roles.UserRoles.role
Id", "roles->UserRoles"."createdAt" AS "roles.UserRoles.createdAt", "roles->UserRoles"."updatedAt" AS "roles.UserRoles.updatedAt" FROM "users" AS "User" LEFT OUTER JOIN ( "UserRoles" AS "roles->UserRoles" INNER JOIN "role" AS "ro
les" ON "roles"."id" = "roles->UserRoles"."roleId") ON "User"."id" = "roles->UserRoles"."roleId" WHERE ("User"."deletedAt" IS NULL);
role.model.js
'use strict';
const {Model} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
class Role extends Model {
static associate(models) {
//...
Role.belongsToMany(models.UserRoles, {
as: 'userRoles',
foreignKey: 'roleId',
});
}
}
// ...
return Role;
};
User.findAll({where: condition, include: {model: Role, as: 'roles', include: ['userRoles']}})
^^^^^^^^^^^^^^^^^^^^^^