php - 同一个表中有两个外键的 order by 和 group by 问题

Question

我需要用 php 开发聊天应用程序，为此我创建了两个表，如用户和消息。每个用户详细信息都将存储在用户表中，每条消息都将存储在消息表中。我已经完成了存储部分，它工作正常。现在我需要显示消息。所以按照我的要求。

当任何用户登录他的门户时，他/她将能够看到最新的消息用户列表。如果他想给任何其他用户发消息，他只需点击那里的个人资料图片，然后消息面板就会打开。直到在这里，我完成了一切。

但我的问题是我需要显示

• 最新的消息用户列表，

• 在此我需要显示用户名字、头像、最后一条消息、最后一条消息日期。

• 还有一个条件是我需要首先显示最新消息用户列表。

我尝试了很多方法，但我得到的用户列表中包含第一条消息，我不想要这样，我需要该用户的最后一条消息

我的 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
----------------------------------------------

消息表

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 
----------------------------------------------------------------

例如我以 kumar(uid=1) 登录

预期输出:

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

我试过这样的:

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

但是得到这样的输出

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

提前致谢

Answer 1

试试这个

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