Jump to content

Latest Conversations


Go to solution Solved by Psycho,

Recommended Posts

Suppose there is a table with the following columns:

    id, uid_to, uid_from, msg, date_sent

Using the following selects all interactions with our user:

    SELECT * FROM tbl WHERE (uid_to=".$uid." OR uid_from=".$uid.") ORDER BY date_sent

How to extend this to group by ((to & from) and (from & to)), so that it only shows the latest in any conversation?



Link to comment
Share on other sites

Assuming your db schema is setup properly, you would just add DESC to order descendingly by date_sent

ORDER BY date_sent DESC

But if you have a primary index also, you could select more accurately like this


Add onto your ORDER BY clause, and order by the ID descendingly after ordering by date_sent.

ORDER BY date_sent DESC, id DESC
Link to comment
Share on other sites

  • Solution

There is a particular aspect of your request that I feel needs to be clarified.



How to extend this to group by ((to & from) and (from & to)), so that it only shows the latest in any conversation?


Let's say there are messages between Mike & Bob. So, some message are From: Mike -> To: Bob and others are From: Bob -. To: Mike. It would be simple® to group all of those into two different groups. But, if I understand you correctly, you want to group all of the conversations together where the two people in the From and To columns are the same two people - regardless of who was in the From and who was in the To.


I'm sure there are more efficient ways to do this, but here is how I would approach it. First, we need a way to have a unique identifier for the two people in a message regardless of who is in the From/To columns. I would concatenate the two user IDs ensuring the lower number comes before the higher number. E.g. 

CONCAT(LEAST(uid_to, uid_from), '-', GREATEST(uid_to, uid_from)) as users_key

You can then GROUP BY that to get the most recent ID  for conversations with unique participants. Then you need to use that to get the records for those conversations.

FROM tbl
    SELECT MAX(id)
    FROM tbl
    GROUP BY CONCAT(LEAST(uid_to, uid_from), '-', GREATEST(uid_to, uid_from))

Note: I used a dash '-' between the IDs in the CONCAT() because there would be erroneous associations otherwise. E.g. records (1 and 122) would create the same key as (11 and 22): 1122. Using the dash, I instead get 1-122 vs. 11-22.

Edited by Psycho
  • Like 1
Link to comment
Share on other sites

Brilliant, works a charm, many thanks.



As to using this method, in PM system I use two messages so can track deletes easily, whereas here I'm trying a MSG system without delete (*more a buffer) and half the storage... Who knows


First, was there any doubt?! :)


Second, not sure what you are saying, really. If you want to allow deletes, no reason for two messages. You would have one table with the messages (including From/To fields) and then a separate, associative table representing the 'inbox' for each user with a reference to the message. When a user deletes a message in their inbox, you would delete the associative reference. And, if you so choose, you could then see if there are any remaining references to the message. If not, delete the message. So, if there are two people involved in a message, the first one to delete only deletes their reference. The second one to delete will delete their reference and the message.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.