CODEWITHSUNDEEP
mysqlsql
Khalid
Khalid

Reputation: 77

Select latest message from conversation

I have two tables, conversation_tbl and conversation_reply_tbl

conversation_reply_tbl
-------------------------
|  conversation_reply_id|   //auto increment id      
|  reply_message        |   //message text
|  user_id              |   //the user who sent the message
|  datetime_sent        |   //date and time of message sent
|  status               |   //values '0' or '1' (read or not)
|  conversation_id      |   //relation to the conversation
-------------------------

conversation_tbl
-------------------------
|  conversation_id      |    //auto increment id     
|  from_user            |    //the user who sent the message
|  to_user              |    //the recipient
|  datetime_created     |    //date of the message created
|  gallery_id           |//the gallery where the message started from
-------------------------

Now I use the conversation_reply_tbl to store all the messages that go on in a conversation and conversation_tbl to store the conversation main data.

Now, I want to select all conversations that the user is involved in but I want to show the most recent message of every conversation, just like how it's done in famous websites and apps such as 'WhatsApp' or 'Facebook'.

Could this be done in one query or do I have to use a function with PHP?

Upvotes: 0

Views: 253

Answers (3)

trincot
trincot

Reputation: 350147

Here is how you can get that information with one query:

select     t.*,
           last.*
from       conversation_tbl t
inner join (select   conversation_id, max(conversation_reply_id) conversation_reply_id
            from     conversation_reply_tbl
            group by conversation_id) all_lasts
        on all_lasts.conversation_id = t.conversation_id
inner join conversation_reply_tbl last
        on last.conversation_reply_id = all_lasts.conversation_reply_id
left join  (select   conversation_id 
            from     conversation_reply_tbl
            where    user_id = :the_user_id
            group by conversation_id) user_conv
        on user_conv.conversation_id = t.conversation_id
where      (     user_conv.conversation_id is not null
           or    t.to_user = :the_user_id )

Note the left join, as it might be that the targeted user has not yet replied with a message, but was the to_user of the starting message. The where clause makes an or of both situations: if the message thread has a message of the user, or if the user is the target of the initiating message, the query should return that conversation.

Upvotes: 1

Steven Cao
Steven Cao

Reputation: 1

I assume that you save all the conversation messages under reply_message column of conversation_reply_tbl table.

In that case, you can try this

SELECT T2.conversation_id, T2.reply_message
FROM conversation_tbl T1  INNER JOIN  conversation_reply_tbl T2 ON 
T1.conversation_id = T2.conversation_id
WHERE (T1.from_user = $userID OR T1.to_user = $userID)
GROUP BY T1.conversation_id
ORDER BY T2.datime_sent DESC;

Upvotes: 0

Kade M.
Kade M.

Reputation: 181

If I understand what you're trying to do correctly, something like this would provide you with the record if provided the appropriate conversation_id.

Get the conversations which the users is a part of

SELECT conversation_id FROM conversation_tbl WHERE from_user = ?

With that id, get the last message that was sent.

SELECT datetime_sent, reply_message FROM conversation_reply_tbl
WHERE datetime_sent IN (SELECT MAX(datetime_sent)
FROM conversation_reply_tbl WHERE conversation_id = ? )

Upvotes: 0

Related Questions