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: stackoverflow.com/questions/20695062/… — Anonym
– Anonym, Commented May 16, 2019 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
– Ibtisam, Commented May 16, 2019 at 13:42

TheWildHealer · Accepted Answer · 2019-05-16 14:04:05Z

6

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,
}

answered May 16, 2019 at 14:04

TheWildHealer

1,6461 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ibtisam Over a year ago

thanks a lot my friend. Sometimes we are not thinking out of the box :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related