'Sequelize OR condition object

By creating object like this

var condition=
{
  where:
  {
     LastName:"Doe"，
     FirstName:["John","Jane"],
     Age:{
       gt:18
     }
  }    
}

and pass it in

Student.findAll(condition)
.success(function(students){

})

It could beautifully generate SQL like this

"SELECT * FROM Student WHERE LastName='Doe' AND FirstName in ("John","Jane") AND Age>18"

However, It is all 'AND' condition, how could I generate 'OR' condition by creating a condition object?

node.jssequelize.js


Solution 1:[1]

String based operators will be deprecated in the future (You've probably seen the warning in console).

Getting this to work with symbolic operators was quite confusing for me, and I've updated the docs with two examples.

Post.findAll({
  where: {
    [Op.or]: [{authorId: 12}, {authorId: 13}]
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.findAll({
  where: {
    authorId: {
      [Op.or]: [12, 13]
    }
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Solution 2:[2]

Use Sequelize.or:

var condition = {
  where: Sequelize.and(
    { name: 'a project' },
    Sequelize.or(
      { id: [1,2,3] },
      { id: { lt: 10 } }
    )
  )
};

Reference (search for Sequelize.or)

Edit: Also, this has been modified and for the latest method see Morio's answer,

Solution 3:[3]

In Sequelize version 5 you might also can use this way (full use Operator Sequelize) :

var condition = 
{ 
  [Op.or]: [ 
   { 
     LastName: {
      [Op.eq]: "Doe"
      },
    },
   { 
     FirstName: {
      [Op.or]: ["John", "Jane"]
      }
   },
   {
      Age:{
        [Op.gt]: 18
      }
    }
 ]
}

And then, you must include this : 

const Op = require('Sequelize').Op

and pass it in :

Student.findAll(condition)
.success(function(students){ 
//
})

It could beautifully generate SQL like this :

"SELECT * FROM Student WHERE LastName='Doe' OR FirstName in ("John","Jane") OR Age>18"

Solution 4:[4]

For Sequelize 4

Query

SELECT * FROM Student WHERE LastName='Doe' 
AND (FirstName = "John" or FirstName = "Jane") AND Age BETWEEN 18 AND 24

Syntax with Operators

const Op = require('Sequelize').Op;

var r = await to (Student.findAll(
{
  where: {
    LastName: "Doe",
    FirstName: {
      [Op.or]: ["John", "Jane"]
    },
    Age: {
      // [Op.gt]: 18
      [Op.between]: [18, 24]
    }
  }
}
));

Notes

  • Avoid alias operators $ (e.g $and, $or ...) as these will be deprecated
  • Unless you have {freezeTableName: true} set in the table model then Sequelize will query against the plural form of its name ( Student -> Students )

Solution 5:[5]

See the docs about querying.

It would be:

$or: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)

Solution 6:[6]

where: {
          [Op.or]: [
            {
              id: {
                [Op.in]: recordId,
              },
            }, {
              id: {
                [Op.eq]: recordId,
              },
            },
          ],
        },

This Works For Me !

Solution 7:[7]

For those who are facing issue in making more complex query like -

// where email = '[email protected]' AND (( firstname = 'first' OR lastname = 'last' ) AND age > 18)

would be:

[Op.and]: [
    {
        "email": { [Op.eq]: '[email protected]' }
        // OR "email": '[email protected]'
    },
    {
        [Op.and]: [
            {
                [Op.or]: [
                    {
                        "firstname": "first"
                    },
                    {
                        "lastname": "last"
                    }
                ]
            },
            {
                "age": { [Op.gt]: 18 }
            }]
    }
]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 CoredusK
Solution 2 Community
Solution 3
Solution 4
Solution 5 Evan Siroky
Solution 6 Vijay Anand.M
Solution 7 Vikas Chauhan

Related Questions