PostgreSQL：如何基于聚合结果与ID排序实现游标分页

问题背景 #

未分页的原始查询及结果：

SELECT 
    "Post"."id",
    COALESCE(SUM("PostVote"."value"), 0)::int as "voteScore"
FROM "Post"
LEFT JOIN "PostVote" ON "PostVote"."postId" = "Post"."id"
WHERE "Post"."questionId" = 28
GROUP BY "Post"."id"
ORDER BY "voteScore" DESC, id ASC

查询结果：

尝试以ID=30为游标、LIMIT=2分页时，仅按ID筛选的错误查询及结果：
错误查询：

SELECT 
    "Post"."id",
    COALESCE(SUM("PostVote"."value"), 0)::int as "voteScore"
FROM "Post"
LEFT JOIN "PostVote" ON "PostVote"."postId" = "Post"."id"
WHERE "Post"."questionId" = 28
    AND "Post"."id" > 30 -- 仅按ID筛选，未匹配排序规则
GROUP BY "Post"."id"
ORDER BY "voteScore" DESC, id ASC
LIMIT 2

错误结果：

期望结果：

正确的游标分页实现 #

游标分页的核心规则是：筛选条件必须完全匹配排序规则，不能仅依赖单一字段。原排序逻辑是voteScore DESC, id ASC，因此游标条件需要同时对比这两个字段，确保只返回游标记录之后的结果。

1. 基础版正确SQL #

已知游标（ID=30）对应的voteScore为0，直接编写匹配条件：

SELECT 
    "Post"."id",
    COALESCE(SUM("PostVote"."value"), 0)::int as "voteScore"
FROM "Post"
LEFT JOIN "PostVote" ON "PostVote"."postId" = "Post"."id"
WHERE "Post"."questionId" = 28
  AND (
    -- 分数低于游标分数（降序排列，更低分数在后面）
    COALESCE(SUM("PostVote"."value"), 0)::int < 0
    -- 分数等于游标时，ID大于游标ID（同分数下ID升序）
    OR (COALESCE(SUM("PostVote"."value"), 0)::int = 0 AND "Post"."id" > 30)
  )
GROUP BY "Post"."id"
ORDER BY "voteScore" DESC, id ASC
LIMIT 2

2. 适配GraphQL的动态参数版 #

在GraphQL实现中，需要将游标封装为包含voteScore和id的对象（而非单一ID），动态生成筛选条件：

-- 示例：使用参数替换适配GraphQL输入
SELECT 
    "Post"."id",
    COALESCE(SUM("PostVote"."value"), 0)::int as "voteScore"
FROM "Post"
LEFT JOIN "PostVote" ON "PostVote"."postId" = "Post"."id"
WHERE "Post"."questionId" = $questionId
  AND (
    COALESCE(SUM("PostVote"."value"), 0)::int < $cursorVoteScore
    OR (
      COALESCE(SUM("PostVote"."value"), 0)::int = $cursorVoteScore
      AND "Post"."id" > $cursorId
    )
  )
GROUP BY "Post"."id"
ORDER BY "voteScore" DESC, id ASC
LIMIT $limit

错误原因说明 #

原查询仅过滤id > 30，但ID=32的voteScore为2，远高于游标（ID=30，voteScore=0）的分数，会被排序规则提前到结果中，导致分页逻辑断裂。只有同时匹配排序的两个字段，才能保证分页结果的连续性和正确性。

内容的提问来源于stack exchange，提问作者R.M. Reza