时间戳

首页 > 解决方案 > MYSQL：带有详细信息和未读消息计数的朋友列表

mysql - MYSQL：带有详细信息和未读消息计数的朋友列表

问题描述

我正在开发聊天系统，并希望列出未读消息计数的朋友详细信息。

表结构如下

用户 

id
name

朋友们

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 的朋友，但我想要朋友列表中的未读消息计数（WHERE read_at = NULL）。我如何通过查询来实现它？

结果列就像

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

有谁能够帮我？问候和谢谢大家

标签： mysql

解决方案

你把这件事复杂化了。像这样的东西应该可以工作，尽管如果没有表格的示意图就很难说：

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

推荐阅读