这是一个极限和关联的问题。类似问题:https://github.com/sequelize/sequelize/issues/7585 https://github.com/sequelize/sequelize/issues/7585
Sequelize 将关联转变为子查询,但在您的用例中,您需要一个关联,然后限制总体关联结果。
为此,您可以使用subQuery: false
禁用子查询。
await A.findOne({
include: [{
model: B,
required: false,
where: {
'AId': null,
}
}],
subQuery: false
});
这仍然无法实现你想要的,因为where
in include
将生成AND
的条件ON
.
生成的查询:
SELECT `A`.`id`, `A`.`aField`, `A`.`createdAt`, `A`.`updatedAt`, `Bs`.`id` AS `Bs.id`,
`Bs`.`bField` AS `Bs.bField`, `Bs`.`createdAt` AS `Bs.createdAt`,
`Bs`.`updatedAt` AS `Bs.updatedAt`, `Bs`.`AId` AS `Bs.AId`
FROM `As` AS `A`
LEFT OUTER JOIN `Bs` AS `Bs` ON `A`.`id` = `Bs`.`AId` AND `Bs`.`AId` IS NULL
LIMIT 1;
你想要的是
...
... ON `A`.`id` = `Bs`.`AId` WHERE `Bs`.`AId` IS NULL
LIMIT 1;
为了得到这个WHERE
,你可以把where
条款为A
.
await A.findOne({
include: [{
model: B,
required: false
}],
where: {
'$Bs.AId$': null
},
subQuery: false
});