Sequelize嵌套include中的Where条件

Question

如何使用include在Sequelize中生成以下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,
},
},
],
});