如何用SQL/TypeORM查询聊天应用中指定双用户的DM会话

核心SQL语句 #

假设TypeORM自动生成的多对多关联表名为room_user（包含room_id和user_id两个外键字段），以下是满足需求的SQL：

SELECT r.*
FROM Room r
INNER JOIN room_user ru ON r.id = ru.room_id
WHERE r.type = 'dm'
  AND ru.user_id IN (?, ?) -- 替换为你的user1_id和user2_id实际值
GROUP BY r.id
HAVING COUNT(DISTINCT ru.user_id) = 2
   AND (SELECT COUNT(*) FROM room_user WHERE room_id = r.id) = 2;

逻辑说明 #

• 先通过WHERE r.type = 'dm'限定只查询单聊类型的房间；
• 关联中间表筛选出包含目标用户的房间；
• 按房间分组后，COUNT(DISTINCT ru.user_id) = 2确保两个目标用户都在该房间内；
• 子查询(SELECT COUNT(*) FROM room_user WHERE room_id = r.id) = 2保证房间恰好只有这两个用户，排除包含目标用户但还有其他成员的情况。

注意事项 #

如果TypeORM生成的中间表字段采用驼峰命名（比如roomId、userId），请对应调整SQL中的字段名。

内容的提问来源于stack exchange，提问作者Romain T-BIGOT