基于外部表数据的过滤器

Question

我有一个Users表和一个Staff表。Users有一个列作为TenantId, Staff有一个UsersId外键。我使用以下代码来获取Staff和他的User:

const listofStaff = await staff.findAll({
include:[
{
model: Users, 
attributes: ['TenantId']
}] }
)

检索以下结果:

[
{
"id": 1,
"profession": "Dentist",
"locations": "1",
"services": "1",
"note": "Good Man",
"holidays": "Saturday",
"createdAt": "2022-12-14T10:30:54.000Z",
"updatedAt": "2022-12-14T10:30:54.000Z",
"roleId": null,
"UserId": 1,
"timesheetId": null,
"User": {
"TenantId": 1
}
}
]

我现在要做的是添加一个基于TenantId的Where条件，例如:

WHERE: {TenantId: 2}

我该怎么做?

Answer 1

您可以轻松地在include选项中添加条件，就像在根级别(include旁边:

)添加条件一样。

const listofStaff = await staff.findAll({
include:[
{
model: Users, 
attributes: ['TenantId'],
required: true,
where: {
TenantId: tenantId
}
}]
})