php - 如何使用左连接选择涉及当前用户的对话

Question

以下查询选择对话参与者的总数，但列出所有相同的对话:

$id = 4;
$query = 'SELECT COUNT(1)'
       + ' FROM tbl_conversations AS a'
       + ' LEFT JOIN tbl_conversations_participants AS b'
       + ' ON a.id = b.conversation_id';

如果我添加以下内容:WHERE b.user_id = $id; 它会显示正确的对话数量，但会列出所有相同的对话。

对话表:

参与者表:

Answer 1

您可能正在查找参与特定对话的参与者数量。

SELECT 
COUNT(DISTINCT b.user_id) totalParticipants
FROM tbl_conversations AS a
INNER JOIN tbl_conversations_participants AS b
ON a.id = b.conversation_id
WHERE a.id = 4;

如果您只需要计数，那么以下查询就足够了[不需要涉及tbl_conversations]:

SELECT 
COUNT(DISTINCT user_id) totalParticipants
FROM tbl_conversations_participants 
WHERE conversation_id = 4;

如果您想了解每个对话的参与者总数:

SELECT
 a.id, 
 COUNT(DISTINCT b.user_id) totalParticipants
FROM tbl_conversations AS a
LEFT JOIN tbl_conversations_participants AS b
ON a.id = b.conversation_id
GROUP BY a.id