查询
属性
若要仅选择某些属性，可以使用 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 ...
使用聚合函数时，必须给它一个别名才能从模型访问它。在上面的例子中，您可以通过 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 ...
条件
无论是使用 findAll/find 进行查询，还是执行批量 updates/destroys，都可以传递 where 对象来筛选查询。
通常从 attribute:value 对中获取对象，其中 value 可以是相等匹配的原语，也可以是其他运算符的键控对象。
也可以通过嵌套 or 和 and 运算符集来生成复杂的 和/或 条件。
基本
const Op = Sequelize.Op;
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.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;
Post.destroy({
where: {
status: 'inactive'
}
});
// DELETE FROM post WHERE status = 'inactive';
Post.update({
updatedAt: null,
}, {
where: {
deletedAt: {
[Op.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;
运算符
Sequelize公开可用于创建更复杂比较的符号运算符：
const Op = Sequelize.Op
[Op.and]: [{a: 5}, {b: 6}] // (a = 5) AND (b = 6)
[Op.or]: [{a: 5}, {a: 6}] // (a = 5 OR a = 6)
[Op.gt]: 6, // > 6
[Op.gte]: 6, // >= 6
[Op.lt]: 10, // < 10
[Op.lte]: 10, // <= 10
[Op.ne]: 20, // != 20
[Op.eq]: 3, // = 3
[Op.is]: null // IS NULL
[Op.not]: true, // IS NOT TRUE
[Op.between]: [6, 10], // BETWEEN 6 AND 10
[Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
[Op.in]: [1, 2], // IN [1, 2]
[Op.notIn]: [1, 2], // NOT IN [1, 2]
[Op.like]: '%hat', // LIKE '%hat'
[Op.notLike]: '%hat' // NOT LIKE '%hat'
[Op.iLike]: '%hat' // ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat' // NOT ILIKE '%hat' (PG only)
[Op.startsWith]: 'hat' // LIKE 'hat%'
[Op.endsWith]: 'hat' // LIKE '%hat'
[Op.substring]: 'hat' // LIKE '%hat%'
[Op.regexp]: '^[h|a|t]' // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
[Op.notRegexp]: '^[h|a|t]' // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
[Op.iRegexp]: '^[h|a|t]' // ~* '^[h|a|t]' (PG only)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (PG only)
[Op.like]: { [Op.any]: ['cat', 'hat']}
// LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
[Op.overlap]: [1, 2] // && [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2] // @> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2] // <@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3] // ANY ARRAY[2, 3]::INTEGER (PG only)
[Op.col]: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example
[Op.gt]: { [Op.all]: literal('SELECT 1') }
// > ALL (SELECT 1)
范围运算符
可以使用所有支持的运算符查询范围类型。
请记住，提供的范围值也可以定义绑定的包含/排除。
// All the above equality and inequality operators plus the following:
[Op.contains]: 2 // @> '2'::integer (PG range contains element operator)
[Op.contains]: [1, 2] // @> [1, 2) (PG range contains range operator)
[Op.contained]: [1, 2] // <@ [1, 2) (PG range is contained by operator)
[Op.overlap]: [1, 2] // && [1, 2) (PG range overlap (have points in common) operator)
[Op.adjacent]: [1, 2] // -|- [1, 2) (PG range is adjacent to operator)
[Op.strictLeft]: [1, 2] // << [1, 2) (PG range strictly left of operator)
[Op.strictRight]: [1, 2] // >> [1, 2) (PG range strictly right of operator)
[Op.noExtendRight]: [1, 2] // &< [1, 2) (PG range does not extend to the right of operator)
[Op.noExtendLeft]: [1, 2] // &> [1, 2) (PG range does not extend to the left of operator)
组合
const Op = Sequelize.Op;
{
rank: {
[Op.or]: {
[Op.lt]: 1000,
[Op.eq]: null
}
}
}
// rank < 1000 OR rank IS NULL
{
createdAt: {
[Op.lt]: new Date(),
[Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
}
}
// createdAt < [timestamp] AND createdAt > [timestamp]
{
[Op.or]: [
{
title: {
[Op.like]: 'Boat%'
}
},
{
description: {
[Op.like]: '%boat%'
}
}
]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'
运算符别名
Sequelize 允许将特定字符串设置为运算符的别名。 在 v5 这将给您一个弃用警告。
const Op = Sequelize.Op;
const operatorsAliases = {
$gt: Op.gt
}
const connection = new Sequelize(db, user, pass, { operatorsAliases })
[Op.gt]: 6 // > 6
$gt: 6 // same as using Op.gt (> 6)
运算符安全性
默认情况下，Sequelize将使用符号运算符。使用不带任何别名的Sequelize可以提高安全性。如果没有任何字符串别名，则极不可能注入运算符，但您应该始终正确地验证和清理用户输入。
有些框架会自动将用户输入解析为js对象，如果您未能清除输入，则可能会插入一个带有字符串运算符的对象以继续。
为了提高安全性，强烈建议您在代码中使用 Sequelize.Op 中的符号运算符，如：Op.and / Op.or。或者不依赖于任何基于字符串的运算符，如：$and / $or。您可以通过设置 operatorsAliases 选项来限制应用程序所需的别名，记住要清理用户输入，特别是当您直接将它们传递给 Sequentize 方法时。
const Op = Sequelize.Op;
//use sequelize without any operators aliases
const connection = new Sequelize(db, user, pass, { operatorsAliases: false });
//use sequelize with only alias for $and => Op.and
const connection2 = new Sequelize(db, user, pass, { operatorsAliases: { $and: Op.and } });
如果您正在使用默认别名，Sequenceize 将发出警告；如果您希望继续使用所有默认别名（不包括传统别名），不限制这些别名，而不发出警告，您可以传递以下 operatorsAlises 选项：
const Op = Sequelize.Op;
const operatorsAliases = {
$eq: Op.eq,
$ne: Op.ne,
$gte: Op.gte,
$gt: Op.gt,
$lte: Op.lte,
$lt: Op.lt,
$not: Op.not,
$in: Op.in,
$notIn: Op.notIn,
$is: Op.is,
$like: Op.like,
$notLike: Op.notLike,
$iLike: Op.iLike,
$notILike: Op.notILike,
$regexp: Op.regexp,
$notRegexp: Op.notRegexp,
$iRegexp: Op.iRegexp,
$notIRegexp: Op.notIRegexp,
$between: Op.between,
$notBetween: Op.notBetween,
$overlap: Op.overlap,
$contains: Op.contains,
$contained: Op.contained,
$adjacent: Op.adjacent,
$strictLeft: Op.strictLeft,
$strictRight: Op.strictRight,
$noExtendRight: Op.noExtendRight,
$noExtendLeft: Op.noExtendLeft,
$and: Op.and,
$or: Op.or,
$any: Op.any,
$all: Op.all,
$values: Op.values,
$col: Op.col
};
const connection = new Sequelize(db, user, pass, { operatorsAliases });
JSON
JSON数据类型仅受 PostgreSQL、SQLite、MySQL 和 MariaDB 方言的支持。
PostgreSQL
PostgreSQL 中的 JSON 数据类型将值存储为纯文本，而不是二进制表示。如果您只想存储和检索 JSON 表示，那么使用 JSON 将占用更少的磁盘空间和更少的时间从其输入表示构建。但是，如果要对 JSON 值执行任何操作，您应该首选下面描述的 JSONB 数据类型。
MSSQL
MSSQL 没有 JSON 数据类型，但自 SQL Server 2016 以来，它确实通过某些函数提供了对存储为字符串的 JSON 的支持。使用这些函数，您将能够查询存储在字符串中的 JSON，但是任何返回的值都需要单独解析。
// ISJSON - to test if a string contains valid JSON
User.findAll({
where: sequelize.where(sequelize.fn('ISJSON', sequelize.col('userDetails')), 1)
})
// JSON_VALUE - extract a scalar value from a JSON string
User.findAll({
attributes: [[ sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), 'address line 1']]
})
// JSON_VALUE - query a scalar value from a JSON string
User.findAll({
where: sequelize.where(sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), '14, Foo Street')
})
// JSON_QUERY - extract an object or array
User.findAll({
attributes: [[ sequelize.fn('JSON_QUERY', sequelize.col('userDetails'), '$.address'), 'full address']]
})
JSONB
可以通过三种不同的方式查询 JSONB。
嵌套对象
{
meta: {
video: {
url: {
[Op.ne]: null
}
}
}
}
嵌套键
{
"meta.audio.length": {
[Op.gt]: 20
}
}
包含
{
"meta": {
[Op.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 接受一个项目数组来按或 sequenceize 方法对查询进行排序。通常，您需要使用属性、方向或仅方向的元组/数组来确保正确转义。
Subtask.findAll({
order: [
// Will escape title 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')
// Will order randomly based on the dialect (instead of fn('RAND') or fn('RANDOM'))
order: sequelize.random()
})
表提示
tableHint 可用于在使用 mssql 时选择性地传递表提示。提示必须是 Sequelize.TableHints 中的值，并且只能在绝对必要时使用。每个查询当前只支持单个表提示。
表提示通过指定某些选项覆盖 mssql 查询优化器的默认行为。它们只影响该子句中引用的表或视图。
const TableHints = Sequelize.TableHints;
Project.findAll({
// adding the table hint NOLOCK
tableHint: TableHints.NOLOCK
// this will generate the SQL 'WITH (NOLOCK)'
})
索引提示
使用mysql时，indexHints 可用于可选地传递索引提示。提示类型必须是来自 Sequelize.IndexHints 的值，并且值应该引用现有索引。
索引提示覆盖mysql查询优化器的默认行为。
Project.findAll({
indexHints: [
{ type: IndexHints.USE, values: ['index_project_on_name'] }
],
where: {
id: {
[Op.gt]: 623
},
name: {
[Op.like]: 'Foo %'
}
}
})
将生成如下所示的 mysql 查询：
SELECT * FROM Project USE INDEX (index_project_on_name) WHERE name LIKE 'FOO %' AND id > 623;
Sequelize.IndexHints 包括 USE, FORCE 和 IGNORE。
原始API提案见第9421期。
