0

I have a table 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>
     ...

(this table holds the thread of emails, we need to fetch the email thread)

  1. I just know the message_id and id of an email and its in_reply_to can be null or not null.
  2. I need to fetch all the message_ids and ids of those messages where in_reply_to is equal to the message_id we know and keep fetching with in_reply_to.
  3. Once the message_id is fetched I need to search for other emails who have in_reply_to field as this message_id and fetch until the coming message_id is not in_reply_to of any other messages.
  4. There is NO foreign_key relationship with respect to in_reply_to and it's just a list like any other column.
  5. id is primary-key for emails table, message_id is always unique to each email.
  6. One message_id can be in_reply_to of many messages .

If I pass message_id = <me912@gmail.com>

my output table should be

      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>

I just know I need to use recursive and I got stuck understanding WITH RECURSIVE - https://www.postgresql.org/docs/current/queries-with.html

I tried somewhat this: 

    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;

Can you help me to fix the query, please?

Coder
  • 540
  • 1
  • 11
  • 34

1 Answers1

1

Join the CTE comparing email_id and 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<>fiddle

sticky bit
  • 36,626
  • 12
  • 31
  • 42