sql - 使用 postgresql 连接一对多表时如何使用 LIMIT 和 OFFSET？

Question

我正在将 sqlalchemy 核心与 postgres 数据库一起使用，并且正在实现 JSON:API spec .

对于分页，我只使用基本的 LIMIT和 OFFSET .因此，例如以下两个表:

session
id
name

appearance
id
date
session_id

对于 session ，我可以像这样简单地对其进行分页:

SELECT id, name FROM session LIMIT 20 OFFSET 40

由于数据可以保存多长时间的限制，我们的数据库只能增长到这么大，所以我不担心 LIMIT 的速度变慢OFFSET方法可以创建。在上面的示例中，它工作正常，记录数始终等于限制，如果在末尾则更少。但是当我有以下问题时，我的问题就来了:

SELECT s.id, s.name, a.date FROM session s JOIN appearance a on s.id = a.session_id LIMIT 3 OFFSET 0

因为可能有两个appearance引用 session 的行然后我可能会得到类似的东西:

s.id     s.name                 a.date
1        FirstSesh              24/04/14
1        FirstSesh              01/01/20
2        Hello                  09/09/10

现在我只返回了一行，可能还有另一行或只是另一行的一部分。

我想到的第一个解决方案是:

SELECT s.id, s.name, a.date FROM (SELECT id, name FROM session LIMIT 3 OFFSET 0) s JOIN appearance a on s.id = a.session_id

但这现在受到使用能力的限制 WHERE或 ORDER在 s因为它将被限制为三个。而且我不能只将所有这些条件放在子查询中，因为我建立 JSON:API 关系部分的方式，并且因为我想限制我是否返回 session。也基于 if 例如appearances.date 在 2012 年之前。这会导致同样的问题。

作为引用，我的 JSON:API 设置中的每种类型都有自己的查询，然后当在关系中使用时，这些查询被用作子查询，这允许简单的递归关系和新关系的简单实现。

如果我可以改为使用 LIMIT和 OFFSET基于 session ID 的组，那么我认为这可能有效？但我不确定该怎么做？

Answer 1

您可以使用窗口函数。例如，这会为您提供前 3 个 session (按 id 排序)，以及所有相应的外观(无论有多少匹配)。

SELECT s.id, s.name, a.date 
FROM (SELECT s.*, ROW_NUMBER() OVER(ORDER BY id) rn FROM sessions) s 
INNER JOIN appearance a ON s.id = a.session_id 
WHERE s.rn BETWEEN 0 AND 3
ORDER BY s.rn, a.date

然后您可以通过更改 BETWEEN 条件的范围对结果集进行“分页”。

编辑

或者:

SELECT id, name, date
FROM (
    SELECT s.id, s.name, a.date,
        DENSE_RANK () OVER(ORDER BY id) rn
    FROM sessions s 
    INNER JOIN appearance a ON s.id = a.session_id 
    WHERE a.is_admin = 1
) c
WHERE s.rn BETWEEN 0 AND 3
ORDER BY rn, date