Recursive query in Oracle

Question

I am kind of new to the more advanced topics of PLSQL, so hopefully someone can help me out.

The problem: I have a table with messages sent between an admin and users. The table has a message_parent with FK to the same table message_id field: in case the field is populated, then it means that message was sent as a reply to a previous message. I need to select all the messages that are part of the same conversation and display them. Can this be done with a single query or do I need a procedure to handle that kind of logic? As I understand, it needs to be recursive, since the message_id by which I am searching, is always changing

Example Messages table:

|message_id|parent_id|message_content|
|----------|---------|---------------|
|101       |100      | foo           |
|100       |97       | bar           |
|99        |(null)   | Left out      |
|97        |(null)   | baz           |

So the correct query selecting message_content should return "baz", "bar" and "foo" but not "Left out" (since baz is the original message). This would be simple if there were e.g. only two messages that can be tied together or e.g. a thread_id column, that would link all messages in the same 'thread', but with the parent_id's constantly shifting, I am having trouble figuring it out.

Answer 1

In Oracle this is easily done using CONNECT BY

select message_id, parent_id, message_content
from messages
start with message_id = 97 -- this is the root of your conversation
connect by prior message_id = parent_id;

This walks the tree from top to bottom.

If you want to walk the tree from a single message to the root, change the start with and the connect by part:

select message_id, parent_id, message_content
from messages
start with message_id = 100 -- this is the root of your conversation
connect by prior parent_id = message_id; -- this now goes "up" in the tree

Answer 2

To get the entire message context based off of a single message_id, you can use two hierarchical queries. One walks the message tree from the current message down to the root, while the second walks back up from the root to the leaves. Assuming the current message_id is 100 (though values of 101 or 97 would have the same end result), the following query returns all related messages (all except 'Left out'):

with msgs(message_id, parent_id, message_content) as (
  select 101, 100, 'foo' from dual union all
  select 100, 97, 'bar' from dual union all
  select 99, null, 'Left out' from dual union all
  select 97, null, 'baz' from dual
), down as (
  select message_id start_id
       , CONNECT_BY_ROOT message_id curr_id
    from msgs
   where connect_by_isleaf = 1
   start with message_ID = 100
 connect by message_ID = prior parent_ID
) /* up */
 select level lvl
      , case message_id when curr_id then '*' end curr
      , msgs.* 
   from msgs, down
  start with message_ID = start_id
connect by prior message_ID = parent_ID
  order siblings by message_id;

In the down query I've limited it to return only the root message node (connect_by_isleaf) such that start_id will be the root message, and included the current message_id as curr_id for reference in the up query where I've included a column to mark the current message with an asterisk.