Query for the latest message in each conversation with every other user

Question

I have a table messages for conversations between the users. The table columns' names are:

messageID | fromUser | forUser | message | submitDate | seen

Sample data:

1  |  1 | 2 | "hi"             | "12341" | 0
2  |  2 | 1 | "hi"             | "12342" | 0
3  |  1 | 3 | "hi"             | "12343" | 0
4  |  1 | 4 | "hi 4"           | "12344" | 0
5  |  2 | 1 | "hello"          | "12345" | 0
6  |  1 | 2 | "hello how r u?" | "12346" | 0
7  |  3 | 1 | "hello user 1"   | "12345" | 0

How I can write a query to find the last message that was sent between myself and every other user in the system? I mean last messages are:

between user 1 and 2 : "hello how r u?"
between user 1 and 3 : "hello user 1"
between user 4 and 1 : "hi 4""

My query:

$query = "SELECT DISTINCT `fromUser`, `forUser`, `message`, `seen`, 
                          `username`, `userPhoto` 
          FROM `messages`,`user` 
          WHERE (`forUser`= '$myUserID' OR `fromUser`= '$myUserID')
                AND (((`forUser`= `userID`) AND (`forUser` !=  '$myUserID')) 
                   OR ((`fromUser`= `userID`) 
                   AND (`fromUser` !=  '$myUserID'))) 
          ORDER BY `submitDate` DESC";

but this query needs to fetch all messages in a conversation! I just need the last message.

Answer 1

Try this simple and easy one it will also find the users of each group Look at my code:-

select m.* ,u.*
    from
      messages m
      inner join (
            select max(id) as maxid
            from messages
            where messages.fromUser = "$myUsreId"
            OR messages.forUser = "$myUsreId"             
            group By (if(fromUser > forUser,  fromUser, forUser)), 
            (if(fromUser > forUser,  forUser, fromUser))
           ) t1 on m.id=t1.maxid 
      join 
      users u  ON u.id = (CASE WHEN m.fromUser = "$myUsreId"
                             THEN m.forUser
                             ELSE m.fromUser        
                         END)

Answer 2

You can use the following query to get the latest message for user with userID = 1 per user conversation with some other user:

SELECT messageID, message, submitDate, otherUser
FROM (
   SELECT messageID, message, submitDate,
          @row_number:=CASE WHEN @other=otherUser THEN @row_number+1 
                            ELSE 1 
                       END AS row_number,
          @other:=otherUser AS otherUser             
   FROM (
      SELECT messageID, message, submitDate,
             IF(1 = fromUser, forUser, fromUser) as otherUser             
      FROM messages AS m
      WHERE 1 IN (fromUser,forUser) 
      ORDER BY otherUser, submitDate DESC) t ) s
WHERE s.row_number = 1

SQL Fiddle Demo

The inner query:

SELECT messageID, message, submitDate,
       IF(1 = fromUser, forUser, fromUser) as otherUser             
FROM messages AS m
WHERE 1 IN (fromUser,forUser) 
ORDER BY otherUser, submitDate DESC

is used to return a list of messages either sent or received by user with userID = 1. Calculated column otherUser simply contains the other user, either sender or receiver, involved in the conversation with user with userID = 1.

In an outer query, variable @row_number is used in order to simulate ROW_NUMBER() OVER (PARTITION BY otherUser ORDER BY submitDate DESC) available in SQL Server.

Finally, the outer query uses the number calculated by @row_number to select only the most recent message per otherUser.

Answer 3

If messageId is an auto_increment primary key then you can use its values to distinguish which is the latest message in each conversation. If submitDate has type DATETIME or TIMESTAMP then would be another choice for that purpose, but if it has type DATE then its resolution is not sufficient.

The key thing, though, is to identify and filter on the timestamps or ids of the latest messages. You can identify the IDs or timestamps on a per-conversation basis with a suitable aggregate (sub)query, and perform the filtering via an inner join, like so:

SELECT m.*
FROM
  messages m
  JOIN (
    SELECT
      MAX(messageId),
      CASE
        WHEN fromUser = '$myUserId' THEN forUser
        WHEN forUser = '$myUserId' THEN fromUser
      END AS otherUser  
    FROM messages
    GROUP BY
      CASE
        WHEN fromUser = '$myUserId' THEN forUser
        WHEN forUser = '$myUserId' THEN fromUser
      END
    HAVING otherUser IS NOT NULL
  ) other
    ON m.messageId = other.messageId