在我正在使用的一个应用程序中,我的Postgres数据库中有一个 comments 表,它可以简化为如下所示:
+----+-----------+-----------------+---------------------+
| 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 |
| 5 | 1 | 1 | 2023-08-01 12:00:05 |
| 6 | 2 | 2 | 2023-08-01 12:00:06 |
| 7 | 2 | 1 | 2023-08-01 12:00:07 |
+----+-----------+-----------------+---------------------+
填充parent_id
列时,它会引用COMMENT表中的另一行.这将建立父/子关系.换句话说,一条 comments 可以有其他回复的 comments .任何家长的 comments 都可以有很多回复.就这款应用程序而言, comments 父/子关系只有一个级别.
还有一个Group Members表,由Comments表通过外键引用.它代表每个 comments 的作者,可以简化为:
+----+---------------+
| id | name |
+----+---------------+
| 1 | Johnny Tables |
| 2 | Susan Select |
+----+---------------+
对于任何给定的组成员,我想要找到他们回复的最新不同的根 comments ,按照他们回复的顺序.例如,Johnny Tables
(Id 1)将得到:
+----+-----------+-----------------+---------------------+
| 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 |
+----+-----------+-----------------+---------------------+
和Susan Select
(ID 2)将获得:
+----+-----------+-----------------+---------------------+
| id | parent_id | group_member_id | created_at |
+----+-----------+-----------------+---------------------+
| 2 | NULL | 2 | 2023-08-01 12:00:01 |
+----+-----------+-----------------+---------------------+
Here is a fiddle,它用示例数据设置表 struct .
我try 了使用子查询以及DISTINCT
和ORDER BY
的各种咒语,但我遗漏了一些东西.例如,使用下面的查询,我似乎得到了正确的答复,但实际上并没有按created_at
日期进行排序.它们是按升序parent_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
一旦我有了它,我就不太知道如何利用它来获得根 comments ,并将它们保持在回复的时间顺序上.我遗漏了什么?
Note: While I'm asking for general SQL guidance here, since the app I'm working on is a Rails app, an Active Record or Arel interpretation would also be welcome.个
新增EDIT:张样表和预期结果.