从多对多表中对mysqli进行排序

I'm trying to make a internal web based message system, with a *amp system, primarily for learning purposes. I don't know if this is a trivial topic, but I'm having difficulties so please bear with me.

我正在尝试使用* amp系统创建一个基于Web的内部消息系统,主要用于学习目的。我不知道这是否是一个微不足道的话题,但我遇到了困难所以请耐心等待。

The goal is to list all the contacts ordered by the last message sent / received. Currently without sorting it the SQL looks like this

目标是列出发送/接收的最后一条消息所订购的所有联系人。目前没有排序,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})   
;";

The structure of the tables are:
The user table has an id,
That links to the message_user table which has a sent_id and a receive_id,
The message_user has a message_id that corresponds to the message.id,
The message table has a timestamp.

表的结构是:用户表有一个id,它链接到message_user表,它有一个sent_id和一个receive_id,message_user有一个message_id,对应于message.id,消息表有一个时间戳。

I would like this to be done in SQL but if it comes down to PHP I resign to resort to that.

我希望这可以在SQL中完成,但如果归结为PHP,我会辞职以诉诸于此。

2 个解决方案

#1

This works.

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 broke down the problem I was having.

这打破了我遇到的问题。

@Andreas thanks for time and help.

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

#2

Use 2 LEFT JOIN with a DISTINCT (untested):

使用2 LEFT JOIN和DISTINCT(未经测试):

SELECT DISTINCT `u`.`id`
FROM `user` AS `u`
LEFT JOIN `message_user` AS `mu` ON `u`.`id` = `mu`.`sent_id` OR `u`.`id` = `mu`.`receive_id`
LEFT JOIN `message` AS `m` ON `m`.`id` = `mu`.`message_id`
ORDER BY `m`.`timestamp` DESC;

#1