node.js - Sequelize 嵌套包含的 Where 条件

Question

如何在 Sequelize 中使用 include 生成以下 sql 语句？

select
    *
from
    User_Role_Table as user_role
inner join User_Table as u on
    u.User_ID = user_role.User_ID
inner join Role_Table as r on
    r.User_Role_ID = user_role.User_Role_ID
where
    u.User_ID = u_id AND r.Role_Name = 'role_name'
    

我尝试将 where 条件放在 include 中，但如果没有找到记录，则会出现错误。

UserRole.findOne({
        where: {
            userId,
        },
        include: [
            {
                model: User,
                as: "users",
                required: true,
            },
            {
                model: Role,
                as: "roles",
                require: true,
                where: {
                    roleName,
                },
            },
        ],
    })

我是否可以将包含内的 where 条件放入外部初始 where 块中？我已经尝试了以下，但它返回错误: SequelizeDatabaseError: The multi-part identifier "UserRole.roleName" could not be bound.

UserRole.findOne({
        where: {
            userId,
            "$Role.roleName$": roleName,//roleName condition write here
        },
        include: [
            {
                model: User,
                as: "users",
                required: true,
            },
            {
                model: Role,
                as: "roles",
                require: true,
            },
        ],
    })

Answer 1

您可以在每个连接中包含 where。

const userRole = await UserRole.findOne({
  include: [
    {
      model: User,
      where: {
        User_ID: userId,
      },
    },
    {
      model: Role,
      where: {
        Role_Name: roleName,
      },
    },
  ],
});