使用递归查询从表中获取电子邮件线程

Question

我有一个表格emails：

id     message_id          in_reply_to
1     <[e-mail]>   null
2     <[e-mail]>   <[e-mail]>
3     <[e-mail]>   <[e-mail]>
4     <[e-mail]>   <[e-mail]>
5     <[e-mail]>   null
6     <[e-mail]>   <[e-mail]>
7     <[e-mail]>   <[e-mail]>
8     <[e-mail]>   <[e-mail]>
9     <[e-mail]>   <[e-mail]>
10    <[e-mail]>   <[e-mail]>
11    <[e-mail]>   <[e-mail]>
...

(此表保存电子邮件线程，我们需要获取电子邮件线程)

1. 我只知道电子邮件的message_id和id及其in_reply_to可以为空或不为空。
2. 我需要获取这些消息的所有message_id和id，其中in_reply_to等于我们知道的message_id，并继续获取in_reply_to。
3. 获取message_id后，我需要搜索具有in_reply_to字段的其他电子邮件，因为message_id并获取，直到即将到来的message_id没有in_reply_to任何其他消息。
在
4. in_reply_to方面没有foreign_key关系 它只是一个列表，就像任何其他列一样。
5. id是电子邮件表的主键，message_id对于每封电子邮件始终是唯一的。
6. 一个message_id可以是许多消息in_reply_to。

如果我通过message_id=<[e-mail]>

我的输出表应该是

id     message_id          in_reply_to      
8     <[e-mail]>   <[e-mail]>
9     <[e-mail]>   <[e-mail]>
10    <[e-mail]>   <[e-mail]>
11    <[e-mail]>   <[e-mail]>

我只知道我需要使用递归，但我无法理解WITH RECURSIVE- https://www.postgresql.org/docs/current/queries-with.html

我尝试了一下：

WITH RECURSIVE emails AS (
SELECT message_id, in_reply_to FROM emails WHERE id = ?
UNION ALL
SELECT message_id, in_reply_to
FROM emails where in_reply_to =  // Stuck here
)
SELECT * 
FROM emails;

你能帮我解决查询吗？

Answer 1

加入 CTE 比较email_id和in_reply_to。

WITH RECURSIVE
thread
AS
(
SELECT e.id,
e.message_id,
e.in_reply_to
FROM emails e
WHERE id = 8
UNION ALL
SELECT e.id,
e.message_id,
e.in_reply_to
FROM emails e
INNER JOIN thread t
ON t.message_id = e.in_reply_to
)
SELECT *
FROM thread
ORDER BY id;

数据库<>小提琴