Home How can I can all unique user whom I send message and receive message
Reply: 1

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

Abhishek Agarwal
1#
Abhishek Agarwal Published in 2018-02-13 01:34:43Z

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

GaryL
2#
GaryL Reply to 2018-02-15 06:06:07Z

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.411374 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO