sequelizeを使用して、Where句に条件を書こうとするときに調べることが多いので、よく使うものをまとめます。
環境
DB：MySQL
sequelizeのバージョン：4.38.0
node.jsのバージョン：7.10.0
以下、コードについてはそのままコピペでは動きません(モデル定義等必要です)。
あくまでWhere句部分を参考にしてもらえればと思います。
一番下に早見表もあります。
where id= 2
models.Book.findAll({
where: {
id : 2
}
}).then(function(books){
console.log(books);
});
where id != 2
models.Book.findAll({
where: {
id : {
$ne: 2
}
}
}).then(function(books){
console.log(books);
});
where id = 2 AND price = 500
models.Book.findAll({
where: {
id : 2,
price: 500
}
}).then(function(books){
console.log(books);
});
where id = 2 OR price = 500
models.Book.findAll({
where: {
$or: [
{id: 2},
{price: 500}
]
}
}).then(function(books){
console.log(books);
});
一つのカラムで複数の値を調べたい場合は以下でも可。
models.Book.findAll({
where: {
price : {
$or : [500, 0]
}
}
}).then(function(books){
console.log(books);
});
where price > 500
models.Book.findAll({
where: {
price : {
$gt: 500
}
}
}).then(function(books){
console.log(books);
});
where price >= 500
models.Book.findAll({
where: {
price : {
$gte: 500
}
}
}).then(function(books){
console.log(books);
});
where price < 500
models.Book.findAll({
where: {
price : {
$lt: 500
}
}
}).then(function(books){
console.log(books);
});
where price <= 500
models.Book.findAll({
where: {
price : {
$lte: 500
}
}
}).then(function(books){
console.log(books);
});
where price BETWEEN 500 AND 1000
models.Book.findAll({
where: {
price : {
$between: [500, 1000]
}
}
}).then(function(books){
console.log(books);
});
where price NOT BETWEEN 500 AND 1000
models.Book.findAll({
where: {
price : {
$notBetween: [500, 1000]
}
}
}).then(function(books){
console.log(books);
});
where id IN (1,5)
models.Book.findAll({
where: {
id : {
$in: [1, 5]
}
}
}).then(function(books){
console.log(books);
});
where id NOT IN (1,5)
models.Book.findAll({
where: {
id : {
$notIn: [1, 5]
}
}
}).then(function(books){
console.log(books);
});
where title LIKE '%巻'
models.Book.findAll({
where: {
title : {
$like: '%巻'
}
}
}).then(function(books){
console.log(books);
});
where title NOT LIKE '%巻'
models.Book.findAll({
where: {
title : {
$notLike: '%巻'
}
}
}).then(function(books){
console.log(books);
});
where price IS NULL
models.Book.findAll({
where: {
price : null
}
}).then(function(books){
console.log(books);
});
where price IS NOT NULL
models.Book.findAll({
where: {
price : {
$ne: null
}
}
}).then(function(books){
console.log(books);
});
参考