sequelize where relation is null

Question

I have an issue with sequelize. Here is what I'm trying to do:

return pointsTransaction.findAndCountAll({
    where:{
        user_id: userId
    },
    limit: opts.limit,
    offset: opts.offset,
    include:[{
        model:sequelize.models.pointsPendingtransaction
    }]
});

The generated query looks like that:

SELECT "pointsTransaction".*,
"pointsPendingtransactions"."transaction_ptr_id" AS "pointsPendingtransactions.transactionPtrId",
"pointsPendingtransactions"."is_active" AS "pointsPendingtransactions.isActive",
"pointsPendingtransactions"."transaction_id" AS "pointsPendingtransactions.transaction_id" 
FROM (
SELECT "pointsTransaction"."id",
"pointsTransaction"."date_time" AS "dateTime",
"pointsTransaction"."details",
"pointsTransaction"."points",
"pointsTransaction"."user_id" 
FROM "points_transaction" AS "pointsTransaction" 
WHERE "pointsTransaction"."user_id" = 10002 LIMIT 1000
) AS "pointsTransaction" 
LEFT OUTER JOIN "points_pendingtransaction" AS "pointsPendingtransactions" 
ON "pointsTransaction"."id" = "pointsPendingtransactions"."transaction_id"

So in SQL I would just need to add that line at the end of my query to make it work: WHERE "pointsPendingtransactions"."transaction_id" IS null

So my question is, how can I do that with sequelize? I tried many different ways but no one worked...

Something like: include:[{ model:sequelize.models.pointsPendingtransaction, where:{transaction_id:null} }] should work althought i haven't tried this myself — Molda, Sep 28 '15 at 10:59
I tried it, also tried it with some having based on a count but nothing is working. I suspect an error due to "findAndCountAll" — Patrick Mettraux, Sep 29 '15 at 05:50
i know its late, but you can check my answer here https://stackoverflow.com/questions/48005763/load-items-where-relation-is-null-in-sequelize/48008961#48008961 — Ewomazino Ukah, Dec 28 '17 at 14:12

score 24 · Answer 1 · answered Aug 30 '19 at 15:13

If you are using Sequelize's symbol operators from Sequelize.Op then ...

return pointsTransaction.findAndCountAll({
    where:{
        user_id: userId
    },
    limit: opts.limit,
    offset: opts.offset,
    include:[{
        model:sequelize.models.pointsPendingtransaction,
        where: {
            transaction_id: {
              // "$eq" changes to "[Op.eq]"
              [Op.eq]: null
            }
        }
    }]
});

score 11 · Answer 2 · answered Apr 13 '17 at 12:40

11

Try to do next

        where: {
            transaction_id: {
              $eq: null
            }
        }

to generate IS NULL

return pointsTransaction.findAndCountAll({
    where:{
        user_id: userId
    },
    limit: opts.limit,
    offset: opts.offset,
    include:[{
        model:sequelize.models.pointsPendingtransaction,
        where: {
            transaction_id: {
              $eq: null
            }
        }
    }]
});

answered Apr 13 '17 at 12:40

a chernyavenko

147
1
4

1

All this does is put an extra condition on the join, it does not do what he's wanting. Tried it myself. A where clause is needed after the left outer join but putting a condition in the outer where clause adds it to this section `WHERE "pointsTransaction"."user_id" = 10002 LIMIT 1000` – Nemesis02 May 08 '20 at 14:54

score 1 · Answer 3 · answered Oct 17 '22 at 16:22

Tested on Sequelize 5.22.4. You should use Op.is instead of eq.

eq will produce a:

(...) where transactions_id="" (...)

While Op.is will produce a

(...) where transaction_id is NULL (...)

return pointsTransaction.findAndCountAll({
    where:{
        user_id: userId
    },
    limit: opts.limit,
    offset: opts.offset,
    include:[{
        model:sequelize.models.pointsPendingtransaction,
        where: {
            transaction_id: {
              // "$is" changes to "[Op.is]"
              [Op.is]: null
            }
        }
    }]
});

score 0 · Answer 4 · answered Aug 30 '22 at 11:57

$eq and [Op.eq] are not needed tested as of Sequelize 6.14.0

It works without them, just as it does for other values, e.g.:

where: {
  transaction_id: null
}

produces IS NULL.

You do need the magic syntax for IS NOT NULL however as usual, achieved with:

where: {
  transaction_id: {
    [Op.ne]: null
  }
}

The where inside include part of answer is then the same as for other non null values, see e.g.: Sequelize find based on association

sequelize where relation is null

4 Answers4