1

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 ... ??
  • I think you need a self join – Mihai Feb 2 '14 at 19:26
  • 1
    Do you want to count them or show all of them? In the second case, you don't want to use GROUP BY. – ypercubeᵀᴹ Feb 2 '14 at 20:39
  • I want show them (not individual messages, but conversations, sorted by latest activity) – nute Feb 2 '14 at 20:41
  • 1
    And how do you know which message belongs to which conversation? You mean all messages between two users constitute a conversation? – ypercubeᵀᴹ Feb 2 '14 at 20:48
  • yes @ypercube . – nute Feb 3 '14 at 7:44
3

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.

  • Thank you. It looks good, however I took a simpler approach by getting some help from php. I created a hash combining both user ids (stackoverflow.com/questions/21515318/…) and used that to identify each conversations. I added a conversation field, and was able to use a simple GROUP BY conversation. – nute Feb 3 '14 at 7:47
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 :)

1
SELECT *
  FROM message
 WHERE (from_id = 1 OR to_id = 1)
 GROUP BY IF(from_id=1, to_id, from_id)
  • 2
    Could you please explain how the part that is different (IF) works? That would make your answer more valuable than just a piece of code. – dezso Apr 19 '18 at 16:31
  • I think the meaning is clear as the code presents. Take some example data: – Bryant Jun 25 '18 at 9:46
  • it will put all pairs which contain the given id (here is 1) and the same opposite id into one group. – Bryant Jun 25 '18 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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