MYSQL:包含详细信息和未读消息计数的好友列表

Question

我正在开发聊天系统，想要列出好友详细信息以及未读消息数。

表格结构如下

用户

id
name

friend

id
sender_id
recipient_id

消息

id
from_id
to_id
read_at

使用以下查询获取用户好友的详细数据

(SELECT U.id, U.name 
   FROM users U LEFT JOIN friends F 
     ON U.id = F.recipient_id 
  WHERE F.sender_id = 2) 
UNION 
(SELECT U.id, U.name 
   FROM users U LEFT JOIN friends F 
     ON U.id = F.sender_id 
  WHERE F.recipient_id = 2) 
ORDER BY name ASC

查询给了我完美的结果，返回用户 2 的 friend ，但我想要未读消息计数(WHERE read_at = NULL)和 friend 列表。我怎样才能通过查询实现它？

结果列就像

+----+-------+---------------+
| id | name  | message_count |
+----+-------+---------------+
|  1 | One   |             1 |
|  3 | Two   |             4 |
|  4 | Three |            10 |
+----+-------+---------------+

我尝试过以下查询，但没有成功

(SELECT U.id, U.name, COUNT(M.id) as message_count 
   FROM users U LEFT JOIN friends F 
     ON U.id = F.recipient_id LEFT OUTER JOIN messages M 
     ON  M.from_id = U.id 
    AND M.read_at IS NULL 
  WHERE F.sender_id = 2) 
UNION 
(SELECT U.id, U.name, COUNT(M.id) as message_count 
   FROM users U LEFT JOIN friends F 
     ON U.id = F.sender_id LEFT OUTER JOIN messages M 
     ON M.from_id = U.id 
    AND M.read_at IS NULL 
  WHERE F.recipient_id = 2) 
ORDER BY name ASC

有人可以帮我吗？问候并谢谢大家

Answer 1

你把这件事搞得太复杂了。像这样的东西应该可以工作，尽管如果没有表格的原理图很难判断:

SELECT U.id, U.name, COUNT(M.id) FROM users U 
INNER JOIN friends F 
ON (U.id = F.recipient_id AND F.sender_id = 2) 
   OR (F.recipient_id = 2 AND U.id = F.sender_id)
LEFT JOIN messages M
ON M.to_id = 2 AND M.from_id = U.id
WHERE M.read_at IS NULL
GROUP BY M.from_id
ORDER BY name ASC