23 排序、分页与计数：数的是客户，还是 JOIN 行？

对应项目：findCustomers()、listActorFilms()。

1. LIMIT 不代表“最近”

错误思路：租赁查询加 limit: 10 就得到最近 10 条。

正确写法：

const rentals = await Rental.findAll({
  where: { customer_id },
  order: [["rental_date", "DESC"], ["rental_id", "DESC"]],
  limit: 10,
});

SELECT ... FROM rental
WHERE customer_id = :customer_id
ORDER BY rental_date DESC, rental_id DESC
LIMIT 10;

主键打破相同时间的并列，得到稳定顺序。只按姓氏、时间排序时，同值记录的前后位置没有保证。

ORDER BY customer_id 并不保证业务上的“最近创建”；它只定义按 ID 的顺序。业务含义仍要选对时间字段。

2. 页码、大小和偏移量各自校验

const page = options.page ?? 1;
const page_size = options.page_size ?? 20;

if (!Number.isSafeInteger(page) || page < 1) {
  throw new Error("page 必须是正整数"); // 接入项目时映射为 AppError
}
if (!Number.isInteger(page_size) || page_size < 1 || page_size > 100) {
  throw new Error("page_size 必须在 1～100 之间");
}
const offset = (page - 1) * page_size;
if (!Number.isSafeInteger(offset)) {
  throw new Error("offset 超出安全整数范围");
}

?? 只补 null/undefined；但接口是否允许 null 由验证层决定。|| 会把 0 偷偷替换成默认值，掩盖非法输入。

HTTP 层校验不取代 Service 的关键边界检查：以后 CLI、定时任务、测试可能直接调用 Service。防止超大每页大小也能限制响应体与数据库工作量。

3. order 的字段名必须白名单

type CustomerSort = "customer_id" | "last_name" | "create_date";

const sort: CustomerSort = options.sort ?? "customer_id";
const direction = options.order === "desc" ? "DESC" : "ASC";
const order: Order = [[sort, direction]];
if (sort !== "customer_id") order.push(["customer_id", direction]);

示例假设 options 已通过对应校验，Order 从 Sequelize 导入。Service 仍可再检查运行时白名单，当前项目就是这样做的。

不能用值绑定替代 SQL 标识符白名单：ORDER BY :sort 常得到按一个字符串常量排序，根本不是按该列排序。更不能 literal(req.query.sort)。

4. findAndCountAll 是两次查询

const { count, rows } = await Customer.findAndCountAll({
  where: { active: 1 },
  attributes: ["customer_id", "first_name"],
  order: [["customer_id", "ASC"]],
  limit: 20,
  offset: 20,
});

等价 SQL：

SELECT COUNT(*) AS count FROM customer WHERE active = 1;

SELECT customer_id, first_name FROM customer
WHERE active = 1
ORDER BY customer_id ASC
LIMIT 20 OFFSET 20;

本版本内部用 Promise.all 调用 count 与 findAll，不应假设固定执行先后。COUNT 会去掉分页和排序；结果 count 是筛选总数。

两次查询之间有并发变化时，count 与 rows 可能不属于同一快照。普通管理列表往往接受这个小偏差。报表若严格要求一致，需明确使用适当隔离级别的一致性快照，并让相关查询都参加同一事务；仅有两个 SQL 不一定值得加事务。

5. JOIN 为什么会把 count 放大？

假设只有两个客户：客户 1 有 3 条租赁，客户 2 没有租赁。

SELECT c.customer_id, r.rental_id
FROM customer AS c
LEFT JOIN rental AS r ON r.customer_id = c.customer_id;

结果有四行：

customer_id | rental_id
1           | 101
1           | 102
1           | 103
2           | NULL

COUNT(c.customer_id) 是 4；客户数量是 2。ORM 可能把结果装配为 2 个 Customer 实例，但 SQL 计数不会因此自动变成 2。

const result = await Customer.findAndCountAll({
  attributes: ["customer_id"],
  include: [{ model: Rental, as: "rentals", attributes: ["rental_id"] }],
  distinct: true,
  limit: 2,
});

本项目版本计数 SQL 的核心是：

SELECT COUNT(DISTINCT(`Customer`.`customer_id`)) AS count
FROM customer AS Customer
LEFT JOIN rental AS rentals ON Customer.customer_id = rentals.customer_id;

distinct 在这里让 count 按基表主键去重，不是把整个 rows 查询改成万能的 SELECT DISTINCT。

required include 会影响计数筛选；某些非 required JOIN 即使不筛掉客户也可能进入计数 SQL。复杂情况下直接看两条实际 SQL，别只凭参数名推测。

当前 Customer 列表只 JOIN belongsTo 的 address/city/country 链，不会形成上述一对多放大，因此不需要盲目加 distinct。未来加 rentals 后再重新评估。

6. distinct 为什么不能顺带修好分页？

如果直接对前面的 JOIN 行执行 LIMIT 2，可能只得到客户 1 的两条租赁，最后装配出来只有 1 个客户。

Sequelize 会根据关联和选项，可能生成父表分页子查询：

SELECT Customer.*, rentals.rental_id
FROM (
  SELECT customer_id FROM customer ORDER BY customer_id LIMIT 2
) AS Customer
LEFT JOIN rental AS rentals ON rentals.customer_id = Customer.customer_id
ORDER BY Customer.customer_id;

先限定两个客户，再展开其租赁。subQuery: false 可能取消这个保护。顶层子表条件、排序、required 和多层 include 又可能让 SQL 更复杂。

当查询难以验证时，优先考虑：

1. 先分页查客户 ID，再批量查这些客户及关联，恢复原来的顺序；
2. hasMany 使用 separate；
3. 将详情中的大数组独立成分页接口；
4. 用清晰、可测试的 Raw SQL。

两步查不是天然错误，但需要接受或处理两步之间的并发变化。

7. group 以后 count 不是一个数字

const result = await Customer.findAndCountAll({
  attributes: ["store_id"],
  group: ["store_id"],
});

计数结果形如：

[
  { store_id: 1, count: 326 },
  { store_id: 2, count: 273 },
]

数字仅示意。count.length 是分组数；每个 count 是该组内记录数。不能拿整个数组去做 Math.ceil(count / page_size)。

如果列表主体就是“门店分组报表”，要定义总组数的查询，而不是硬复用客户分页 DTO。

8. 深分页与游标分页

大 offset 常需要扫描并跳过很多记录。按主键升序翻页时可用游标：

const rows = await Customer.findAll({
  where: { customer_id: { [Op.gt]: last_customer_id } },
  order: [["customer_id", "ASC"]],
  limit: 20,
});

SELECT ... FROM customer
WHERE customer_id > :last_customer_id
ORDER BY customer_id ASC LIMIT 20;

多字段游标则需完整匹配排序：

WHERE last_name > :last_name
   OR (last_name = :last_name AND customer_id > :customer_id)
ORDER BY last_name ASC, customer_id ASC
LIMIT 20;

再与其他筛选用 AND 连接，并为整组 OR 加括号。游标不能随意跳到第 100 页；排序值更新时仍可能移动记录。选择取决于 API 需求，不是 offset 永远错误。

9. 练习

1. 创建只在纸上的“2 客户、3 租赁”数据，预测 COUNT 与 COUNT DISTINCT。
2. 比较默认 subQuery 与 subQuery=false 的日志和结果。
3. 给姓氏排序补主键，解释两字段游标的 OR 条件。
4. 说明 FilmActor 为基表的演员电影列表为什么更容易分页。

下一章：fn、col、literal 与表达式。