Fetch conversations from a row oriented form in postgresql

user_5220257 0
Viewed 39

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!

postgresql
2 Answers

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;
user_138083190

This is IMHO textbook example to demonstrate analytic functions. I assume multiple messages can repeat between same client-user pair in both directions and you want to group subsequent messages going in same direction together, utilizing id as sequence number. Possible solution might be (some rows added by myself):

with t (id, client_id, message, user_id, incoming) as (values
(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),
(9  , 1         , 'Hi, anybody there again?'      , 2       , True),
(10 , 1         , 'I need help'                   , 2       , True),
(11 , 1         , 'Yes, I am here to help you.'   , 2       , False),
(12 , 1         , 'Again.'                        , 2       , False)
), ch as (
  select t.*
       , case coalesce(incoming !=
             lag(incoming) over (
               partition by client_id, user_id 
               order by id
             )
             , true) 
           when true then 1
           else 0
         end as incoming_changed
  from t
), groups as (
  select ch.*
       , sum(incoming_changed) over (
           partition by client_id, user_id
           order by id
         ) as grp
  from ch
), grouped as (
  select client_id
       , string_agg(message, ' ') as message
       , user_id
       , incoming
       , min(id) as min_id
  from groups
  group by client_id, user_id, grp, incoming
  order by client_id, user_id, min_id
), paired as (
  select grouped.*
       , lead(min_id) over (
           partition by client_id, user_id
           order by min_id
         ) as response_id
  from grouped
)
select pc.client_id
     , pc.message as client_message
     , pu.user_id
     , pu.message as user_message
from paired pc
join paired pu on pc.response_id = pu.min_id
where pc.incoming

Explanation: first detect when conversation direction changes. It divides rows into groups. Messages are aggregated within each group (see CTE select * from grouped). Then pair each client's message with user response (if any) which is the next by id (I hope this column has a role of timestamp).

DB fiddle here

user_6535390
Related