如何使用Sequelize查询不具备特定角色的用户？

模型定义与关联 #

模型代码 #

const User = db.define(
  'users',
  {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: Sequelize.INTEGER
    },
    email: {
      type: Sequelize.STRING(255)
    },
  }
);
const UserRole = db.define(
  'user_roles',
  {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: Sequelize.INTEGER
    }
  },
);
const Role = db.define(
  'organization_roles',
  {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: Sequelize.INTEGER
    },
    roleName: Sequelize.STRING(255),
  },
);

关联关系 #

User.belongsToMany(Role, {
  as: 'roles',
  through: UserRole,
  foreignKey: 'userId'
});
Role.belongsToMany(User, {
  as: 'organizationUsers',
  through: UserRole,
  foreignKey: 'roleId'
});

问题描述 #

如何查询所有不具备特定角色的用户？例如：用户1拥有Teacher、Guardian角色，用户2拥有Teacher角色，想要查询不具备Guardian角色的用户，期望得到用户2。

解决方案 #

方法一：使用NOT EXISTS子查询 #

这种方式直接过滤掉已拥有目标角色的用户，性能表现较好：

const { Op } = require('sequelize');
const targetRoleName = 'Guardian';

const usersWithoutRole = await User.findAll({
  where: {
    [Op.notExists]: Sequelize.literal(`
      SELECT 1 FROM user_roles ur
      JOIN organization_roles r ON ur.roleId = r.id
      WHERE ur.userId = users.id AND r.roleName = '${targetRoleName}'
    `)
  },
  // 可选：关联查询用户现有角色，按需添加
  include: [{
    model: Role,
    as: 'roles',
    through: { attributes: [] }
  }]
});

方法二：左连接+空值判断 #

通过左连接目标角色关联表，筛选出未匹配到目标角色的用户：

const { Op } = require('sequelize');
const targetRoleName = 'Guardian';

const usersWithoutRole = await User.findAll({
  include: [{
    model: Role,
    as: 'roles',
    through: { attributes: [] },
    where: { roleName: targetRoleName },
    required: false // 开启左连接
  }],
  where: {
    '$roles.id$': null // 筛选未匹配到目标角色的用户
  }
});

注意事项 #

• 需确保已解构引入Sequelize操作符Op
• 如果用角色ID而非名称查询，只需将roleName替换为id，可避免角色名称重复的问题
• 若要同时排除多个角色，可调整条件为roleName: { [Op.in]: ['Guardian', 'Admin'] }

内容的提问来源于stack exchange，提问作者asdc