Issue with order by and group by with two foreign keys in same table

Question

I need to develop chat application in php for this i have created two tables likes users and message. every user details will be stored in users table and every message will be stored in messages table.I have done storing part it is working fine. Now i need to display messages.So as per my requirement.

when any user logs into his portal he/she will be able to see latest messaged users list.And if he want to message any of other users ,he just clicks on there profile pic than a message panel will be opened .Untill here i completed everything.

But my issue is i need to display

latest messaged users list,
in this i need to show user first name, profile picture,last message, last message date.
And one more condition is i need to display the list like latest messaged user first.

I have tried in many ways but i got users list with first message i don't want like that i need last message for that user

My tables are

Users table

uid  | firstname | email           | mobile
---------------------------------------------
1    | kumar     | kumar@gmail.com | 9876543210
----------------------------------------------
2    | jack      | jack@gmail.com  | 8876543216
----------------------------------------------
3    | rams      | rams@gmail.com  | 7876543215
----------------------------------------------
4    | devid     | devid@gmail.com | 9876543220
----------------------------------------------
5    | joe       | joe@gmail.com   | 8876543212
----------------------------------------------

messages table

mid| from_id  | to_id | message            | created_at
----------------------------------------------------------------
1  |   1      |    2  |  hello jack        | 2017-02-03 09:00:52
----------------------------------------------------------------  
2  |   2      |    1  |  hi kumar          | 2017-02-03 09:10:30
----------------------------------------------------------------
3  |   2      |    3  |  ram where are you | 2017-02-03 09:15:02
----------------------------------------------------------------
4  |   3      |    2  |  at home           | 2017-02-03 09:35:20
----------------------------------------------------------------
5  |   1      |    2  |  hello how are you | 2017-02-03 09:42:55
----------------------------------------------------------------
6  |   4      |    2  |  good morning      | 2017-02-03 09:50:45 
----------------------------------------------------------------
8  |   1      |    3  |  hi                | 2017-02-03 09:54:22 
----------------------------------------------------------------
7  |   3      |    1  |  hello kumar       | 2017-02-03 09:58:38 
----------------------------------------------------------------

For example i have logged in as kumar(uid=1)

Expected output:

firstname | message           | mid | uid
-----------------------------------------
rams      | hello kumar         | 7   | 3
-----------------------------------------
jack      | hello how are you   | 5   | 2
-----------------------------------------

I have tried like this :

SELECT DISTINCT
`u`.`firstname`,
`u`.`profile_photo`,
`u`.`uid`,
`u2`.`firstname`,
`u2`.`profile_photo`,
`u2`.`uid`,
`message`,
`messages`.`created_at`,
`messages`.`from_id`,
`messages`.`to_id`,
`messages`.`mid` 
FROM
`messages`
INNER JOIN
`users` AS `u` ON `u`.`uid` = `messages`.`from_id`
INNER JOIN
`users` AS `u2` ON `u2`.`uid` = `messages`.`to_id`
WHERE
(from_id = 1 OR to_id = 1)
GROUP BY
`u`.`uid`,
`u2`.`uid`
ORDER BY
`messages`.`mid` DESC

But got output like this

firstname | message           | mid | uid
-----------------------------------------
jack      | hello jack        | 1   | 2
-----------------------------------------
rams      | hi                | 5   | 2
-----------------------------------------

Thanks in advance

You should do separate queries for messages FROM the user and TO the user, then combine them with UNION. — Barmar, Feb 07 '17 at 06:33
There are hundreds of SO questions about writing queries to find conversations. For some reason, you're not the first person trying to add messaging to their web site. — Barmar, Feb 07 '17 at 06:33
Search SO for `[mysql] conversation`: http://stackoverflow.com/search?q=%5Bmysql%5D+conversation — Barmar, Feb 07 '17 at 09:27
i have already checked but i did't get solutions if you have solution for this issue please send that link not like "[mysql] conversation". — Srinivas Rao, Feb 07 '17 at 09:48

mandar · Answer 1 · 2017-02-07T09:30:46.273

1

try this way

SELECT DISTINCT `u`.`firstname`,`u`.`profile_photo`, `u`.`uid`, `u2`.`firstname`,`u2`.`profile_photo`,`u2`.`uid`, `message`,`messages`.`created_at`, `messages`.`from_id`,`messages`.`to_id`,`messages`.`mid` 
FROM `messages`
INNER JOIN `users` AS `u` ON `u`.`uid` = `messages`.`from_id` 
INNER JOIN `users` AS `u2` ON `u2`.`uid` = `messages`.`to_id` 
WHERE (from_id = 1 OR to_id = 1) 
GROUP BY `u`.`uid`, `u2`.`uid` 
ORDER BY `messages`.`created_at` DESC

edited Feb 07 '17 at 09:30

answered Feb 07 '17 at 06:37

mandar

98
6

Thank you for update i have tried like this but same issue – Srinivas Rao Feb 07 '17 at 07:29
I have updated my answer instead of sorting it by mid you can use created_at column because i can see in your example the last two columns mid = 7 and mid = 8 are not sorted according to mid but are sorted by created_at column` – mandar Feb 07 '17 at 08:52
What's the difference between his query and this one? You have unmatched parentheses in the `WHERE` clause. – Barmar Feb 07 '17 at 09:26

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

It appears that you want to put messages in the same group if they're between the same two users, regardless of the direction. To do this, change your GROUP BY to:

GROUP BY GREATEST(u.uid, u2.uid), LEAST(u.uid, u2.uid)

Use this along with the solutions in SQL Select only rows with Max Value on a Column to get the first or last message in each conversation found using this grouping.

You should also give aliases to the columns from u and u2 in the SELECT clause, so you can distinguish the sender and receiver information in the result.

SELECT u.firstname AS sender_name, u.profile_photo AS sender_photo, ...

Or since one of the users is always kumar, you could just select only the information about the other user:

SELECT IF(from_id = 1, u2.firstname, u1.firstname) AS firstname,
       IF(from_id = 1, u2.profile_photo, u1.profile_photo) AS profile_photo,
       ...

edited May 23 '17 at 11:45

Community

1
1

answered Feb 07 '17 at 10:12

Barmar

741,623
53
500
612

GROUP BY GREATEST(u.uid, u2.uid), LEAST(u.uid, u2.uid) here we need not to order by uid we need order by mid – Srinivas Rao Feb 09 '17 at 14:38
ordering and grouping are separate operations – Barmar Feb 09 '17 at 17:16
yes but i got old output. unable to get expected output – Srinivas Rao Feb 10 '17 at 04:35
See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the first or last row in each group. Use that with the grouping solution I showed. – Barmar Feb 10 '17 at 06:22

Issue with order by and group by with two foreign keys in same table

2 Answers2