How can I can all unique user whom I send message and receive message

I have table in which all conversation is stored like this

Suppose I have a table like this (fromUser and toUser is fk from user table)

-----------------------------------------
Id.  |  FromUser  |  toUser  | message  |  
     |            |          |          |
1    |     1      |    2     |  Hi      |
2    |     2      |    1     |  hello   |  
3    |     3      |    1     |  hi      |
4    |     1      |    4     |  hello   |
-----------------------------------------

Desired output for distinct row for user 1 orderBy last updated date

-----------------------------------------
Id.  |  FromUser  |  toUser  | message  |  
     |            |          |          |
2    |     2      |    1     |  hello   |  
3    |     3      |    1     |  hi      |
4    |     1      |    4     |  hello   |
-----------------------------------------

Desired output for distinct row for user 2 orderBy last date

-----------------------------------------
Id.  |  FromUser  |  toUser  | message  |  
     |            |          |          |
2    |     2      |    1     |  hello   |  
-----------------------------------------

Desired output for distinct row for user 3 orderBy last date

-----------------------------------------
Id.  |  FromUser  |  toUser  | message  |  
     |            |          |          |
3    |     3      |    1     |  hi      |
-----------------------------------------

As well for other users

1 answer

  • answered 2018-02-13 01:34 GaryL

    The query I came up with is a bit complex, so here it is in multiple steps:

    1) Get the “Other User” for the message matching the requested user.

    SET @id = 1;
    SELECT id, FromUser, ToUser, updateDate,
           IF(FromUser=@id, ToUser, FromUser) as OtherUser
      FROM user_msgs 
      WHERE @id=FromUser OR @id=ToUser;
    

    2) Wrap the above and get the last updateDate. (see below for user 'id' instead)

    SET @id = 1;
    SELECT OtherUser, max(updateDate) as updateDate FROM
       (
        SELECT id, FromUser, ToUser, updateDate,
              IF(FromUser=@id, ToUser, FromUser) as OtherUser
         FROM user_msgs 
         WHERE @id=FromUser OR @id=ToUser
       ) AS i1
     GROUP BY OtherUser;
    

    3) FULL QUERY HERE Finally wrap again to get the desired records.

    SET @id = 1;
    SELECT * FROM user_msgs O
      WHERE EXISTS
      (
       SELECT * FROM 
        (
         SELECT OtherUser, min(updateDate) as updateDate FROM
           (
             SELECT id, FromUser, ToUser, updateDate,
                  IF(FromUser=@id, ToUser, FromUser) as OtherUser
               FROM user_msgs 
               WHERE @id=FromUser OR @id=ToUser
            ) AS i1
           GROUP BY OtherUser
        ) i2
       WHERE O.updateDate = i2.updateDate AND
               (( O.FromUser=@id AND i2.OtherUser = O.ToUser) OR
                ( O.ToUser=@id AND i2.OtherUser = O.FromUser) )
      );
    

    Note: If you don’t have a last date "updateDate" field, and instead are using “id” (I wasn’t sure), then just replace all the “updateDate” field occurrences in the query with “id”.

    SQL Fiddle: link