mysql - 同一张表上的多个 JOIN

Question

我正在 Laravel 上开发消息系统，我有下表:

用户

user_id | email | password | name | ...
---------------------------------------

聊天

chat_id | user1_id | user2_id
-----------------------------

留言

message_id | content | date | user_id | chat_id | ...
------------------------------------------------------

我想要做的是获取经过身份验证的用户与其他用户开始的所有聊天，按消息日期排序(从最新到最旧的消息)，以及有关用户的信息他正在说话。

我目前正在处理原始 SQL 请求，然后将其传递到 Laravel 中，这就是我尝试的方法，但它没有给出等待的结果:

SELECT user.user_id, user.name, user.surname
FROM user
JOIN chat a
ON user.user_id = a.user1_id
JOIN chat b
ON user.user_id = b.user2_id
WHERE b.chat_id IN
    (SELECT chat_id 
     FROM chat 
     WHERE user1_id = 1 OR user2_id = 1) 
AND user.user_id != 1  

----> (I'm testing with the user that has the ID #1)

如果有人可以提供帮助，我们将不胜感激。

Answer 1

您可以使用 Union 来获得两个结果，如下所示:

select a.user1_id, a.chat_id, user.fname, user.lname 
from user
join chat a on a.user2_id = user.user_id and a.user1_id = 1
union 
select b.user2_id, b.chat_id, user.fname, user.lname 
from user
join chat b on b.user1_id = user.user_id and user2_id = 1