查询
属性
如果只想查询某些特定的属性，而不加载模型的所有字段值，使用 attributes 选项：
Model.findAll({
attributes: ['foo', 'bar']
});
SELECT foo, bar ...
字段名可以通过一个嵌套的数组重写：
Model.findAll({
attributes: ['foo', ['bar', 'baz']]
});
SELECT foo, bar as baz ...
你还可以使用 sequelize.fn 进行聚合：
Model.findAll({
attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});
SELECT COUNT(hats) AS no_hats ...
当你使用聚合函数时，你必须提供一个用于访问其值的的别名，在上面的示例中，我们可以以下面这样的方式获取 hats 的数量：
instance.get('no_hats')
如果你想除了聚合之外，还要展示模型其它字段的值：
// This is a tiresome way of getting the number of hats...
Model.findAll({
attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});
// This is shorter, and less error prone because it still works if you add / remove attributes
Model.findAll({
attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
});
SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...
同样的，你还可以方便的去掉一些字段：
Model.findAll({
attributes: { exclude: ['baz'] }
});
SELECT id, foo, bar, quz ...
Where 查询
当你进行 find、findAll 查询或者批量 update 或者 destroys 时，可以添加一个 where 查询对象，该对象可以包含多个 key: value 对，而 value 可以直接是值或者一个操作对象，在 where 查询中，还可以使用 $or 与 $and。
基础使用
Post.findAll({
where: {
authorId: 2
}
});
// SELECT * FROM post WHERE authorId = 2
Post.findAll({
where: {
authorId: 12,
status: 'active'
}
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';
Post.destroy({
where: {
status: 'inactive'
}
});
// DELETE FROM post WHERE status = 'inactive';
Post.update({
updatedAt: null,
}, {
where: {
deletedAt: {
$ne: null
}
}
});
// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;
Post.findAll({
where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;
操作符
$and: {a: 5} // AND (a = 5)
$or: [{a: 5}, {a: 6}] // (a = 5 OR a = 6)
$gt: 6, // > 6
$gte: 6, // >= 6
$lt: 10, // < 10
$lte: 10, // <= 10
$ne: 20, // != 20
$eq: 3, // = 3
$not: true, // IS NOT TRUE
$between: [6, 10], // BETWEEN 6 AND 10
$notBetween: [11, 15], // NOT BETWEEN 11 AND 15
$in: [1, 2], // IN [1, 2]
$notIn: [1, 2], // NOT IN [1, 2]
$like: '%hat', // LIKE '%hat'
$notLike: '%hat' // NOT LIKE '%hat'
$iLike: '%hat' // ILIKE '%hat' (case insensitive) (PG only)
$notILike: '%hat' // NOT ILIKE '%hat' (PG only)
$regexp: '^[h|a|t]' // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
$notRegexp: '^[h|a|t]' // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
$iRegexp: '^[h|a|t]' // ~* '^[h|a|t]' (PG only)
$notIRegexp: '^[h|a|t]' // !~* '^[h|a|t]' (PG only)
$like: { $any: ['cat', 'hat']}
// LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
$overlap: [1, 2] // && [1, 2] (PG array overlap operator)
$contains: [1, 2] // @> [1, 2] (PG array contains operator)
$contained: [1, 2] // <@ [1, 2] (PG array contained by operator)
$any: [2,3] // ANY ARRAY[2, 3]::INTEGER (PG only)
$col: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example
区间操作
区间类型可以使用所有查询操作：
// All the above equality and inequality operators plus the following:
$contains: 2 // @> '2'::integer (PG range contains element operator)
$contains: [1, 2] // @> [1, 2) (PG range contains range operator)
$contained: [1, 2] // <@ [1, 2) (PG range is contained by operator)
$overlap: [1, 2] // && [1, 2) (PG range overlap (have points in common) operator)
$adjacent: [1, 2] // -|- [1, 2) (PG range is adjacent to operator)
$strictLeft: [1, 2] // << [1, 2) (PG range strictly left of operator)
$strictRight: [1, 2] // >> [1, 2) (PG range strictly right of operator)
$noExtendRight: [1, 2] // &< [1, 2) (PG range does not extend to the right of operator)
$noExtendLeft: [1, 2] // &> [1, 2) (PG range does not extend to the left of operator)
组合
{
rank: {
$or: {
$lt: 1000,
$eq: null
}
}
}
// rank < 1000 OR rank IS NULL
{
createdAt: {
$lt: new Date(),
$gt: new Date(new Date() - 24 * 60 * 60 * 1000)
}
}
// createdAt < [timestamp] AND createdAt > [timestamp]
{
$or: [
{
title: {
$like: 'Boat%'
}
},
{
description: {
$like: '%boat%'
}
}
]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'
JSONB
JSONB 可以通过三种方式查询：
嵌套对象
{
meta: {
video: {
url: {
$ne: null
}
}
}
}
嵌套键
{
"meta.audio.length": {
$gt: 20
}
}
包含语法
{
"meta": {
$contains: {
site: {
url: 'http://google.com'
}
}
}
}
关联
// Find all projects with a least one task where task.state === project.state
Project.findAll({
include: [{
model: Task,
where: { state: Sequelize.col('project.state') }
}]
})
分页与数量限制
// Fetch 10 instances/rows
Project.findAll({ limit: 10 })
// Skip 8 instances/rows
Project.findAll({ offset: 8 })
// Skip 5 instances and fetch the 5 after that
Project.findAll({ offset: 5, limit: 5 })
排序
order 可以通过一个数组接收多个排序方式：
Subtask.findAll({
order: [
// Will escape username and validate DESC against a list of valid direction parameters
['title', 'DESC'],
// Will order by max(age)
sequelize.fn('max', sequelize.col('age')),
// Will order by max(age) DESC
[sequelize.fn('max', sequelize.col('age')), 'DESC'],
// Will order by otherfunction(`col1`, 12, 'lalala') DESC
[sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
// Will order an associated model's created_at using the model name as the association's name.
[Task, 'createdAt', 'DESC'],
// Will order through an associated model's created_at using the model names as the associations' names.
[Task, Project, 'createdAt', 'DESC'],
// Will order by an associated model's created_at using the name of the association.
['Task', 'createdAt', 'DESC'],
// Will order by a nested associated model's created_at using the names of the associations.
['Task', 'Project', 'createdAt', 'DESC'],
// Will order by an associated model's created_at using an association object. (preferred method)
[Subtask.associations.Task, 'createdAt', 'DESC'],
// Will order by a nested associated model's created_at using association objects. (preferred method)
[Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
// Will order by an associated model's created_at using a simple association object.
[{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
// Will order by a nested associated model's created_at simple association objects.
[{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
]
// Will order by max age descending
order: sequelize.literal('max(age) DESC')
// Will order by max age ascending assuming ascending is the default order when direction is omitted
order: sequelize.fn('max', sequelize.col('age'))
// Will order by age ascending assuming ascending is the default order when direction is omitted
order: sequelize.col('age')
})