具有多列的键集分页 [英] Keyset pagination with multiple columns

问题描述

我正在尝试对多列进行键集分页，但除第一页外，我无法在任何页面上使用它.此外，键集列中的一列可以为空 (col1) 并按 nulls last 排序.

I'm trying to do keyset pagination with multiple columns but I can't get it to work on any page except the first one. Also one of the columns in the keyset columns is nullable (col1) and sorted with nulls last.

select colId, col1, col2 from tablename
where (case when parameter_col1 is null then true else col1 < parameter_col1)
and (case when parameter_col2 is null then true else col2 < parameter_col2)
and (case when parameter_colId is null then true else colId < parameter_colId)
order by col1 desc nulls last, col2 desc, colId desc
limit 10;

我在每个 where 中使用这些 case 语句，以便在所有 3 个 null 传入结果的第一页parameter_ 值，然后返回所有内容.所以它在结果的第一页上运行良好.

I'm using these case statements in each where so that on the first page of results when null is passed in for all 3 parameter_ values, then everything is returned. So it works fine on the first page of results.

但是在第二页上，无论 parameter_col1 是否为空，它都不会返回正确的结果.使用多列(其中一些可以为空)进行分页的正确方法是什么?

But on the second page it doesn't return the right results whether or not parameter_col1 is null. What is the right way to do pagination with multiple columns (where some of them can be null)?

将其分解为 2 个单独的查询是否有帮助，一个用于第一页，另一个用于所有后续查询?如果可能的话，我更愿意将它合并到一个查询中以便于维护，但如果不是，我可以将两个查询放入 sql 或 plpgsql 类型的函数中，以便它们至少在同一个地方，并使用 if 来选择使用哪个.

Would it help to break it into 2 separate queries, 1 for the first page and another for all subsequent queries? I'd prefer to combine it into a single query for easier maintenance if possible, but if not I can put both queries into a function of type sql or plpgsql so they're at least in the same place, and use an if to choose which one to use.

推荐答案

选择一个小于所有可能的 col1 值的值，并将其与 coalesce 一起使用.例如，如果 col1 是 timestamp 类型，你可以使用 '-infinity'，查询看起来像

Pick a value that is less than all possible col1 values and use that with coalesce. For example, if col1 is of type timestamp, you could use '-infinity', and the query would look like

SELECT colId, col1, col2 from tablename
WHERE ROW(coalesce(col1, '-infinity'), col2, colId)
    < ROW(coalesce(parameter_col1, 'infinity'), parameter_col2, parameter_colId)
ORDER BY coalesce(col1, '-infinity') DESC, col2 DESC, colid DESC
LIMIT 10;

使用 ORDER BY 表达式上的索引可能会很快.

That could be fast with an index on the ORDER BY expression.

这篇关于具有多列的键集分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！