Sequelize OR condition object

Question

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?

i've seen 3 ways or operator being used... `where: { $or : [ {attr:val}, {attr:val}] }` , `where : { $or : { attr:val, attr2:val} }` , `where: { attr: { $or: [val, val] } }` — Muhammad Umer, Oct 01 '18 at 00:42

score 119 · Accepted Answer · edited Feb 13 '16 at 05:07

119

Seems there is another format now

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

edited Feb 13 '16 at 05:07

Robin Huy

960
7
23

answered Jul 13 '15 at 17:58

Morio

8,463
5
25
29

2

This should be `$or: { ... }`. Note, the `[` and `]` should become `{` and `}` – briangonzalez Sep 05 '15 at 23:04
I prefer this way before the and/or functional way @evanhadfield suggested since it's neater. – mraxus Nov 16 '15 at 22:57
how would i do the following in sequelize? ...where (A or B) and (C or D) – user3662456 Apr 19 '19 at 23:22
5

Use `[Op.or]` now from sequelize, `const { Op } = require('sequelize')`, instead of `$or`, as your key for sequelize 5+. They changed them to symbols. – kidcapital Jan 28 '20 at 13:31

score 83 · Answer 2 · answered Nov 16 '17 at 10:43

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;

i've seen 3 way or operator being used... ` where: { $or : [ {attr:val}, {attr:val}] } ` , ` where : { $or : { attr:val, attr2:val} } ` , ` where: { attr: { $or: [val, val] } } ` — Muhammad Umer, Oct 01 '18 at 00:41

score 40 · Answer 3 · edited May 23 '17 at 12:02

40

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,

edited May 23 '17 at 12:02

Community

1
1

answered Mar 02 '14 at 21:37

evanhadfield

409
5
3

Muhammad Fari · Answer 4 · 2019-06-23T06:30:41.700

31

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"

edited Jun 23 '19 at 06:30

answered Jun 23 '19 at 06:24

Muhammad Fari

311
3
5

Thanks a ton! You saved my life bro. I was getting error for ('Sequelize/type'), your detailed answer with import did help me. – nadafafif May 20 '21 at 19:38

inmyth · Answer 5 · 2021-03-24T14:03:00.647

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 )

score 21 · Answer 6 · answered Sep 12 '15 at 20:56

21

See the docs about querying.

It would be:

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

answered Sep 12 '15 at 20:56

Evan Siroky

9,040
6
54
73

Looks like the syntax has changed; this format no longer works (Sequelize 6.6.5 on my system). From their docs: https://sequelize.org/master/manual/model-querying-basics.html#examples-with--code-op-and--code--and--code-op-or--code- – Rory Mar 17 '22 at 00:22

score 5 · Answer 7 · answered Feb 17 '22 at 12:34

5

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

This Works For Me !

answered Feb 17 '22 at 12:34

Vijay Anand.M

81
1
1

It helps more if you supply an explanation why this is the preferred solution and explain how it works. We want to educate, not just provide code. – the Tin Man Feb 21 '22 at 20:36

score 2 · Answer 8 · answered May 17 '22 at 15:08

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

score -1 · Answer 9 · answered May 03 '23 at 21:56

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

Sequelize OR condition object

9 Answers9

Linked