How to create dynamic where clause in sequelize query using $or and $and

Question

I'm trying to create dynamic where clause to filter data using sequelize. But I am stuck in using "OR" in where clause.

I have tried given below way to create dynamic where clause

let where = {};
if(obj.NoteCat)
   where.NoteCat = obj.NoteCat;
if(obj.NoteSubCat)
   where.NoteSubCat = obj.NoteSubCat;
where.NoteEntryDate = {
        [Op.and]: {
            [Op.gte]: obj.FromDate,
            [Op.lte]: obj.ToDate,
        }
};
where.NoteWeekEndingDate = {
        [Op.and]: {
            [Op.gte]: obj.FromDate,
            [Op.lte]: obj.ToDate,
        }
}; 
await table.findAll({
    where: where,
}

I expect given below query

SELECT *
FROM   table     
WHERE  NoteCat = ''
AND    NoteSubCat  = ''
OR     (NoteEntryDate      >= '2019-05-16 12:52:50.931' AND NoteEntryDate      <= '2019-05-16 12:52:50.931') 
OR     (NoteWeekEndingDate >= '2019-05-16 12:52:50.931' AND NoteWeekEndingDate <= '2019-05-16 12:52:50.931')

But the actual output is

SELECT *
FROM   table     
WHERE  NoteCat = ''
AND    NoteSubCat  = ''
AND    (NoteEntryDate      >= '2019-05-16 12:52:50.931' AND NoteEntryDate      <= '2019-05-16 12:52:50.931') 
AND    (NoteWeekEndingDate >= '2019-05-16 12:52:50.931' AND NoteWeekEndingDate <= '2019-05-16 12:52:50.931')

How I get expected result. I have already wasted my whole day. Any help in this regard is highly appreciated.

Thanks.

Use $or in a top level as in question below: https://stackoverflow.com/questions/20695062/sequelize-or-condition-object — Anonym, May 16 '19 at 13:08
@Anonym I have already checked the above link but unable to adjust in dynamic where clause. Can you please share updated version of my where clause — Ibtisam, May 16 '19 at 13:42

score 5 · Answer 1 · answered May 16 '19 at 14:04

You can create it in a reverse order, like so:

let where = {
  [Op.or]: {
    NoteEntryDate: {
      [Op.and]: {
        [Op.gte]: obj.FromDate,
        [Op.lte]: obj.ToDate,
      }
    },
    NoteWeekEndingDate: {
      [Op.and]: {
        [Op.gte]: obj.FromDate,
        [Op.lte]: obj.ToDate,
      }
    }
  }
};
if(obj.NoteCat)
  where.NoteCat = obj.NoteCat;
if(obj.NoteSubCat)
  where.NoteSubCat = obj.NoteSubCat;
await table.findAll({
  where: where,
}

thanks a lot my friend. Sometimes we are not thinking out of the box :) — Ibtisam, May 16 '19 at 16:02

How to create dynamic where clause in sequelize query using $or and $and

1 Answers1