Sequelize OR condition object

user_1643850 0
Viewed 204012

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?

sequelize.js node.js
8 Answers

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;
user_56260620

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"
user_112254580 
where: {
          [Op.or]: [
            {
              id: {
                [Op.in]: recordId,
              },
            }, {
              id: {
                [Op.eq]: recordId,
              },
            },
          ],
        },

This Works For Me !

user_159202000

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

// where email = 'xyz@mail.com' AND (( firstname = 'first' OR lastname = 'last' ) AND age > 18)

would be:

[Op.and]: [
    {
        "email": { [Op.eq]: 'xyz@mail.com' }
        // OR "email": 'xyz@mail.com'
    },
    {
        [Op.and]: [
            {
                [Op.or]: [
                    {
                        "firstname": "first"
                    },
                    {
                        "lastname": "last"
                    }
                ]
            },
            {
                "age": { [Op.gt]: 18 }
            }]
    }
]
user_52331510
Related