Fetch conversations from a row oriented form in postgresql

Question

I have a table with the following data:

id | client_id | message                       | user_id | incoming
1  | 1         | Hi, anybody there?            | 2       | True
2  | 1         | I need help                   | 2       | True
3  | 1         | Yes, I am here to help you.   | 2       | False
4  | 2         | Did you solve it yet?         | 5       | True
5  | 3         | Is my issue resolved?         | 5       | True
6  | 2         | yes, it is solved             | 5       | False
7  | 5         | Are you happy with us?        | 3       | False
8  | 5         | yes, very much                | 3       | True

The clients are talking with users and incoming=True means that the message is from the client whereas False means that the user has sent the message. I want the result to be like this:

client_id | client_message                    | user_id  | user_message
1         | Hi, anybody there? I need help    | 2        | Yes, I am here to help you.
2         | Did you solve it yet?             | 5        | yes, it is solved

I want the conversations attached in one row where the client has sent the message first and then the user has replied to that. Notice, that at the end, in rows 7,8 it seems like there is the conversation but since the user started it, it is not added in the result. The row with ID 5 is discarded because it doesn't have any reply.

My query currently is like this:

SELECT client_id, t1.message as client_message, user_id, t2.message as user_message
FROM conversations c1 INNER JOIN conversations c2
ON c1.client_id=c2.client_id AND c1.user_id=c2.user_id AND c1.incoming=True

but it is not resulting in a proper response. Any help will be highly appreciated. Thanks!

Answer 1

This is an interesting problem.

See this dbfiddle. After running it the first time, uncomment the insert for id 15 to see what dialog_id is all about.

with responses as (
  select id, client_id, user_id, 
         row_number() 
           over (order by client_id, user_id, id) 
           as dialog_id
    from conversations
   where incoming = false
), dialogs as (
  select c.*, min(dialog_id) as dialog_id
    from conversations c
    join responses r
      on r.client_id = c.client_id
     and r.user_id = c.user_id
     and r.id >= c.id
   group by c.id, c.client_id, c.message, c.user_id, c.incoming
)
select dialog_id, client_id,
       array_agg(message order by id) 
         filter (where incoming = true) 
           as client_message,
       user_id,
       max(message) 
         filter (where incoming = false) 
           as user_message  
  from dialogs
 group by dialog_id, client_id, user_id
order by dialog_id;