CODEWITHSUNDEEP
mysqlsql
Tzook Bar Noy
Tzook Bar Noy

Reputation: 11677

select last message of conversation for each conversation in mysql

my query:

SELECT *
  FROM messages_status mst
       INNER JOIN messages msg 
             ON mst.msg_id=msg.id
 WHERE mst.user_id = 1 
       AND mst.status NOT IN (0,3)
ORDER BY 
       msg.created_at DESC

will show up in this sqlfiddle: http://sqlfiddle.com/#!2/be010/1/0

You have there the 2 tables: messages and message_status first contains message data like content, sender_id, created time, and conversation id. Second has specific user msg status, is it read or deleted.

I want to get the last message of each conversation

any ideas?

Upvotes: 3

Views: 1976

Answers (4)

Neels
Neels

Reputation: 2543

Here is the Fiddle. http://sqlfiddle.com/#!2/be010/116

This is the query which you can use mostly:

SELECT *
FROM 
(SELECT messages.id as id, 
messages.conv_id as conv_id,
max(messages.created_at) as created_at FROM messages,messages_status
WHERE messages.id = messages_status.msg_id
AND messages_status.user_id = 1 
AND messages_status.status NOT IN (0,3)
GROUP BY messages.conv_id) max_created
INNER JOIN messages 
ON messages.created_at = max_created.created_at
INNER JOIN  messages_status mst 
ON mst.msg_id=max_created.id
GROUP BY messages.conv_id
ORDER BY max_created.created_at DESC;

Upvotes: 1

Justin
Justin

Reputation: 9724

SQLFIDDLEExample

Query:

SELECT msg.*
FROM messages_status mst
INNER JOIN messages msg ON mst.msg_id=msg.id
LEFT JOIN messages ms2
 ON ms2.conv_id = msg.conv_id
 AND ms2.created_at > msg.created_at
WHERE mst.user_id = 1 
 AND mst.status NOT IN (0,3)
 AND ms2.created_at is null

Result:

| ID | SENDER_ID | CONV_ID |       CONTENT |                   CREATED_AT |                   UPDATED_AT |
|----|-----------|---------|---------------|------------------------------|------------------------------|
| 16 |         3 |       3 |         hy :) | March, 26 2014 16:38:17+0000 | March, 26 2014 16:38:17+0000 |
| 14 |         1 |       1 | kjhkjhkjh  kl | March, 26 2014 16:16:25+0000 | March, 26 2014 16:16:25+0000 |

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

As you asked get the last message of each conversation You may try this

select *
from messages msg
inner join
(
  select max(created_at) created_at
  from messages
  group by conv_id
) m2
  on msg.created_at = m2.created_at

Inner JOIN messages_status mst ON msg.id=mst.msg_id 
WHERE mst.user_id = 1 AND mst.status NOT IN (0,3)

http://sqlfiddle.com/#!2/be010/89

Upvotes: 1

VahidSH
VahidSH

Reputation: 81

Check this out:

SELECT *
  FROM messages_status mst
       INNER JOIN (SELECT messages.* FROM messages INNER JOIN (
                    SELECT sender_ID,conv_id,MAX(created_at) as maxtime FROM messages
                    GROUP BY sender_ID,conv_id) as t1 ON
                    t1.sender_id = messages.sender_id AND t1.conv_id = messages.conv_id AND 
                    t1.maxtime = messages.created_at) msg 
                    ON mst.msg_id=msg.id
WHERE mst.user_id = 1 AND mst.status NOT IN (0,3)
ORDER BY msg.created_at DESC

this is the link

Upvotes: 2

Related Questions