Блог

У меня есть таблица emails :

 
     id     message_id          in_reply_to
      1     <me123@gmail.com>   null
      2     <me345@gmail.com>   <me123@gmail.com>
      3     <me567@gmail.com>   <me345@gmail.com>
      4     <me768@gmail.com>   <me567@gmail.com>
      5     <me910@gmail.com>   null
      6     <me911@gmail.com>   <me768@gmail.com>
      7     <me912@gmail.com>   <me567@gmail.com>
      8     <me913@gmail.com>   <me912@gmail.com>
      9     <me914@gmail.com>   <me913@gmail.com>
      10    <me915@gmail.com>   <me914@gmail.com>
      11    <me916@gmail.com>   <me914@gmail.com>
     ...

  

(эта таблица содержит поток электронных писем, нам нужно извлечь поток электронной почты)

1. Я просто знаю message_id и id электронного письма, и оно in_reply_to может быть нулевым или не нулевым.
2. Мне нужно извлечь все message_id s и id те сообщения, где in_reply_to равно message_id известному нам, и продолжать выборку с помощью in_reply_to.
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 = <me912@gmail.com>

моя выходная таблица должна быть

       id     message_id          in_reply_to      
      8     <me913@gmail.com>   <me912@gmail.com>
      9     <me914@gmail.com>   <me913@gmail.com>
      10    <me915@gmail.com>   <me914@gmail.com>
      11    <me916@gmail.com>   <me914@gmail.com>
  

Я просто знаю, что мне нужно использовать рекурсивный, и я застрял в понимании 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;

  

Можете ли вы помочь мне исправить запрос, пожалуйста?

Присоединитесь к 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<>скрипта