Model Querying - Basics - 模型查询(基础)

    重要说明：要使用 Sequelize 执行生产级别的查询,请确保你还阅读了事务指南. 事务对于确保数据完整性和提供其它好处很重要.

    本指南将说明如何进行标准的 查询.

    首先,一个简单的例子：

    Model.create() 方法是使用 构建未保存实例并使用 instance.save() 保存实例的简写形式.

    也可以定义在 create 方法中的属性. 如果你基于用户填写的表单创建数据库条目,这将特别有用. 例如,使用它可以允许你将 User 模型限制为仅设置用户名和地址,而不设置管理员标志：

    1. const user = await User.create({
    2.   username: 'alice123',
    3.   isAdmin: true
    4. }, { fields: ['username'] });
    5. // 假设 isAdmin 的默认值为 false
    6. console.log(user.username); // 'alice123'
    7. console.log(user.isAdmin); // false

    简单 SELECT 查询

    你可以使用 findAll 方法从数据库中读取整个表：

    1. // 查询所有用户
    2. const users = await User.findAll();
    3. console.log(users.every(user => user instanceof User)); // true
    4. console.log("All users:", JSON.stringify(users, null, 2));
    1. SELECT * FROM ...

    SELECT 查询特定属性

    选择某些特定属性,可以使用 attributes 参数：

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

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

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

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

    1. Model.findAll({
    2.   attributes: [
    3.     'foo',
    4.     [sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats']
    5.     'bar'
    6.   ]
    7. });
    1. SELECT foo, COUNT(hats) AS n_hats, bar FROM ...

    使用聚合函数时,必须为它提供一个别名,以便能够从模型中访问它. 在上面的示例中,你可以通过 instance.n_hats 获取帽子数量.

    有时,如果只想添加聚合,那么列出模型的所有属性可能会很麻烦：

    1. // 这是获取帽子数量的烦人方法(每列都有)
    2. Model.findAll({
    3.   attributes: [
    4.     'id', 'foo', 'bar', 'baz', 'qux', 'hats', // 我们必须列出所有属性...
    5.     [sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats'] // 添加聚合...
    6.   ]
    7. });
    9. // 这个更短,并且更不易出错. 如果以后在模型中添加/删除属性,它仍然可以正常工作
    10. Model.findAll({
    11.   attributes: {
    12.     include: [
    13.       [sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats']
    14.     ]
    15.   }
    16. });
    1. SELECT id, foo, bar, baz, qux, hats, COUNT(hats) AS n_hats FROM ...

    同样,也可以排除某些属性：

    1. Model.findAll({
    2.   attributes: { exclude: ['baz'] }
    3. });

    where 参数用于过滤查询.where 子句有很多运算符,可以从 中以 Symbols 的形式使用.

    1. Post.findAll({
    2.   where: {
    3.     authorId: 2
    4.   }
    5. });
    6. // SELECT * FROM post WHERE authorId = 2

    可以看到没有显式传递任何运算符(来自Op),因为默认情况下 Sequelize 假定进行相等比较. 上面的代码等效于：

    1. const { Op } = require("sequelize");
    2. Post.findAll({
    3.   where: {
    4.     authorId: {
    5.       [Op.eq]: 2
    6.     }
    7.   }
    8. });
    9. // SELECT * FROM post WHERE authorId = 2

    可以传递多个校验:

    1. Post.findAll({
    2.   where: {
    3.     authorId: 12
    4.     status: 'active'
    5.   }
    6. });
    7. // SELECT * FROM post WHERE authorId = 12 AND status = 'active';
    1. const { Op } = require("sequelize");
    2. Post.findAll({
    3.   where: {
    4.     [Op.and]: [
    5.       { authorId: 12 },
    6.       { status: 'active' }
    7.     ]
    8.   }
    9. });
    10. // SELECT * FROM post WHERE authorId = 12 AND status = 'active';

    OR 可以通过类似的方式轻松执行：

    1. const { Op } = require("sequelize");
    2. Post.findAll({
    3.   where: {
    4.     [Op.or]: [
    5.       { authorId: 12 },
    6.       { authorId: 13 }
    7.     ]
    8.   }
    9. });
    10. // SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

    由于以上的 OR 涉及相同字段 ,因此 Sequelize 允许你使用稍有不同的结构,该结构更易读并且作用相同：

    1. const { Op } = require("sequelize");
    2. Post.destroy({
    3.   where: {
    4.     authorId: {
    5.       [Op.or]: [12, 13]
    6.     }
    7.   }
    8. });
    9. // DELETE FROM post WHERE authorId = 12 OR authorId = 13;

    操作符

    Sequelize 提供了多种运算符.

    1. const { Op } = require("sequelize");
    2. Post.findAll({
    3.   where: {
    4.     [Op.and]: [{ a: 5 }, { b: 6 }],            // (a = 5) AND (b = 6)
    5.     [Op.or]: [{ a: 5 }, { b: 6 }],             // (a = 5) OR (b = 6)
    6.     someAttribute: {
    7.       // 基本
    8.       [Op.eq]: 3,                              // = 3
    9.       [Op.ne]: 20,                             // != 20
    10.       [Op.is]: null,                           // IS NULL
    11.       [Op.not]: true,                          // IS NOT TRUE
    12.       [Op.or]: [5, 6],                         // (someAttribute = 5) OR (someAttribute = 6)
    14.       // 使用方言特定的列标识符 (以下示例中使用 PG):
    15.       [Op.col]: 'user.organization_id',        // = "user"."organization_id"
    17.       // 数字比较
    18.       [Op.gte]: 6,                             // >= 6
    19.       [Op.lt]: 10,                             // < 10
    20.       [Op.lte]: 10,                            // <= 10
    21.       [Op.between]: [6, 10],                   // BETWEEN 6 AND 10
    22.       [Op.notBetween]: [11, 15],               // NOT BETWEEN 11 AND 15
    24.       // 其它操作符
    26.       [Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)
    28.       [Op.in]: [1, 2],                         // IN [1, 2]
    29.       [Op.notIn]: [1, 2],                      // NOT IN [1, 2]
    32.       [Op.notLike]: '%hat',                    // NOT LIKE '%hat'
    33.       [Op.startsWith]: 'hat',                  // LIKE 'hat%'
    34.       [Op.endsWith]: 'hat',                    // LIKE '%hat'
    35.       [Op.substring]: 'hat',                   // LIKE '%hat%'
    36.       [Op.iLike]: '%hat',                      // ILIKE '%hat' (不区分大小写) (仅 PG)
    37.       [Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (仅 PG)
    38.       [Op.regexp]: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]' (仅 MySQL/PG)
    39.       [Op.notRegexp]: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' (仅 MySQL/PG)
    40.       [Op.iRegexp]: '^[h|a|t]',                // ~* '^[h|a|t]' (仅 PG)
    41.       [Op.notIRegexp]: '^[h|a|t]',             // !~* '^[h|a|t]' (仅 PG)
    43.       [Op.any]: [2, 3],                        // ANY ARRAY[2, 3]::INTEGER (仅 PG)
    45.       // 在 Postgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用:
    46.       [Op.like]: { [Op.any]: ['cat', 'hat'] }  // LIKE ANY ARRAY['cat', 'hat']
    48.       // 还有更多的仅限 postgres 的范围运算符,请参见下文
    49.     }
    50.   }
    51. });

    Op.in 的简写语法

    直接将数组参数传递给 where 将隐式使用 IN 运算符：

    1. Post.findAll({
    2.   where: {
    3.     id: [1,2,3] // 等同使用 `id: { [Op.in]: [1,2,3] }`
    4.   }
    5. });
    6. // SELECT ... FROM "posts" AS "post" WHERE "post"."id" IN (1, 2, 3);

    运算符的逻辑组合

    运算符 Op.and, Op.or 和 Op.not 可用于创建任意复杂的嵌套逻辑比较.

    使用 Op.and 和 Op.or 示例

    1. const { Op } = require("sequelize");
    3. Foo.findAll({
    4.   where: {
    5.     rank: {
    6.       [Op.or]: {
    7.         [Op.lt]: 1000,
    8.         [Op.eq]: null
    9.       }
    10.     },
    11.     // rank < 1000 OR rank IS NULL
    13.     {
    14.       createdAt: {
    15.         [Op.lt]: new Date(),
    16.         [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
    17.       }
    18.     },
    19.     // createdAt < [timestamp] AND createdAt > [timestamp]
    21.     {
    22.       [Op.or]: [
    23.         {
    24.           title: {
    25.             [Op.like]: 'Boat%'
    26.           }
    27.         },
    28.         {
    29.           description: {
    30.             [Op.like]: '%boat%'
    31.           }
    32.         }
    33.       ]
    34.     }
    35.     // title LIKE 'Boat%' OR description LIKE '%boat%'
    36.   }
    37. });

    使用 Op.not 示例

    1. Project.findAll({
    2.   where: {
    3.     name: 'Some Project',
    4.     [Op.not]: [
    5.       { id: [1,2,3] },
    6.       {
    7.         description: {
    8.           [Op.like]: 'Hello%'
    9.         }
    10.       }
    11.     ]
    12.   }
    13. });

    上面将生成：

    1. SELECT *
    2. FROM `Projects`
    3. WHERE (
    4.   `Projects`.`name` = 'a project'
    5.   AND NOT (
    6.     `Projects`.`id` IN (1,2,3)
    7.     OR
    8.     `Projects`.`description` LIKE 'Hello%'
    9.   )
    10. )

    如果你想得到类似 WHERE char_length("content") = 7 的结果怎么办？

    1. Post.findAll({
    2.   where: sequelize.where(sequelize.fn('char_length', sequelize.col('content')), 7)
    3. });
    4. // SELECT ... FROM "posts" AS "post" WHERE char_length("content") = 7

    请注意方法 和 sequelize.col 的用法,应分别用于指定 SQL 函数调用和列. 应该使用这些方法,而不是传递纯字符串(例如 char_length(content)),因为 Sequelize 需要以不同的方式对待这种情况(例如,使用其他符号转义方法).

    如果你需要更复杂的东西怎么办？

    上面生成了以下SQL：

    1. SELECT
    2.   ...
    3. FROM "posts" AS "post"
    4. WHERE (
    5.   char_length("content") = 7
    6.   OR
    7.   "post"."content" LIKE 'Hello%'
    8.   OR (
    9.     "post"."status" = 'draft'
    10.     AND
    11.     char_length("content") > 10
    12.   )
    13. )

    仅限 Postgres 的范围运算符

    可以使用所有支持的运算符查询范围类型.

    请记住,提供的范围值也可以定义绑定的 包含/排除.

    1. [Op.contains]: 2,            // @> '2'::integer  (PG range 包含元素运算符)
    2. [Op.contains]: [1, 2],       // @> [1, 2)        (PG range 包含范围运算符)
    3. [Op.contained]: [1, 2],      // <@ [1, 2)        (PG range 包含于运算符)
    4. [Op.overlap]: [1, 2],        // && [1, 2)        (PG range 重叠(有共同点)运算符)
    5. [Op.adjacent]: [1, 2],       // -|- [1, 2)       (PG range 相邻运算符)
    6. [Op.strictLeft]: [1, 2],     // << [1, 2)        (PG range 左严格运算符)
    7. [Op.strictRight]: [1, 2],    // >> [1, 2)        (PG range 右严格运算符)
    8. [Op.noExtendRight]: [1, 2],  // &< [1, 2)        (PG range 未延伸到右侧运算符)
    9. [Op.noExtendLeft]: [1, 2],   // &> [1, 2)        (PG range 未延伸到左侧运算符)

    不推荐使用: 操作符别名

    在 Sequelize v4 中,可以指定字符串来引用运算符,而不是使用 Symbols. 现在不建议使用此方法,很可能在下一个主要版本中将其删除. 如果确实需要,可以在 Sequelize 构造函数中传递 operatorAliases 参数.

    例如:

    1. const { Sequelize, Op } = require("sequelize");
    2. const sequelize = new Sequelize('sqlite::memory:', {
    3.   operatorsAliases: {
    4.     $gt: Op.gt
    5.   }
    6. });
    8. // 现在我们可以在 where 子句中使用 `$gt` 代替 `[Op.gt]`：
    9. Foo.findAll({
    10.   where: {
    11.     $gt: 6 // 就像使用 [Op.gt]
    12.   }
    13. });

    简单 UPDATE 查询

    Update 查询也接受 where 参数,就像上面的读取查询一样.

    1. await User.update({ lastName: "Doe" }, {
    2.   where: {
    3.     lastName: null
    4.   }
    5. });

    简单 DELETE 查询

    1. // 删除所有名为 "Jane" 的人 
    2. await User.destroy({
    3.   where: {
    4.     firstName: "Jane"
    5.   }
    6. });

    要销毁所有内容,可以使用 TRUNCATE SQL：

    2. await User.destroy({
    3.   truncate: true
    4. });

    Sequelize 提供了 Model.bulkCreate 方法,以允许仅一次查询即可一次创建多个记录.

    通过接收数组对象而不是单个对象,Model.bulkCreate 的用法与 Model.create 非常相似.

    1. const captains = await Captain.bulkCreate([
    2.   { name: 'Jack Sparrow' },
    3.   { name: 'Davy Jones' }
    4. ]);
    5. console.log(captains.length); // 2
    6. console.log(captains[0] instanceof Captain); // true
    7. console.log(captains[0].name); // 'Jack Sparrow'
    8. console.log(captains[0].id); // 1 // (或另一个自动生成的值)

    但是,默认情况下,bulkCreate 不会在要创建的每个对象上运行验证(而 create 可以做到). 为了使 bulkCreate 也运行这些验证,必须通过validate: true 参数. 但这会降低性能. 用法示例：

    1. const Foo = sequelize.define('foo', {
    2.   bar: {
    3.     type: DataTypes.TEXT,
    4.     validate: {
    5.       len: [4, 6]
    6.     }
    7.   }
    8. });
    10. // 这不会引发错误,两个实例都将被创建
    11. await Foo.bulkCreate([
    12.   { name: 'abc123' },
    13.   { name: 'name too long' }
    14. ]);
    16. // 这将引发错误,不会创建任何内容
    17. await Foo.bulkCreate([
    18.   { name: 'abc123' },
    19.   { name: 'name too long' }
    20. ], { validate: true });

    如果你直接从用户获取值,那么限制实际插入的列可能会有所帮助. 为了做到这一点,bulkCreate() 接受一个 fields 参数,该参数须为你要定义字段的数组(其余字段将被忽略).

    1. await User.bulkCreate([
    2.   { username: 'foo' },
    3.   { username: 'bar', admin: true }
    4. ], { fields: ['username'] });
    5. // foo 和 bar 都不会是管理员.

    排序和分组

    Sequelize 提供了 order and group 参数,来与 ORDER BY 和 GROUP BY 一起使用.

    order 参数采用一系列 项 来让 sequelize 方法对查询进行排序. 这些 项 本身是 [column, direction] 形式的数组. 该列将被正确转义,并且将在有效方向列表中进行验证(例如 ASC, DESC, NULLS FIRST 等).

    1. Subtask.findAll({
    2.   order: [
    3.     // 将转义 title 并针对有效方向列表进行降序排列
    4.     ['title', 'DESC'],
    6.     // 将按最大年龄进行升序排序
    7.     sequelize.fn('max', sequelize.col('age')),
    9.     // 将按最大年龄进行降序排序
    10.     [sequelize.fn('max', sequelize.col('age')), 'DESC'],
    12.     // 将按 otherfunction(`col1`, 12, 'lalala') 进行降序排序
    13.     [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
    15.     // 将使用模型名称作为关联名称按关联模型的 createdAt 排序.
    16.     [Task, 'createdAt', 'DESC'],
    18.     // 将使用模型名称作为关联名称通过关联模型的 createdAt 排序.
    19.     [Task, Project, 'createdAt', 'DESC'],
    21.     // 将使用关联名称按关联模型的 createdAt 排序.
    22.     ['Task', 'createdAt', 'DESC'],
    24.     // 将使用关联的名称按嵌套的关联模型的 createdAt 排序.
    25.     ['Task', 'Project', 'createdAt', 'DESC'],
    27.     // 将使用关联对象按关联模型的 createdAt 排序. (首选方法)
    28.     [Subtask.associations.Task, 'createdAt', 'DESC'],
    30.     // 将使用关联对象按嵌套关联模型的 createdAt 排序. (首选方法)
    31.     [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
    33.     // 将使用简单的关联对象按关联模型的 createdAt 排序.
    34.     [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
    36.     // 将由嵌套关联模型的 createdAt 简单关联对象排序.
    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. });
    53. Foo.findOne({
    54.   order: [
    55.     // 将返回 `name`
    56.     ['name'],
    57.     // 将返回 `username` DESC
    58.     ['username', 'DESC'],
    59.     // 将返回 max(`age`)
    60.     sequelize.fn('max', sequelize.col('age')),
    61.     // 将返回 max(`age`) DESC
    62.     [sequelize.fn('max', sequelize.col('age')), 'DESC'],
    63.     // 将返回 otherfunction(`col1`, 12, 'lalala') DESC
    64.     [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
    65.     // 将返回 otherfunction(awesomefunction(`col`)) DESC, 这种嵌套可能是无限的!
    66.     [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']
    67.   ]
    68. });

    回顾一下,order 数组的元素可以如下：

    • 一个字符串 (它将被自动引用)
    • 一个数组, 其第一个元素将被引用,第二个将被逐字追加
    • 一个具有 raw 字段的对象:
      • raw 内容将不加引用地逐字添加
      • 其他所有内容都将被忽略,如果未设置 raw,查询将失败
    • 调用 Sequelize.fn (这将在 SQL 中生成一个函数调用)
    • 调用 Sequelize.col (这将引用列名)

    分组

    分组和排序的语法相同,只是分组不接受方向作为数组的最后一个参数(不存在 ASC, DESC, NULLS FIRST 等).

    你还可以将字符串直接传递给 group,该字符串将直接(普通)包含在生成的 SQL 中. 请谨慎使用,请勿与用户生成的内容一起使用.

    1. Project.findAll({ group: 'name' });
    2. // 生成 'GROUP BY name'

    限制和分页

    使用 limit 和 offset 参数可以进行 限制/分页：

    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 还提供了一些实用方法.

    count

    count 方法仅计算数据库中元素出现的次数.

    Sequelize 还提供了 max,min 和 sum 便捷方法.

    1. await User.max('age'); // 40
    2. await User.max('age', { where: { age: { [Op.lt]: 20 } } }); // 10
    3. await User.min('age'); // 5
    4. await User.min('age', { where: { age: { [Op.gt]: 5 } } }); // 10
    5. await User.sum('age'); // 55