Mysql queries two tables, retrieves messages for each conversation

I have two tables.

One of them, called "conversations", has the following data.

ConversationID  Sender     Reciever
1               bla1       bla2
2               bla1       bla3
3               bla1       bla4


Another called "Messages" has the following data.

MessageID    MessageText    TimeAddedMessage        ConversationID
1             helo           2012-03-12 13:00:00          2
2             helo           2012-03-12 13:01:00          1
3             helo           2012-03-12 13:02:00          3
4             helo           2012-03-12 13:03:00          3
5             helo           2012-03-12 13:04:00          2


The result I want to get from the request is the following:

5             helo           2012-03-12 13:04:00          2
4             helo           2012-03-12 13:03:00          3
2             helo           2012-03-12 13:01:00          1


This means that we need the most recent comment for each conversation (DESC sorted by time).

Any help was appreciated.


source to share

3 answers

Try -

SELECT m2.*, c.Sender
    SELECT m1.ConversationID, MAX(m1.MessageID) AS MessageID
    FROM Messages m1
    GROUP BY m1.ConversationID
) latest_msg
INNER JOIN Messages m2
    ON latest_msg.MessageID = m2.MessageID
    AND latest_msg.ConversationID = m2.ConversationID
INNER JOIN Conversations c
    ON m2.ConversationID = c.ConversationID


EDIT . I modified the above query to include the Sender value from the Conversations table. I noticed that your structure for speaking is a bit weird. The conversation goes from one user to another user, but there is no way to determine which user wrote each message. Is this intentional?



You can do this by creating a view with a dialog interface and max TimeAddedMessage and attaching it to messages:

select Messages.MessageID, Messages.MessageText, 
       Messages.TimeAddedMessage, Messages.ConversationID
  from Messages inner join
    select ConversationID, max (TimeAddedMessage) TimeAddedMessage
      from Messages
     group by ConversationID
  ) LastMessages
  on Messages.ConversationID = LastMessages.ConversationID 
     and Messages.TimeAddedMessage = LastMessages.TimeAddedMessage




Simple query with good performance for relatively small datasets.

FROM Messages m1 LEFT JOIN Messages m2
 ON (m1.ConversationId = m2.ConversationId AND m1.TimeAddedMessage < m2.TimeAddedMessage)


Modified from this post .

This kind of counter is intuitive, but has become part of an overused cookbook recipe.



All Articles