0

I have DB structure like this one: structure.

message_id  from_user_id    to_user_id  subject    body    date_add    parent_message_id
1           1               2           message 1  body 1  2024-07-01  null
2           2               1           message 2  body 2  2024-07-01  1
3           1               2           message 3  body 3  2024-07-01  2
4           2               1           message 4  body 4  2024-07-01  null
5           1               2           message 5  body 5  2024-07-01  null
6           1               2           message 6  body 6  2024-07-01  null
7           2               1           message 7  body 7  2024-07-01  6
8           1               2           message 8  body 8  2024-07-01  7

I'm looking for efficient way to find last_message_id in each thread and total message count in each thread. If you have messages connected together with parent_message_id this create message thread (like in Gmail).

For example for:

message_id last_thread_message_id total_thread_count
1          3                      3  
2          3                      3
3          3                      3
4          4                      1
5          5                      1
6          8                      3
7          8                      3
8          8                      3

thanks for advice

R.

3
  • Can you define what "thread" is in your context?
    – Sahap Asci
    Commented Jul 1 at 18:33
  • 1
    Common recursive CTE will solve.
    – Akina
    Commented Jul 1 at 18:52
  • @SahapAsci I have updated problem description. If you connect together messages with parent_message_id this create some message thread. Imagine you send message, someone reply to you to this message, you reply again, etc. This all message create one thread
    – Robert
    Commented Jul 2 at 6:48

1 Answer 1

0

I am not sure if its efficient but this is how I solve such "tree/hierarchy" problem.

WITH RECURSIVE 
message_tree(message_id, message_ids, hierarchy_level) AS (
  -- create a tree by using recursive CTE
  SELECT
      m.message_id,
      ARRAY[m.message_id]                   AS message_ids,
      1                                     AS hierarchy_level
    FROM message_tab m
    WHERE 
      m.parent_message_id ISNULL
  
  UNION ALL
  
  SELECT
      m.message_id,
      mt.message_ids || m.message_id,
      array_length(mt.message_ids || m.message_id, 1)
    FROM message_tab m
    JOIN message_tree mt ON m.parent_message_id = mt.message_id

   
),
agg_message_tree AS (
  -- filter tree, left only record with last message
  SELECT distinct ON (message_ids[1])
      mt.message_id     last_thread_message_id,
      mt.message_ids,
      mt.hierarchy_level
    FROM message_tree mt
    ORDER BY mt.message_ids[1], mt.hierarchy_level DESC
)
  --unnest aggregated tree to get all messages
SELECT
    unnest (amt.message_ids) as message_id,
    amt.last_thread_message_id,
    amt.hierarchy_level
  FROM agg_message_tree amt
  ORDER BY 1

fiddle here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.