CODEWITHSUNDEEP
sequelize.js
user10033391
user10033391

Reputation:

how to make where using 'or' operator in sequelize?

I would like to know how to use 'or' operator in that case:

getOrderDeadline(order) {
  return order.getDeadlines({
    limit: 1,
    where: {
      '$OrdersDeadlines.initialDate$': { [this.Sequelize.Op.lte]: new Date() },
      '$OrdersDeadlines.finishDate$': { [this.Sequelize.Op.gte]: new Date() },
    },
    order: [['situationId', 'DESC']],
  });
}

I need to get a deadline inside my current date, but sometime a deadline may have an indeterminate date, a null value in finishDate column. So I need to use 'or' operator.

Upvotes: 0

Views: 1227

Answers (2)

user10033391
user10033391

Reputation:

Well, based on @PhilippeAuriach answer, I solved my problem. So I will explain why I used the code below. In our App an Order have situations, a situation represents the current state of an Order and it has many deadlines, for Situations and Deadlines we have a table for each other with Name and ID. So I limited my select to one and get the row with the last situation, but sometimes a deadline may have a undefined finish date. What I did not understand is how to use the Operator, for me it is a little bit confusing, but now I see that I need to wrap my conditions using the Operator in Sequelize. So here is the code: 

getOrderDeadlines(order) {
  const Operator = this.Sequelize.Op;
  const currentDate = new Date();
  return order.getDeadlines({
    limit: 1,
    where: {
      [Operator.or]: [
        {
          '$OrdersDeadlines.initialDate$': { [Operator.lte]: currentDate },
          '$OrdersDeadlines.finishDate$': { [Operator.gte]: currentDate },
        },
        {
          '$OrdersDeadlines.initialDate$': { [Operator.lte]: currentDate },
          '$OrdersDeadlines.finishDate$': null,
        },
      ],
    },
    order: [['situationId', 'DESC']],
  });
}

@PhilippeAuriach, Thank you.

Upvotes: 0

PhilippeAuriach
PhilippeAuriach

Reputation: 2447

You should use the or operator the following way :

getOrderDeadline(order) {
  const Op = this.Sequelize.Op;
  const now = new Date();
  return order.getDeadlines({
    limit: 1,
    where: {
      [Op.or]: [
         [Op.and]: [
            {'$OrdersDeadlines.initialDate$': { [Op.lt]: now }},
            {'$OrdersDeadlines.finishDate$': { [Op.gt]: now }},
         ],
         {'$OrdersDeadlines.initialDate$': { [Op.lt]: now }},
      ]
    },
    order: [['situationId', 'DESC']],
  });
}

Updated according to your comment. Aside note : as you said '<' I used Op.gt and Op.lt instead of Op.gte and Op.lte.

Also, this corresponds to the following query as you wanted :

   ( initialDate < currentDate and finishDate > currentDate ) 
or ( initialDate < currentDate )

which is exactly the same as just ( initialDate < currentDate )

Upvotes: 1

Related Questions