CODEWITHSUNDEEP
mysqlsql
Stefan
Stefan

Reputation: 709

MySQL, how can I select conversations from a list of messages

I have this code:

SELECT a.id, a.to_id, a.from_id, a.seen, a.date, a.message

FROM  `Chat_messages` a

INNER JOIN (
    SELECT MAX(  `id` ) AS id
    FROM  `Chat_messages` AS  `alt` 
    WHERE  `alt`.`to_id` =7
    OR  `alt`.`from_id` =7
    GROUP BY  `to_id` ,  `from_id`
)b ON a.id = b.id

returning:

enter image description here

So, I want to get conversations (sent and received messages) of an user and latest message of it. Latest message works ok, but the problem is that I get 2 rows from messages received (#1 and #2) and 2 rows from messages sent (#3 and #4), but I only need 2 results, because there are 2 conversations.

Upvotes: 1

Views: 1153

Answers (2)

David Bachmann Jeppesen
David Bachmann Jeppesen

Reputation: 395

The best way to pick out the row that holds the latest sent message and the row that holds the latest received message respectively, is using the row_number() window function. Unfortunately, MySql does not support window functions, so I think it's best to use two nested SELECT's:

SELECT z.id, max(z.to_id), max(z.from_id), max(z.seen), max(z.date), max(z.message)
FROM chat_messages z
LEFT JOIN 
(SELECT x.from_id, max(date) date
FROM chat_messages x
GROUP BY x.from_id) f
ON z.from_id = f.from_id AND z.date = f.date
LEFT JOIN
(SELECT y.to_id, max(date) date
FROM chat_messages y
GROUP BY y.to_id) t
ON z.to_id = t.to_id AND z.date = t.date
GROUP BY z.id

I do not recommend using max on ID's if you care about correctness in the long run.

Upvotes: 3

Fabricator
Fabricator

Reputation: 12772

You can group by least(to_id, from_id), greatest(to_id, from_id) to make sure conversions between 2 people are merged:

SELECT a.id, a.to_id, a.from_id, a.seen, a.date, a.message

FROM  `Chat_messages` a

INNER JOIN (
    SELECT MAX(  `id` ) AS id
    FROM  `Chat_messages` AS  `alt` 
    WHERE  `alt`.`to_id` =7
    OR  `alt`.`from_id` =7
    GROUP BY  least(`to_id` ,  `from_id`), greatest(`to_id` ,  `from_id`)
)b ON a.id = b.id

Upvotes: 2

Related Questions