Group by reversible pair

Question

I'm sure my title is not clear enough...

Let's say I have a "chat" table, storing individual messages like this:

message_id, from_user_id, to_user_id, timestamp

For a given user_id, I want a list of "conversations" for this user. A conversation would be all messages involving the same 2 users. Kinda of like Facebook.

What would be the best way to achieve this? I was starting to write a simple query with GROUP BY but then realized the pair of user ids could be both ways - but to me it's still the same conversation.

SELECT * FROM message WHERE (from_id = 1 OR to_id = 1) GROUP BY ... ??

Answer 1

You can use a UNION of two subqueries:

SELECT 
    user_id,
    MAX(ts)  AS last_timestamp,
    SUM(cnt) AS number_of_messages
FROM
  ( SELECT  to_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE from_user_id = 1
    GROUP BY to_user_id 
  UNION ALL
    SELECT  from_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE to_user_id = 1
    GROUP BY from_user_id 
  ) AS tmp 
GROUP BY 
    user_id ;

Two indexes, on (from_user_id, to_user_id, timestamp) and (to_user_id, from_user_id, timestamp) would help efficiency of the subqueries.

Answer 2

I know it's a late answer but I created this to do a similar thing in an application:

SELECT * FROM message WHERE (from_id = 1 OR to_id = 1) GROUP BY
CONCAT(GREATEST(from_id,to_id), '-', LEAST(from_id,to_id))

Hopefully this helps someone :)

Answer 3

SELECT *
  FROM message
 WHERE (from_id = 1 OR to_id = 1)
 GROUP BY IF(from_id=1, to_id, from_id)