如何查询包含未读消息数量的聊天列表（PostgreSQL）

表结构 #

PostgreSQL中存储聊天记录的表request_messages结构如下：

id (PRIMARY KEY)
professional_id (FOREIGN KEY)
request_id (FOREIGN KEY)
message
created_by (FOREIGN KEY)
created_at
read_at

需求 #

需要获取指定请求（request_id = $1）的聊天列表，同时为每个聊天条目添加未读消息数量（即read_at IS NULL的消息条数）。

原查询语句 #

当前用于获取聊天列表的SQL：

SELECT
rm.professional_id,
CONCAT(u.first_name, ' ', u.last_name) AS professional_full_name,
FROM request_messages AS rm 
LEFT JOIN users AS u ON (rm.professional_id = u.id) 
WHERE rm.request_id = $1 
GROUP BY rm.professional_id, professional_full_name

修改后的查询语句 #

可以通过两种方式添加未读消息统计列，以下是可行的写法：

写法一：使用COUNT + CASE表达式 #

SELECT
rm.professional_id,
CONCAT(u.first_name, ' ', u.last_name) AS professional_full_name,
COUNT(CASE WHEN rm.read_at IS NULL THEN 1 END) AS unread_count
FROM request_messages AS rm 
LEFT JOIN users AS u ON (rm.professional_id = u.id) 
WHERE rm.request_id = $1 
GROUP BY rm.professional_id, professional_full_name

写法二：使用SUM + 布尔值转换 #

PostgreSQL会自动将布尔值转换为整数（true=1，false=0），可以简化统计逻辑：

SELECT
rm.professional_id,
CONCAT(u.first_name, ' ', u.last_name) AS professional_full_name,
SUM((rm.read_at IS NULL)::INT) AS unread_count
FROM request_messages AS rm 
LEFT JOIN users AS u ON (rm.professional_id = u.id) 
WHERE rm.request_id = $1 
GROUP BY rm.professional_id, professional_full_name

说明 #

两种写法均能精准统计每个professional_id对应的未读消息数，仅对read_at为NULL的记录进行计数；分组条件保持不变，确保每个聊天条目按专业人员维度聚合。

内容的提问来源于stack exchange，提问作者Francesco Vibert