postgresql - 如何在字母数据上查找/键集分页

Question

• 我有 2 张桌子

资源

resource_votes_aggregate存储每个 resource_id 的点赞数

• 我想使用搜索/键集分页按标题的降序对资源进行分页

• 没有分页，这就是你得到的

• 我想要 5 个批次的结果，因为我一次分页 5 个结果

我的 PAGE 1 查询使用下面的查询没有问题

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes 
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
WHERE
   category_id = 1 
ORDER BY
   title DESC,
   resource_id DESC LIMIT 5;

• 如何使用键集/搜索分页获取下一页？

我的尝试

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes 
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
WHERE
   category_id = 1 
   AND 
   (
      likes,
      title
   )
   < (586, 'Zatoichi Meets Yojimbo (Zatôichi to Yôjinbô) (Zatôichi 20)') 
ORDER BY
   title DESC,
   resource_id DESC LIMIT 5;

结果不正确

更新 1

我创建了一个加载 20 个结果的FIDDLE HER E？如何使用搜索分页以 5 个为一组进行分页？

标签： postgresqlpaginationkeyset-pagination

Answer 1

有两个选项可以满足您的需要：

OFFSET n FETCH FIRST n ROWS ONLY和sort_column > last_val

示例 1：

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes 
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
WHERE
   category_id = 1 
   AND 
   (
      likes,
      title
   )
   < (586, 'Zatoichi Meets Yojimbo (Zatôichi to Yôjinbô) (Zatôichi 20)') 
ORDER BY
   title DESC,
   resource_id DESC
OFFSET 5
FETCH FIRST 5 ROWS ONLY;

然后，您只需每次更改 OFFSET 值。

然后另一种方法是跟踪应用层中的 title 和 resource_id，然后将其传递给查询，以便进一步从结果集开始：

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes 
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
WHERE
   category_id = 1 
   AND 
   (
      likes,
      title
   )
   < (586, 'Zatoichi Meets Yojimbo (Zatôichi to Yôjinbô) (Zatôichi 20)')
   AND
     (title,resource_id) > (last_title_id_from_before,last_resource_id_from_before)
ORDER BY
   title DESC,
   resource_id DESC LIMIT 5;

这将确保您跳过前一页中的行，但是这种策略的缺点是您无法再次返回页面。