Sequelize - Multiple Op.Or operations for the same field

Question

How do I create a Sequelize filter that will check for multiple ranges of the same field.

In simple SQL query this would have been a straight forward thing to day but proves to be a bit tricky in Sequelize. I have tried few things but not getting the desired result with any of them

I am simplifying the code in this question because the actual code is very lengthy and has many parts that are not relevant to this question.

// This filter is added successfully
filters.advertised_price_weekly__c = { [Op.gt]: 0 }; 

// This filter 
filters.numberField = { 
[Op.Or]: [ 
  { [Op.gte]: 1, [Op.lte]: 3 }, 
  { [Op.gte]: 5, [Op.lte]: 7 },
  { [Op.gte]: 9, [Op.lte]: 11 } 
 ]
}


// Then call using the filter
    let options = {
      page: page, // Default 1
      paginate: limit, // Default 50
      attributes:  filterAttributes,
      where: filters,
    };

    return await this.query().paginate(options)

The query generated by Sequelize only doesn't include any of

AND "listing__c"."numberField" = 'NaN

The expected query should be something like:

SELECT * FROM table WHERE numberField>1 AND numberField<3 OR  numberField>5 AND numberField<9 OR  numberField>9 AND numberField<11

I think you have typo, it should be `[Op.or]` and not `[Op.Or]`, please change that and try to execute. — Vivek Doshi, Aug 14 '19 at 10:57
Actually this did the trick! Thanks for spotting it @VivekDoshi! — Siv, Aug 16 '19 at 08:49

Vivek Doshi · Answer 1 · 2019-08-14T11:07:09.873

2

First Change [Op.Or] to [Op.or] and try, and then you can try out the below once:

where: {
    $or: [
        {
            numberField : { [Op.between] : [1,3] },
        }, 
        {
            numberField : { [Op.between] : [5,7] },
        }, 
        {
            numberField : { [Op.between] : [9,11] },
        }
    ]
}

OR,

where: {
    numberField : {
        $or: [
            { [Op.between] : [1,3] },
            { [Op.between] : [5,7] },
            { [Op.between] : [9,11] },
        ]
    }
}

NOTE : I havn't tried out the last one, but it should work

edited Aug 14 '19 at 11:07

answered Aug 14 '19 at 10:51

Vivek Doshi

56,649
12
110
122

Thanks for your suggestion Vivek, I tried it and still getting NaN in the SQL query it is generating... ```AND "listing__c"."geolocation__latitude__s" = 'NaN' AND "listing__c"."geolocation__longitude__s" = 'NaN' ``` – Siv Aug 16 '19 at 08:30
My code looks like this now: ``` filters.geolocation__latitude__s = { $or: [ { geoLocationLat : { [Op.between] : 1, 3 ] }, }, { geoLocationLat : { [Op.between] : [5, 7 ] }, }, { geoLocationLat : { [Op.between] : [9, 11] }, } ]} ``` – Siv Aug 16 '19 at 08:34
@Siv, will you please accept and upvote the answer? – Vivek Doshi Aug 16 '19 at 08:58

Sequelize - Multiple Op.Or operations for the same field

1 Answers1