左排除加入续集 [英] 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

我如何在续集中做到这一点?我是否只需要做一个左连接并手动处理它?<​​/p>

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屋！