如何查询自引用评论表以查找带有回复的评论，按最新回复排序？-解网

问：

在我正在使用的应用程序中，我的 Postgres 数据库中有一个注释表，可以简化为如下所示：

+----+-----------+-----------------+---------------------+
| id | parent_id | group_member_id |     created_at      |
+----+-----------+-----------------+---------------------+
| 1  | NULL      | 1               | 2023-08-01 12:00:00 |
| 2  | NULL      | 2               | 2023-08-01 12:00:01 |
| 3  | NULL      | 2               | 2023-08-01 12:00:02 |
| 4  | 3         | 1               | 2023-08-01 12:00:03 |
| 5  | 2         | 1               | 2023-08-01 12:00:04 |
| 6  | 1         | 1               | 2023-08-01 12:00:05 |
| 7  | 2         | 2               | 2023-08-01 12:00:06 |
| 8  | 2         | 1               | 2023-08-01 12:00:07 |
+----+-----------+-----------------+---------------------+

填充该列时，将引用注释表中的另一行。这将设置父/子关系。换言之，评论可以包含其他作为回复的评论。任何家长评论都可以有很多回复。就此应用程序而言，注释父子关系只有一级深度。parent_id

还有一个“组成员”表，该表由注释表通过外键引用。这代表每个评论的作者，可以简化为：

+----+---------------+
| id |     name      |
+----+---------------+
|  1 | Johnny Tables |
|  2 | Susan Select  |
+----+---------------+

对于任何给定的组成员，我想按照他们回复的顺序找到他们回复的最新不同根评论。例如，（id 1）将得到：Johnny Tables

+----+-----------+-----------------+---------------------+
| id | parent_id | group_member_id |     created_at      |
+----+-----------+-----------------+---------------------+
|  2 | NULL      |               2 | 2023-08-01 12:00:01 |
|  1 | NULL      |               1 | 2023-08-01 12:00:00 |
|  3 | NULL      |               2 | 2023-08-01 12:00:02 |
+----+-----------+-----------------+---------------------+

并且（id 2）将得到：Susan Select

+----+-----------+-----------------+---------------------+
| id | parent_id | group_member_id |     created_at      |
+----+-----------+-----------------+---------------------+
|  2 | NULL      |               2 | 2023-08-01 12:00:01 |
+----+-----------+-----------------+---------------------+

下面是一个使用示例数据设置表结构的小提琴。

我尝试了各种带有子查询和的咒语，但我错过了一些东西。例如，对于这个查询，我似乎得到了正确的回复，但它们实际上并没有按日期排序。它们按升序排序DISTINCTORDER BYcreated_atparent_id

SELECT DISTINCT ON (parent_id)
    parent_id,
    created_at
FROM
    comments
WHERE
    comments.group_member_id = 1
    AND comments.parent_id IS NOT NULL
ORDER BY
    comments.parent_id,
    comments.created_at DESC

一旦我有了它，我就不太知道如何利用它来获取根评论并将它们保持在回复的时间顺序中。我错过了什么？

注意：虽然我在这里寻求一般的 SQL 指导，但由于我正在开发的应用程序是 Rails 应用程序，因此也欢迎使用 Active Record 或 Arel 解释。

编辑：添加了示例表和预期结果。

SQL Ruby-on-Rails 数据库 PostgreSQL 自引用表

select gm.name, r.id, r.group_member_id, r.parent_id, r.created_at,
       max(c.created_at) as last_reply_at
  from comments c
       join group_members gm on gm.id = c.group_member_id
       join comments r on r.id = c.parent_id
 group by gm.name, r.id, r.group_member_id, r.parent_id, r.created_at
 order by gm.name, max(c.created_at) desc;

更新的小提琴

@MichaelBester 如果进一步简化对您有帮助，请尝试考虑不将连接回 .我重新加入的唯一原因是因为看起来您想要整行作为根注释。第一步是“从表格中按 per 给我所有最新的（按）评论”。然后，您可以按最新的 .联接回不计入行的选择或排序 - 它只是为了获取这些根注释列。comments rcomments rcreated_atgroup_member_idparent_idcommentscreated_atcomments r

上一个：Postgres 忽略了我在数组（字符串）列上的 GIN 索引

下一个：Rails：为什么添加唯一约束会删除结构.sql文件和数据库中的索引

如何查询自引用评论表以查找带有回复的评论，按最新回复排序？

How to query a self-referential comments table to find the comments with replies, ordered by the latest replies?

评论

评论