如何使用Sequelize生成关联表查询？含用户-角色场景

嘿，我来帮你解决这个问题，结合你的需求和现有模型，咱们先把模型里的小问题修正，再一步步实现你要的功能：

首先，你的userRole模型里有个明显的错误——userId字段被误设成了主键还开了自增，这肯定会出问题（毕竟表已经有id作为主键了），而且原模型里你把role字段写成了password，这显然不是你要的，正确的模型定义应该是这样：

const userRole = sequelize.define('userRole', {
    id: {
        type: DataTypes.BIGINT,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true,
        field: 'id'
    },
    userId: {
        type: DataTypes.BIGINT,
        allowNull: false,
        field: 'user_id'
    },
    role: { // 这里应该是role字段，对应你的SQL里的role属性
        type: DataTypes.STRING(200),
        allowNull: false,
        field: 'role'
    }
}, { tableName: 'user_role' }); // 数据库表名和你的SQL里的user_role保持一致，下划线命名更规范

关联定义可以调整成和模型字段对应（其实之前的也能用，但调整后逻辑更清晰）：

user.hasMany(models.userRole, { foreignKey: 'userId', as: 'roles' });
userRole.belongsTo(models.user, { foreignKey: 'userId', as: 'user' });

接下来实现你要的核心需求：当用户的任一角色匹配搜索条件时，返回该用户及其所有关联角色：

方式一：贴近你给出的SQL语句实现 #

如果要严格对应你写的SQL结构，我们可以用Sequelize的literal()结合子查询来实现，同时加入搜索条件：

const { Op } = require('sequelize');
const searchRole = 'admin'; // 假设这是前端传入的搜索关键词

const users = await user.findAll({
    include: [
        {
            model: userRole,
            as: 'roles',
            attributes: [],
            required: true, // 对应INNER JOIN，确保只返回有匹配角色的用户
            where: sequelize.literal(`EXISTS (
                SELECT 1 FROM user_role ur 
                WHERE ur.user_id = user.id 
                AND ur.role LIKE '%${searchRole}%'
            )`)
        }
    ],
    attributes: {
        include: [
            // 用子查询获取当前用户的所有角色，聚合为数组
            [sequelize.literal(`(
                SELECT JSON_ARRAYAGG(ur.role) 
                FROM user_role ur 
                WHERE ur.user_id = user.id
            )`), 'rolesList']
        ]
    },
    group: ['user.id'], // 按用户ID分组，避免重复返回同一用户
    order: [[sequelize.literal('rolesList'), 'ASC']] // 按角色排序
});

方式二：更符合Sequelize风格的实现（推荐） #

如果不需要严格照搬SQL结构，优先考虑业务逻辑清晰和可维护性，我们可以分两步走：先筛选出有匹配角色的用户ID，再查询这些用户的完整信息及所有角色。这种方式更适合前端表格展示的需求：

const { Op } = require('sequelize');
const searchRole = 'admin';

// 第一步：找出所有拥有匹配角色的用户ID
const matchedUserIds = await userRole.findAll({
    attributes: ['userId'],
    where: {
        role: {
            [Op.like]: `%${searchRole}%`
        }
    },
    group: ['userId'] // 去重，避免同一个用户ID多次返回
}).then(rows => rows.map(row => row.userId));

// 第二步：查询这些用户的所有信息及关联的所有角色
const users = await user.findAll({
    include: [
        {
            model: userRole,
            as: 'roles',
            required: false // 用LEFT JOIN确保即使角色有变动也能返回，但我们已经筛选了有匹配角色的用户
        }
    ],
    where: {
        id: {
            [Op.in]: matchedUserIds
        }
    }
});

这样返回的结果里，每个用户都会带上自己所有的角色，同时只有角色匹配搜索条件的用户才会被返回，完全符合你的API需求。

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