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

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. 我只知道电子邮件的 and 它message_id可以为空或不为空。idin_reply_to
2. 我需要获取那些 in_reply_to 等于我们知道的消息的所有message_ids 和s，并继续使用 in_reply_to 获取。idmessage_id
3. 获取 message_id 后，我需要搜索具有in_reply_to此字段的其他电子邮件message_id并获取，直到即将到来的 message_id 不in_reply_to属于任何其他消息。
4. 没有foreign_key关系，in_reply_to 它只是一个列表，就像任何其他列一样。
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;

你能帮我解决这个问题吗？

标签： sqlpostgresqlhierarchical-datarecursive-query

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;

db<>小提琴