CODEWITHSUNDEEP
mysqlsql
hpdobrica
hpdobrica

Reputation: 134

Querying conversations from messages table

I have the messages table formatted as follows:

+------------------------------------------------------------------+
| id|sender_id| recipient_id | message_text |created_at            |
+------------------------------------------------------------------+
| 1 | 2       |  10          | "test1"      |"2017-04-10 09:05:45" |
| 2 | 10      |  2           | "test2"      |"2017-04-10 09:05:47" |
| 3 | 2       |  4           | "test3"      |"2017-04-10 09:05:49" |
| 4 | 10      |  4           | "test4"      |"2017-04-10 09:05:51" |
| 5 | 4       |  2           | "test5"      |"2017-04-10 09:05:53" |
| 6 | 2       |  10          | "test6"      |"2017-04-10 09:05:58" |
+------------------------------------------------------------------+

What I'm trying to do is get all the "conversations" of a logged in user (say user with id 2), along with the last messages for that conversation. I managed to pull out the id's of users user2 has messages with using this query:

select distinct users.id 
from messages, users where 
(recipient_id = 2 and users.id = sender_id) 
or 
(sender_id = 2 and users.id = recipient_id);

What this yields is 

4
10

as user2 has either sent and/or received messages from these two people (test1, test2, test6 for 10, and test3, test5 for 4).

What I can't do is modify this query so it also yields the last message sent to or received by the yielded id - for example

4  | test5
10 | test6

Upvotes: 2

Views: 94

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

If I understand your requirement correctly, you want to obtain the most recent message date for every conversation involving a certain user. In this case, we can aggregate over conversations for a given user and retain the most recent message date.

SELECT m1.*
FROM messages m1
INNER JOIN
(
    SELECT LEAST(sender_id, recipient_id)    AS first,
           GREATEST(sender_id, recipient_id) AS second,
           MAX(created_at) AS recent_date
    FROM messages
    WHERE sender_id = 2 OR recipient_id = 2
    GROUP BY LEAST(sender_id, recipient_id),
             GREATEST(sender_id, recipient_id)
) m2
    ON LEAST(m1.sender_id, m1.recipient_id)    = m2.first AND
       GREATEST(m1.sender_id, m1.recipient_id) = m2.second AND
       m1.created_at = m2.recent_date

Output:

enter image description here

Explanation:

The challenge in this query is to find a way to group conversations between two users together. I used the LEAST/GREATEST trick, which is a way that we can treat a 2 -> 4 and 4 -> 2 conversation as being logically the same thing. Then, using GROUP BY, we can identify the most recent conversation date for that pair of conversing users. So the subquery in my answer above finds, for each pair of users, without regard of any order, that pair along with its most recent conversation date. We then join this result back to the messages table to bring in the actual latest message text.

Demo here:

Rextester

Upvotes: 3

theEUG
theEUG

Reputation: 409

Use an order_by created_at desc statement at the end of your query to get the most recent messages.

Upvotes: 0

Related Questions