Left excluding join in sequelize

8
votes

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 has the relation defined with 

models.UserEvent.hasOne(models.EventFeedback, { foreignKey: 'userEventID' });

I need all entries in UserEvent that do not have an entry in EventFeedback, which is an exclusionary join. Stealing images from this article because they have nice individual images: left excluding join

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

How do I do this in sequelize? Do I just need to do a left join and process it manually?

2
sequelize.js

2 Answers

10
votes

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...
});

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.

1
votes

By default SEQUALIZE always use INNER JOIN. It's very easy to make it LEFT JOIN. Just add the...

required: false

along with the code. Follow the sample query code.

 UserModel.findAll({
        attributes: {
            exclude: ['role_id', 'username', 'password', 'otp', 'active']
        },
        where: {
            active: 1,
            role_id: 2
        },
        include: [{
            model: StateModel,
            attributes: ['id', 'short_name'],
            as: 'state_details',
            where: {
                active: 1
            },
            required: false
        }]
    }).then(List => {
        console.log(List);
    }).catch(err => {
        console.log(err);            
 });