我有一个聊天系统。有 3 张表:
data_chats
- 保存聊天本身的 ID。您可以在此处将聊天标记为已删除。
data_chat_parties
- 持有聊天中包含的成员或团队 ID 以及聊天权限、邀请者等
data_chat_messages
- 保存聊天的实际消息
通过我的查询,我尝试从与请求此信息的一方(即当前登录的用户)相关的data_chat_parties
获取信息,但也获取聊天中的聊天方总数。
SELECT
data_chats.id AS chat,
data_chats_parties.*,
COUNT(data_chats_parties.id) AS total_parties,
data_chats_messages.created AS last_message_created,
data_chats_messages.author AS last_message_author,
data_chats_messages.author_type AS last_message_author_type,
data_chats_messages.message AS last_message
FROM data_chats
LEFT JOIN data_chats_parties ON data_chats_parties.chat=data_chats.id
LEFT JOIN data_chats_messages ON data_chats_messages.chat=data_chats.id AND data_chats_messages.active=1
WHERE
data_chats.active=1 AND
data_chats_parties.member=1 AND
data_chats_parties.status >= 1
GROUP BY data_chats_parties.chat
ORDER BY last_message_created DESC
这一切都工作正常,除了total_chat_parties
总是返回 1,大概是因为它只匹配 data_chats_parties.member=1
的记录。我将如何获取特定于此用户的群记录,但同时获取此聊天的群总数?
您应该使用相关查询:
SELECT data_chats.id AS chat,
(SELECT COUNT(data_chats_parties.id) FROM data_chats_parties
WHERE data_chats_parties.chat = data_chats.id) AS total_parties,
data_chats_messages.created AS last_message_created,
data_chats_messages.author AS last_message_author,
data_chats_messages.author_type AS last_message_author_type,
data_chats_messages.message AS last_message
FROM data_chats
LEFT JOIN data_chats_messages
ON data_chats_messages.chat = data_chats.id
AND data_chats_messages.active = 1
AND data_chats_parties.member = 1
AND data_chats_parties.status >= 1
WHERE data_chats.active = 1
ORDER BY last_message_created DESC
另一件事是WHERE
子句上的条件,您可以在WHERE
子句中过滤LEFT JOIN
的RIGHT
表,这些条件应仅在ON
子句中指定。
您还可以按RIGHT
表中的列进行分组 - 根本不建议这样做!使用内部联接,或按其他字段分组。
select 语句中使用子查询来提供所需的计数。
(select COUNT(data_chats_parties.id) from data_chats_parties where data_chats_parties.chat=data_chats.id) AS total_parties,
您也可以删除该行
LEFT JOIN data_chats_parties ON data_chats_parties.chat=data_chats.id
希望我输入正确=)