左不包括加入续集 [英] Left excluding join in sequelize
问题描述
我有两张表,其中一张有另一张的 ID.1:1 的关系.所以像
I have two tables, where one table has the ID of the other. 1:1 relation. So something like
EventFeedback
somePrimaryKey
userEventID
UserEvent
userEventID
Sequalize 具有用
Sequalize has the relation defined with
models.UserEvent.hasOne(models.EventFeedback, { foreignKey: 'userEventID' });
我需要 UserEvent
中没有 EventFeedback
中的条目的所有条目,这是一个排他性连接.从
I need all entries in UserEvent
that do not have an entry in EventFeedback
, which is an exclusionary join.
Stealing images from
他们甚至给出了示例代码!
They even give example code!
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL
我如何在 sequelize 中做到这一点?我只需要进行左连接并手动处理吗?
How do I do this in sequelize? Do I just need to do a left join and process it manually?
推荐答案
查询UserEvent
时需要预先加载EventFeedback
,并添加正确的where
子句.您还需要定义结果中不需要 EventFeedback
,因此查询将生成 LEFT JOIN
而不是 INNER JOIN
You need to eager load EventFeedback
when querying UserEvent
and add proper where
clause. You also need to define that EventFeedback
is not required in the result so the query will generate LEFT JOIN
instead INNER JOIN
UserEvent.all({
include: [
model: EventFeedback,
required: false, // do not generate INNER JOIN
attributes: [] // do not return any columns of the EventFeedback table
],
where: sequelize.where(
sequelize.col('EventFeedback.userEventID'),
'IS',
null
)
}).then(userEvents => {
// user events...
});
在上面的代码中,sequelize
是 Sequelize 的一个实例,其中定义了模型.也可以参考sequelize的文档.where()
和 sequelize.col()
方法.
In the code above the sequelize
is an instance of Sequelize with model defined in it. You can also refer to the documentation of sequelize.where()
and sequelize.col()
methods.
这篇关于左不包括加入续集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!