Latest Conversations

[mentalist]

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?

 

Thanks

[Zane]

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

[mentalist]

Hi, its about the grouping msg's over two columns, not how to use sort... Thanks though

[Psycho]

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.

SELECT *
FROM tbl
WHERE id IN 
(
    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 February 14, 2018 by Psycho

[mentalist]

Freakishly excellent as usual! I'd got half way there last night but the way you order the id's and use a separator makes it work. Not tried yet but many thanks

[mentalist]

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

[Psycho]

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.

[mentalist]

You're right... I used to use a status variable, 0 for undeleted, 1=sender_deleted,2=receiver_deleted, so if greater than 0 then set 3 (fake double delete).

 

But I like your reference table, works better for groups, thanks... Know I need an education!