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...

Answer 1

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

Answer 2

$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

Answer 3

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

Answer 4

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