Jump to content


Photo

Latest Conversations


Best Answer Psycho, 14 February 2018 - 05:00 PM

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.

Go to the full post


  • Please log in to reply
7 replies to this topic

#1 mentalist

mentalist
  • Members
  • PipPipPip
  • Advanced Member
  • 291 posts

Posted 14 February 2018 - 02:09 AM

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



#2 Zane

Zane
  • Administrators
  • Advanced Member
  • 4,134 posts

Posted 14 February 2018 - 05:25 AM

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

btn_donate_SM.gif Want to thank me? Contribute to my PayPal piggy-bank
 

172938.png

#3 mentalist

mentalist
  • Members
  • PipPipPip
  • Advanced Member
  • 291 posts

Posted 14 February 2018 - 03:55 PM

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



#4 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,892 posts
  • LocationCanada

Posted 14 February 2018 - 05:00 PM   Best Answer

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 by Psycho, 14 February 2018 - 05:03 PM.

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#5 mentalist

mentalist
  • Members
  • PipPipPip
  • Advanced Member
  • 291 posts

Posted 14 February 2018 - 05:32 PM

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



#6 mentalist

mentalist
  • Members
  • PipPipPip
  • Advanced Member
  • 291 posts

Posted 14 February 2018 - 05:46 PM

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



#7 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,892 posts
  • LocationCanada

Posted 14 February 2018 - 06:00 PM

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.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#8 mentalist

mentalist
  • Members
  • PipPipPip
  • Advanced Member
  • 291 posts

Posted 15 February 2018 - 08:49 AM

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!






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users