Querying - 查询

属性

想要只选择某些属性，可以使用 attributes 选项。 通常是传递一个数组：

  1. Model.findAll({
  2.   attributes: ['foo', 'bar']
  3. });
  1. SELECT foo, bar ...

属性可以使用嵌套数组来重命名：

  1. Model.findAll({
  2.   attributes: ['foo', ['bar', 'baz']]
  3. });
  1. SELECT foo, bar AS baz ...

也可以使用 sequelize.fn 来进行聚合：

  1. Model.findAll({
  2.   attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
  3. });
  1. SELECT COUNT(hats) AS no_hats ...

使用聚合功能时，必须给它一个别名，以便能够从模型中访问它。 在上面的例子中，您可以使用 instance.get('no_hats') 获得帽子数量。

有时，如果您只想添加聚合，则列出模型的所有属性可能令人厌烦：

  1. // This is a tiresome way of getting the number of hats...
  2. Model.findAll({
  3.   attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
  4. });
  6. // This is shorter, and less error prone because it still works if you add / remove attributes
  7. Model.findAll({
  8.   attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
  9. });
  1. SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...

同样，它也可以删除一些指定的属性：

  1. Model.findAll({
  2.   attributes: { exclude: ['baz'] }
  3. });
  1. SELECT id, foo, bar, quz ...

Where

无论您是通过 findAll/find 或批量 updates/destroys 进行查询，都可以传递一个 where 对象来过滤查询。

where 通常用 attribute:value 键值对获取一个对象，其中 value 可以是匹配等式的数据或其他运算符的键值对象。

也可以通过嵌套 or 和 and 运算符 的集合来生成复杂的 AND/OR 条件。

基础

  1. const Op = Sequelize.Op;
  3. Post.findAll({
  4.   where: {
  5.     authorId: 2
  6.   }
  7. });
  8. // SELECT * FROM post WHERE authorId = 2
  10. Post.findAll({
  11.   where: {
  12.     authorId: 12,
  13.     status: 'active'
  14.   }
  15. });
  16. // SELECT * FROM post WHERE authorId = 12 AND status = 'active';
  18. Post.findAll({
  19.   where: {
  20.     [Op.or]: [{authorId: 12}, {authorId: 13}]
  21.   }
  22. });
  23. // SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
  25. Post.findAll({
  26.   where: {
  27.     authorId: {
  28.       [Op.or]: [12, 13]
  29.     }
  30.   }
  31. });
  32. // SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
  34. Post.destroy({
  35.   where: {
  36.     status: 'inactive'
  37.   }
  38. });
  39. // DELETE FROM post WHERE status = 'inactive';
  41. Post.update({
  42.   updatedAt: null,
  43. }, {
  44.   where: {
  45.     deletedAt: {
  46.       [Op.ne]: null
  47.     }
  48.   }
  49. });
  50. // UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;
  52. Post.findAll({
  53.   where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
  54. });
  55. // SELECT * FROM post WHERE char_length(status) = 6;

操作符

