Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ... ??
share|improve this question
    
I think you need a self join –  Mihai Feb 2 at 19:26
    
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 at 20:39
    
I want show them (not individual messages, but conversations, sorted by latest activity) –  nute Feb 2 at 20:41
    
And how do you know which message belongs to which conversation? You mean all messages between two users constitute a conversation? –  ypercube Feb 2 at 20:48
    
yes @ypercube . –  nute Feb 3 at 7:44
show 1 more comment

1 Answer

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.

share|improve this answer
    
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 at 7:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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