CODEWITHSUNDEEP
node.jssequelize.js
Morio
Morio

Reputation: 8822

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?

Upvotes: 125

Views: 272104

Answers (9)

Morio
Morio

Reputation: 8822

Sequelize version 3 & 4 support the format below using $or, but this is not the latest version/latest format; see other answers which use symbols like [Sequelize.db.Op.or] etc introduced in Sequelize version 5:

where: {
    LastName: "Doe",
    $or: [
        {
            FirstName: 
            {
                $eq: "John"
            }
        }, 
        {
            FirstName: 
            {
                $eq: "Jane"
            }
        }, 
        {
            Age: 
            {
                $gt: 18
            }
        }
    ]
}

Will generate

WHERE LastName='Doe' AND (FirstName = 'John' OR FirstName = 'Jane' OR Age > 18)

See the doc: http://docs.sequelizejs.com/en/latest/docs/querying/#where

Upvotes: 121

user1786647
user1786647

Reputation: 594

let options: FindOptions<any> = {}
let where: WhereOptions = [];

where.push({filedZ: 10});

if (query.search) {
        let tmp: WhereOptions = {
          [Op.or]: [
            {
              [Op.and]: {
                filedX: { [Op.like]: `%${query.search}%` },
              },
            },
            {
              [Op.and]: {
                filedY: { [Op.like]: `%${query.search}%` },
              },
            },
          ],
        };
        where.push(tmp)
}


options.where = where;

await some.findAndCountAll(options);

let options: FindOptions<any> = {}
let where: WhereOptions = [];

where.push({filedZ: 10});

if (query.search) {
        let tmp: WhereOptions = {
          [Op.or]: [
            {
              [Op.and]: {
                filedX: { [Op.like]: `%${query.search}%` },
              },
            },
            {
              [Op.and]: {
                filedY: { [Op.like]: `%${query.search}%` },
              },
            },
          ],
        };
        where.push(tmp)
}


options.where = where;

await some.findAndCountAll(options);

Upvotes: -1

Vikas Chauhan
Vikas Chauhan

Reputation: 1474

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

Upvotes: 2

Vijay Anand.M
Vijay Anand.M

Reputation: 91

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

This Works For Me !

Upvotes: 6

inmyth
inmyth

Reputation: 9050

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 )

Upvotes: 26

Muhammad Fari
Muhammad Fari

Reputation: 331

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"

Upvotes: 33

CoredusK
CoredusK

Reputation: 1229

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;

Upvotes: 87

evanhadfield
evanhadfield

Reputation: 409

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,

Upvotes: 40

Evan Siroky
Evan Siroky

Reputation: 9408

See the docs about querying.

It would be:

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

Upvotes: 21

Related Questions