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.

+3


source to share


3 answers


Try -

SELECT m2.*, c.Sender
FROM (
    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
ORDER BY m2.MessageID DESC

      



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?

+3


source


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

      

+1


source


Simple query with good performance for relatively small datasets.

SELECT m1.*
FROM Messages m1 LEFT JOIN Messages m2
 ON (m1.ConversationId = m2.ConversationId AND m1.TimeAddedMessage < m2.TimeAddedMessage)
WHERE m2.MessageID IS NULL;

      

Modified from this post .

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

+1


source







All Articles