查看此sql Fiddle,了解我的问题
http://sqlfiddle.com/#!9/cf31d3/1的简化版本
我有2个表 – 聊天消息和聊天收件人,如下所示：
示例ChatMessages数据：
示例ChatRecipients数据：
基本上我只想查询包含一组用户ID的消息 – 例如,仅显示在Bob,Susan和Chelsea之间交换的消息.如果我使用用户ID(1,2,3)启动一个新的聊天窗口,那么获取仅涉及这3个人的消息的最佳方式是什么？
这是我当前查询的简化版本(不会产生正确的结果)：
SELECT
cm.message_id as 'message_id',cm.from_id as 'from_id',(SELECT u.user_fname as 'fname' from Users u where u.user_id = cm.from_id) as 'firstName',(SELECT u.user_lname as 'lname' from Users u where u.user_id = cm.from_id) as 'lastName',cm.chat_text as 'chat_text'
FROM
ChatMessages cm
INNER JOIN
ChatRecipients cr
ON
cm.message_id = cr.message_id
INNER JOIN
Users u
ON
cm.from_id = u.user_id
WHERE
cm.from_id in ('1','2','3')
AND
cr.user_id in ('1','3')
我知道使用“IN”运算符对于这种情况不正确,但我有点卡住了.感谢愿意提供帮助的人！
编辑：
我的示例输出返回包含任何上述用户ID的每一行数据,如下所示：
我的目标是将输出限制为只有我测试的每个用户ID与message_id相关联的消息.例如,如果message_id 32是FROM user_id 7并且TO user_id是11& 3,我想检索那条记录.相反,如果message_id 33是FROM user_id 7并且是user_id(s)11& 4我不想检索该记录.
这里的问题是你的消息必须是：
>来自用户1并且收到2,3,… N.
>来自用户2并由1,… N接收
> ……
>来自用户N并且收到1,… N-1
并且您需要一个能够合理缩放的查询,即,对于每个收件人或类似的东西,没有单个JOIN.
让我们从“从”部分开始.
SELECT m.* FROM ChatMessages AS m
WHERE from_id IN ($users)
现在我需要知道这些消息有哪些收件人.
SELECT m.* FROM ChatMessages AS m
JOIN ChatRecipients AS r ON (m.message_id = r.message_id)
WHERE from_id IN ($users)
收件人可能是好是坏,我对它们的数量感兴趣.所以
SELECT m.*,COUNT(*) AS total,SUM(IF(user_id IN ($users),1,0)) AS good
FROM ChatMessages AS m
JOIN ChatRecipients AS r ON (m.message_id = r.message_id)
WHERE from_id IN ($users)
GROUP BY m.message_id;
最后
如果消息在我的[1 … N]个用户之间,则可以接受,这意味着
它有N-1个接收者,其中N-1个很好.
SELECT m.*,SUM(IF(user_id IN ({$users}),0) AS good
FROM ChatMessages AS m
JOIN ChatRecipients AS r ON (m.message_id = r.message_id)
WHERE from_id IN ({$users})
GROUP BY m.message_id
HAVING total = good AND good = {$n}
测试
在这种情况下有三个id,我们有$users = 1,3和$n = 2
SELECT m.*,SUM(IF(user_id IN (1,3),0)) AS good
FROM ChatMessages AS m
JOIN ChatRecipients AS r ON (m.message_id = r.message_id)
WHERE from_id IN (1,3)
GROUP BY m.message_id
HAVING total = good AND good = 2
message_id from_id chat_text
1 2 Message from Susan to Bob and Chelsea
2 3 Message from Chelsea to Bob and Susan
3 1 Message from Bob to Chelsea and Susan
