06｜WHERE、排序、分页与字段选择

本章围绕后台管理员列表、账号锁定查询和登录审计查询，集中练习日常最常用的查询能力。

1. 最简单的 `where`

const admin = await AdminUser.findOne({
  where: { username },
});

大致 SQL：

SELECT ...
FROM admin_user
WHERE username = ?
LIMIT 1;

对象形式的 where 会让 Sequelize 按方言安全处理和转义值。不要自己拼接：

// 错误：有 SQL 注入风险
`WHERE username = '${username}'`

2. Sequelize `Op` 操作符

import { Op } from "sequelize";

比较

where: {
  failed_login_count: { [Op.gte]: 5 },
}

对应：

WHERE failed_login_count >= 5

高频映射：

Sequelize	SQL
`[Op.eq]`	`=`
`[Op.ne]`	`<>` / `!=`
`[Op.gt]`	`>`
`[Op.gte]`	`>=`
`[Op.lt]`	`<`
`[Op.lte]`	`<=`

简单等值通常直接写 { account_status: 1 }，无需强行使用 Op.eq。

集合

where: {
  account_status: {
    [Op.in]: [AdminAccountStatus.Active, AdminAccountStatus.Locked],
  },
}

对应：

WHERE account_status IN (1, 2)

Op.notIn 对应 NOT IN。调用前应明确空数组语义：用户没选状态时，是不加条件、返回空结果，还是校验失败？不要把决定交给 ORM 的偶然生成结果。

3. AND 与 OR

普通对象多个字段通常是 AND：

where: {
  account_status: AdminAccountStatus.Active,
  failed_login_count: { [Op.gt]: 0 },
}

对应：

WHERE account_status = 1
  AND failed_login_count > 0

OR：

where: {
  [Op.or]: [
    { username: keyword },
    { email: keyword },
  ],
}

复杂组合要把括号画出来再写：

where: {
  account_status: AdminAccountStatus.Active,
  [Op.or]: [
    { username: { [Op.like]: pattern } },
    { display_name: { [Op.like]: pattern } },
  ],
}

对应：

WHERE account_status = 1
  AND (username LIKE ? OR display_name LIKE ?)

4. NULL 查询

查询未锁定：

where: { locked_until: null }

对应：

WHERE locked_until IS NULL

查询非 NULL：

where: {
  locked_until: { [Op.ne]: null },
}

SQL 的 NULL 不是一个普通值，不能用 = NULL 判断。Sequelize 会按表达式生成 IS NULL / IS NOT NULL，但业务代码仍要理解三值逻辑。

5. 时间条件：查询仍处于临时锁定的账号

const now = new Date();

const locked_admins = await AdminUser.findAll({
  where: {
    locked_until: { [Op.gt]: now },
  },
});

对应：

SELECT ...
FROM admin_user
WHERE locked_until > ?;

这只表示“临时锁定截止时间还在未来”。人工长期锁定由 account_status = Locked 表示，两套状态应按业务需求使用 OR 组合，不能混为一谈。

6. LIKE 与用户搜索

where: {
  username: { [Op.like]: `%${keyword}%` },
}

通过 Sequelize 查询 API 安全传值可以防止输入逃逸成 SQL 结构，但 % 和 _ 仍是 LIKE 通配符：

% 匹配任意长度；
_ 匹配单个字符。

如果产品语义要求按普通字符搜索，就要明确转义规则，并指定 SQL 的 ESCAPE 行为。不要把“防 SQL 注入”和“转义 LIKE 通配符”混成同一个问题。

前置 %keyword% 往往难以利用普通 B-tree 索引，大数据量时要评估搜索方案。

7. `attributes`：只查真正需要的列

管理员列表无需密码和锁定内部细节时：

const admins = await AdminUser.findAll({
  attributes: [
    "id",
    "username",
    "display_name",
    "account_status",
    "last_login_at",
  ],
});

对应：

SELECT id, username, display_name, account_status, last_login_at
FROM admin_user;

这能减少网络传输和敏感字段误用。注意：如果后面 DTO 访问未查询字段，TypeScript 未必能自动知道该实例是“部分字段实例”；应让查询函数返回明确 DTO，或为投影结果定义清晰类型。

8. 排序 `order`

order: [
  ["created_at", "DESC"],
  ["id", "DESC"],
]

对应：

ORDER BY created_at DESC, id DESC

为什么再加 id？多个记录可能拥有相同 created_at，只按时间排序时，记录之间的顺序不稳定，会造成分页重复或遗漏。唯一主键作为第二排序键可提供确定性顺序。

不要直接接受用户字段名

危险思路：

order: [[req.query.sort_by, req.query.direction]]

即使 ORM 有转义，也不应该让用户决定任意列和 SQL 结构。使用白名单映射：

const sort_fields = {
  created_at: "created_at",
  username: "username",
} as const;

type SortField = keyof typeof sort_fields;

