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?
This question is related to
node.js
sequelize.js
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"
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;
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
$
(e.g $and
, $or
...){freezeTableName: true}
set in the table model then Sequelize will query against the plural form of its name ( Student -> Students )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,
Source: Stackoverflow.com