php - 从多对多表中排序 mysqli

Question

我正在尝试使用 *amp 系统制作一个基于网络的内部消息系统，主要用于学习目的。我不知道这是否是一个微不足道的话题，但我遇到了困难，所以请多多包涵。

目标是列出按最后发送/接收的消息排序的所有联系人。目前没有排序，SQL 看起来像这样

$query = "SELECT username, user.id as user_id,  

(SELECT COUNT(message_read)  
FROM message_user  
WHERE message_read = 0 
AND sent_id = user_id  
AND receive_id = {$userId}) as unread  

FROM user  
WHERE user.id IN  
(SELECT contact_id FROM allowed_contact WHERE user_id = {$userId})   
;";

表的结构是:
user 表有一个id,
链接到具有 sent_id 和 receive_id 的 message_user 表，
message_user 有一个 message_id 对应于 message.id,
message 表有一个时间戳。

我希望这在 SQL 中完成，但如果它归结为 PHP，我将放弃求助于它。

Answer 1

这有效。

SELECT `u`.`id` AS user_id, username,
(SELECT COUNT(message_user.message_read)  
FROM message_user  
WHERE message_user.message_read = 0 
AND sent_id = user_id  
AND receive_id = {$userId}) as unread  

FROM `user` AS `u`
LEFT JOIN `message_user` AS `mu` 
ON 
    (CASE WHEN `u`.`id` != {$userId}
        THEN `u`.`id` = `mu`.`sent_id`
        WHEN `mu`.`sent_id` = {$userId} AND `mu`.`receive_id` = {$userId}
        THEN `u`.`id` = `mu`.`sent_id`
    END)
OR  
    (CASE WHEN `u`.`id` != {$userId}
        THEN `u`.`id` = `mu`.`receive_id`
    END)

LEFT JOIN `message` AS `m` ON `m`.`id` = `mu`.`message_id`

WHERE u.id IN  
(SELECT contact_id FROM allowed_contact WHERE user_id = {$userId})
GROUP BY u.id
ORDER BY MAX(`m`.`timestamp`) DESC;

This解决了我遇到的问题。

@Andreas 感谢您的时间和帮助。