先用运行时校验确认输入是允许值，再传给 Sequelize。TypeScript 类型不能校验网络请求。

9. `limit` / `offset` 分页

const page = 2;
const page_size = 20;
const offset = (page - 1) * page_size;

const result = await AdminUser.findAndCountAll({
  attributes: ["id", "username", "display_name", "created_at"],
  order: [
    ["created_at", "DESC"],
    ["id", "DESC"],
  ],
  limit: page_size,
  offset,
});

必须校验：

page 是正整数；
page_size 有合理上限，例如 100；
避免 Number("abc") 后得到 NaN；
计算结果仍是安全整数。

大致 SQL：

SELECT ...
FROM admin_user
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 20;

深分页问题

OFFSET 100000 通常意味着数据库要先扫描/跳过大量记录。高数据量、连续翻页场景可采用游标分页：

WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Sequelize 可用 OR 组合表达，但先把 SQL 语义学清楚。后台小数据列表使用 offset 仍然简单实用。

10. 条件式构造查询

用户可能只提供部分筛选条件。避免把 undefined 随意塞进 where：

import type { Attributes, WhereOptions } from "sequelize";

const where: WhereOptions<Attributes<AdminUser>> = {};

if (account_status !== undefined) {
  where.account_status = account_status;
}

if (keyword !== undefined && keyword.trim() !== "") {
  const pattern = `%${keyword.trim()}%`;
  where[Op.or] = [
    { username: { [Op.like]: pattern } },
    { display_name: { [Op.like]: pattern } },
  ];
}

当前开启 exactOptionalPropertyTypes，这也鼓励“没有条件就省略属性”，而不是用 undefined 表示 SQL NULL。

复杂动态条件的类型有时比较严格。不要第一时间改成 any；先缩小输入类型、拆分已校验条件，必要时为查询参数定义专用接口。

11. `raw: true` 的取舍

const rows = await AdminUser.findAll({
  attributes: ["id", "username"],
  raw: true,
});

返回普通对象，适合只读报表和明确投影；代价是：

没有 Model 实例方法；
getter、关联嵌套形状可能变化；
类型推断未必精确描述选出的字段；
不能依赖实例 Scope/序列化习惯来理解结果。

当前业务 Service 使用 Model 实例并通过 mapper 生成 DTO，更适合学习清楚边界。

需要特别注意：Sequelize v6 的部分 findAll() 类型重载不会根据 raw: true 和 attributes 精确推导普通对象形状，编辑器仍可能显示 AdminUser[]。这不代表运行时对象拥有 save() 等实例方法。报表查询应为投影结果定义专用 Row 类型，并在数据库边界进行映射或校验。

12. 查询与索引的联系

假设高频查询：

SELECT id, username, created_at
FROM admin_user
WHERE account_status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20;

可能考虑 (account_status, created_at, id) 复合索引，但不要仅凭代码立即创建。应：

获取真实数据量和状态分布；
使用 EXPLAIN；
检查已有索引；
权衡读性能与写入/存储成本；
通过 Migration 添加索引。

低选择性的 account_status 单列索引未必有价值；与排序列组合可能更符合真实查询。

13. 初学者常见错误

把用户输入直接当排序字段或方向。
使用 %keyword% 后认为一定会走普通索引。
只按非唯一时间字段分页，出现重复/漏项。
不限制 page_size，允许一次拉取海量数据。
把 undefined 当作 SQL NULL。
忽略 IN [] 的业务语义。
复杂 AND/OR 没先写 SQL 括号，查询逻辑错误。
默认查询所有字段，增加开销和泄露风险。
深分页仍无限增加 OFFSET。
用 any 掩盖动态 where 的输入校验问题。

14. 动手练习

查询所有“人工 Locked 或临时锁定尚未结束”的管理员，先写 SQL，再写 Sequelize。
实现管理员列表参数：account_status、keyword、page、page_size，为每项写运行时校验。
限制排序仅允许 created_at 和 username，方向仅允许 ASC / DESC。
比较只按 created_at 与再加 id 的分页稳定性。
对一个列表查询执行 EXPLAIN，记录使用的索引和估算行数。
把列表查询改成只返回 DTO 所需字段，观察 SELECT 变化。

15. 复盘问题

普通对象多个字段与 Op.or 分别怎样组合？
安全传值为什么不能自动消除 LIKE 通配符语义？
为什么分页排序最好包含唯一字段？
findAndCountAll() 通常会执行几条查询？
offset 分页在什么情况下足够，什么时候应考虑游标分页？
TypeScript 为什么不能代替对 req.query 的运行时校验？

至此你已能读懂 Model 并完成多数单表 CRUD。接下来应学习版本化建表和结构变更，避免只在 DBeaver 中留下无法追踪的手工操作。

完成 Customer 练习后，可继续阅读 19 查询 options 总览、20 WHERE 与 OR、23 分页与计数、24 SQL 表达式。