Sequelize 可用于创建更复杂比较的符号运算符 -

  1. const Op = Sequelize.Op
  3. [Op.and]: {a: 5}           // 且 (a = 5)
  4. [Op.or]: [{a: 5}, {a: 6}]  // (a = 5 或 a = 6)
  5. [Op.gt]: 6,                // id > 6
  6. [Op.gte]: 6,               // id >= 6
  7. [Op.lt]: 10,               // id < 10
  8. [Op.lte]: 10,              // id <= 10
  9. [Op.ne]: 20,               // id != 20
  10. [Op.eq]: 3,                // = 3
  11. [Op.not]: true,            // 不是 TRUE
  12. [Op.between]: [6, 10],     // 在 6 和 10 之间
  13. [Op.notBetween]: [11, 15], // 不在 11 和 15 之间
  14. [Op.in]: [1, 2],           // 在 [1, 2] 之中
  15. [Op.notIn]: [1, 2],        // 不在 [1, 2] 之中
  16. [Op.like]: '%hat',         // 包含 '%hat'
  17. [Op.notLike]: '%hat'       // 不包含 '%hat'
  18. [Op.iLike]: '%hat'         // 包含 '%hat' (不区分大小写)  (仅限 PG)
  19. [Op.notILike]: '%hat'      // 不包含 '%hat'  (仅限 PG)
  20. [Op.regexp]: '^[h|a|t]'    // 匹配正则表达式/~ '^[h|a|t]' (仅限 MySQL/PG)
  21. [Op.notRegexp]: '^[h|a|t]' // 不匹配正则表达式/!~ '^[h|a|t]' (仅限 MySQL/PG)
  22. [Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (仅限 PG)
  23. [Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (仅限 PG)
  24. [Op.like]: { [Op.any]: ['cat', 'hat']} // 包含任何数组['cat', 'hat'] - 同样适用于 iLike 和 notLike
  25. [Op.overlap]: [1, 2]       // && [1, 2] (PG数组重叠运算符)
  26. [Op.contains]: [1, 2]      // @> [1, 2] (PG数组包含运算符)
  27. [Op.contained]: [1, 2]     // <@ [1, 2] (PG数组包含于运算符)
  28. [Op.any]: [2,3]            // 任何数组[2, 3]::INTEGER (仅限PG)
  30. [Op.col]: 'user.organization_id' // = 'user'.'organization_id', 使用数据库语言特定的列标识符, 本例使用 PG

范围选项

所有操作符都支持支持的范围类型查询。

请记住，提供的范围值也可以定义绑定的 inclusion/exclusion。

  1. // 所有上述相等和不相等的操作符加上以下内容:
  3. [Op.contains]: 2           // @> '2'::integer (PG range contains element operator)
  4. [Op.contains]: [1, 2]      // @> [1, 2) (PG range contains range operator)
  5. [Op.contained]: [1, 2]     // <@ [1, 2) (PG range is contained by operator)
  6. [Op.overlap]: [1, 2]       // && [1, 2) (PG range overlap (have points in common) operator)
  7. [Op.adjacent]: [1, 2]      // -|- [1, 2) (PG range is adjacent to operator)
  8. [Op.strictLeft]: [1, 2]    // << [1, 2) (PG range strictly left of operator)
  9. [Op.strictRight]: [1, 2]   // >> [1, 2) (PG range strictly right of operator)
  10. [Op.noExtendRight]: [1, 2] // &< [1, 2) (PG range does not extend to the right of operator)
  11. [Op.noExtendLeft]: [1, 2]  // &> [1, 2) (PG range does not extend to the left of operator)

组合

  1. {
  2.   rank: {
  3.     [Op.or]: {
  4.       [Op.lt]: 1000,
  5.       [Op.eq]: null
  6.     }
  7.   }
  8. }
  9. // rank < 1000 OR rank IS NULL
  11. {
  12.   createdAt: {
  13.     [Op.lt]: new Date(),
  14.     [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
  15.   }
  16. }
  17. // createdAt < [timestamp] AND createdAt > [timestamp]
  19. {
  20.   [Op.or]: [
  21.     {
  22.       title: {
  23.         [Op.like]: 'Boat%'
  24.       }
  25.     },
  26.     {
  27.       description: {
  28.         [Op.like]: '%boat%'
  29.       }
  30.     }
  31.   ]
  32. }
  33. // title LIKE 'Boat%' OR description LIKE '%boat%'

运算符别名

Sequelize 允许将特定字符串设置为操作符的别名 -

  1. const Op = Sequelize.Op;
  2. const operatorsAliases = {
  3.   $gt: Op.gt
  4. }
  5. const connection = new Sequelize(db, user, pass, { operatorsAliases })
  7. [Op.gt]: 6 // > 6
  8. $gt: 6 // 等同于使用 Op.gt (> 6)

运算符安全性

使用没有任何别名的 Sequelize 可以提高安全性。

一些框架会自动将用户输入解析为js对象，如果您无法清理输入，则可能会将具有字符串运算符的对象注入到 Sequelize。

不带任何字符串别名将使运算符不太可能被注入，但您应该始终正确验证和清理用户输入。

由于向后兼容性原因Sequelize默认设置以下别名 -

$eq, $ne, $gte, $gt, $lte, $lt, $not, $in, $notIn, $is, $like, $notLike, $iLike, $notILike, $regexp, $notRegexp, $iRegexp, $notIRegexp, $between, $notBetween, $overlap, $contains, $contained, $adjacent, $strictLeft, $strictRight, $noExtendRight, $noExtendLeft, $and, $or, $any, $all, $values, $col

目前，以下遗留别名也被设置，但计划在不久的将来完全删除 -

ne, not, in, notIn, gte, gt, lte, lt, like, ilike, $ilike, nlike, $notlike, notilike, .., between, !.., notbetween, nbetween, overlap, &&, @>, <@

为了更好的安全性，建议使用 Sequelize.Op，而不是依赖任何字符串别名。 您可以通过设置operatorsAliases选项来限制应用程序需要的别名，请记住要清理用户输入，特别是当您直接将它们传递给 Sequelize 方法时。

  1. const Op = Sequelize.Op;
  3. // 不用任何操作符别名使用 sequelize 
  4. const connection = new Sequelize(db, user, pass, { operatorsAliases: false });
  6. // 只用 $and => Op.and 操作符别名使用 sequelize 
  7. const connection2 = new Sequelize(db, user, pass, { operatorsAliases: { $and: Op.and } });

如果你使用默认别名并且不限制它们，Sequelize会发出警告。如果您想继续使用所有默认别名（不包括旧版别名）而不发出警告，您可以传递以下运算符参数 -

  1. const Op = Sequelize.Op;
  2. const operatorsAliases = {
  3.   $eq: Op.eq,
  4.   $ne: Op.ne,
  5.   $gte: Op.gte,
  6.   $gt: Op.gt,
  7.   $lte: Op.lte,
  8.   $lt: Op.lt,
  9.   $not: Op.not,
  10.   $in: Op.in,
  11.   $notIn: Op.notIn,
  12.   $is: Op.is,
  13.   $like: Op.like,
  14.   $notLike: Op.notLike,
  15.   $iLike: Op.iLike,
  16.   $notILike: Op.notILike,
  17.   $regexp: Op.regexp,
  18.   $notRegexp: Op.notRegexp,
  19.   $iRegexp: Op.iRegexp,
  20.   $notIRegexp: Op.notIRegexp,
  21.   $between: Op.between,
  22.   $notBetween: Op.notBetween,
  23.   $overlap: Op.overlap,
  24.   $contains: Op.contains,
  25.   $contained: Op.contained,
  26.   $adjacent: Op.adjacent,
  27.   $strictLeft: Op.strictLeft,
  28.   $strictRight: Op.strictRight,
  29.   $noExtendRight: Op.noExtendRight,
  30.   $noExtendLeft: Op.noExtendLeft,
  31.   $and: Op.and,
  32.   $or: Op.or,
  33.   $any: Op.any,
  34.   $all: Op.all,
  35.   $values: Op.values,
  36.   $col: Op.col
  37. };
  39. const connection = new Sequelize(db, user, pass, { operatorsAliases });

JSON

JSON 数据类型仅由 PostgreSQL，SQLite 和 MySQL 语言支持。

PostgreSQL

PostgreSQL 中的 JSON 数据类型将值存储为纯文本，而不是二进制表示。 如果您只是想存储和检索 JSON 格式数据，那么使用 JSON 将占用更少的磁盘空间，并且从其输入数据中构建时间更少。 但是，如果您想对 JSON 值执行任何操作，则应该使用下面描述的 JSONB 数据类型。

MSSQL

MSSQL 没有 JSON 数据类型，但是它确实提供了对于自 SQL Server 2016 以来通过某些函数存储为字符串的 JSON 的支持。使用这些函数，您将能够查询存储在字符串中的 JSON，但是任何返回的值将需要分别进行解析。

  1. // ISJSON - 测试一个字符串是否包含有效的 JSON
  2. User.findAll({
  3.   where: sequelize.where(sequelize.fn('ISJSON', sequelize.col('userDetails')), 1)
  4. })
  6. // JSON_VALUE - 从 JSON 字符串提取标量值
  7. User.findAll({
  8.   attributes: [[ sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), 'address line 1']]
  9. })
  11. // JSON_VALUE - 从 JSON 字符串中查询标量值
  12. User.findAll({
  13.   where: sequelize.where(sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), '14, Foo Street')
  14. })
  16. // JSON_QUERY - 提取一个对象或数组
  17. User.findAll({
  18.   attributes: [[ sequelize.fn('JSON_QUERY', sequelize.col('userDetails'), '$.address'), 'full address']]
  19. })

JSONB

JSONB 可以以三种不同的方式进行查询。

嵌套对象

  1. {
  2.   meta: {
  3.     video: {
  4.       url: {
  5.         [Op.ne]: null
  6.       }
  7.     }
  8.   }
  9. }

嵌套键

  1. {
  2.   "meta.audio.length": {
  3.     [Op.gt]: 20
  4.   }
  5. }

外包裹

  1. {
  2.   "meta": {
  3.     [Op.contains]: {
  4.       site: {
  5.         url: 'http://google.com'
  6.       }
  7.     }
  8.   }
  9. }

关系 / 关联

  1. // 找到所有具有至少一个 task 的  project，其中 task.state === project.state
  2. Project.findAll({
  3.     include: [{
  4.         model: Task,
  5.         where: { state: Sequelize.col('project.state') }
  6.     }]
  7. })

分页 / 限制

  1. // 获取10个实例/行
  2. Project.findAll({ limit: 10 })
  4. // 跳过8个实例/行
  5. Project.findAll({ offset: 8 })
  7. // 跳过5个实例，然后取5个
  8. Project.findAll({ offset: 5, limit: 5 })

排序

order 需要一个条目的数组来排序查询或者一个 sequelize 方法。一般来说，你将要使用任一属性的 tuple/array，并确定排序的正反方向。

  1. Subtask.findAll({
  2.   order: [
  3.     // 将转义标题，并根据有效的方向参数列表验证DESC
  4.     ['title', 'DESC'],
  6.     // 将按最大值排序(age)
  7.     sequelize.fn('max', sequelize.col('age')),
  9.     // 将按最大顺序(age) DESC
  10.     [sequelize.fn('max', sequelize.col('age')), 'DESC'],
  12.     // 将按 otherfunction 排序(`col1`, 12, 'lalala') DESC
  13.     [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
  15.     // 将使用模型名称作为关联的名称排序关联模型的 created_at。
  16.     [Task, 'createdAt', 'DESC'],
  18.     // Will order through an associated model's created_at using the model names as the associations' names.
  19.     [Task, Project, 'createdAt', 'DESC'],
  21.     // 将使用关联的名称由关联模型的created_at排序。
  22.     ['Task', 'createdAt', 'DESC'],
  24.     // Will order by a nested associated model's created_at using the names of the associations.
  25.     ['Task', 'Project', 'createdAt', 'DESC'],
  27.     // Will order by an associated model's created_at using an association object. (优选方法)
  28.     [Subtask.associations.Task, 'createdAt', 'DESC'],
  30.     // Will order by a nested associated model's created_at using association objects. (优选方法)
  31.     [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
  33.     // Will order by an associated model's created_at using a simple association object.
  34.     [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
  36.     // 嵌套关联模型的 created_at 简单关联对象排序
  37.     [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
  38.   ]
  40.   // 将按年龄最大值降序排列
  41.   order: sequelize.literal('max(age) DESC')
  43.   // 按最年龄大值升序排列，当省略排序条件时默认是升序排列
  44.   order: sequelize.fn('max', sequelize.col('age'))
  46.   // 按升序排列是省略排序条件的默认顺序
  47.   order: sequelize.col('age')
  49.   // 将根据方言随机排序 (而不是 fn('RAND') 或 fn('RANDOM'))
  50.   order: sequelize.random()
  51. })

Table Hint

当使用 mssql 时，可以使用 tableHint 来选择传递一个表提示。 该提示必须是来自 Sequelize.TableHints 的值，只能在绝对必要时使用。 每个查询当前仅支持单个表提示。

表提示通过指定某些选项来覆盖 mssql 查询优化器的默认行为。 它们只影响该子句中引用的表或视图。

  1. const TableHints = Sequelize.TableHints;
  3. Project.findAll({
  4.   // 添加 table hint NOLOCK
  5.   tableHint: TableHints.NOLOCK
  6.   // 这将生成 SQL 'WITH (NOLOCK)'
  7. })