postgre如何在SQL查询中使用sort-by实现GraphQL游标

Question

我有一个带有id和rating的数据库表reviews，其中id是自动递增的，评级是0到100之间的整数。

我试图在GraphQL API中创建基于游标的分页，但很难为hasPreviousPage和hasNextPage创建必要的查询。

这是我的数据：

ID: 1, RATING: 50
ID: 2, RATING: 80
ID: 3, RATING: 20
ID: 4, RATING: 40
ID: 5, RATING: 60

---

以下是GQL查询的示例：

reviews(first: 3)

哪个返回

ID: 1, RATING: 50
ID: 2, RATING: 80
ID: 3, RATING: 20

带pageInfo

hasPreviousPage: false
hasNextPage: true

对pageInfo的查询将是

hasPreviousPage = SELECT COUNT(*) > 0 FROM reviews WHERE id < 0;
hasNextPage     = SELECT COUNT(*) > 0 FROM reviews WHERE id > 3;

---

我的问题出现在按评级排序时。进行与以前类似的查询：

reviews(sort: "rating", first: 3)

哪个返回

ID: 3, RATING: 20
ID: 4, RATING: 40
ID: 1, RATING: 50

带pageInfo

hasPreviousPage: false
hasNextPage: true

但是，我怎样才能像以前那样为hasPreviousPage和hasNextPage创建查询呢？

hasPreviousPage = SELECT COUNT(*) > 0 FROM reviews WHERE ???
hasNextPage     = SELECT COUNT(*) > 0 FROM reviews WHERE ???

在这种情况下，WHERE子句应该是什么？使用子查询时，查询是否需要复杂得多？我不确定我错过了什么。

Answer 1

hasPreviousPage和hasNextPage实际上不需要任何DB查询。您需要应用+2技巧来实现这一点(假设您希望为before和after案例实现hasPreviousPage)。

假设您有以下查询：

// `after` should be URL-safe encoded
// `id` must have a monotonic sort order, a ULID is a fine choice for an id
// if ULID is not an option for some reason, chose a different column that has a monotonicity to it, e.g. `created_at`
reviews(first: 3, after: "id:12345;sort_cols:user_id")

你想做的是查询前5条评论：

SELECT *
FROM reviews
WHERE id >= ?
ORDER BY ? ASC
LIMIT 5;
-- result: 12345, 12346, 12347, 12348, 12349
-- from the app return (after computing `pageInfo`): 12346, 12347, 12348

如果第一个结果的id与光标中的id匹配，即12345，则意味着存在前一页。如果存在上一页，并且返回的行数为4或更少，则为hasNextPage: false。如果存在而不是前一页，并且返回的行数为4或更多，则为hasNextPage: true。

在返回结果之前，请确保过滤掉与光标id(12345)匹配的项目，如果有下一页，则过滤掉一个额外的项目。

请注意，SQL必须正确生成。查询将根据分页的方向而改变(before与after)。如果您想支持范围请求，也就是同时提供before和after的范围请求，那么它也会变得更加复杂。

示例：

reviews(first: 3, before: "id:12345;sort_col:user_id")

在这里，您要使用降序。此外，您需要对ids<=进行筛选光标中的id。

SELECT *
FROM reviews
WHERE id <= ?
ORDER BY ? DESC
LIMIT 5;
-- result: 12345, 12344, 12343, 12342, 12341
-- from the app return (after computing `pageInfo`): 12344, 12343, 12342