如何使用“已阻止”值的联接表来过滤用户字段将为 1、0 或 NULL

Question

我目前有两个表：

用户

id

等...

连接

user_id_1

user_id_2

被阻止

*其中 user_id_1 是关注用户，user_id_2 是关注用户（在模型中） Twitter 的）*

---

我目前正在修改我的内部消息系统，以允许所有用户向所有其他用户发送消息，唯一的限制是，如果用户 A 向用户 B 发送消息，但用户 B 已阻止用户 A，则该消息将不会通过。

考虑到这一点，请看一下这个查询：

SELECT u.id, IFNULL(c.blocked, 0) AS blocked
FROM cms_users u
    LEFT JOIN cms_connections c ON u.id=c.user_id_2 AND c.user_id_1=71
WHERE u.id IN (62, 65, 89, 90)

这里的想法是用户 71 正在尝试向用户 62、65、89 和 90 发送消息。用户 71 正在关注 62、65 和 89，但不关注 90。但是，用户 89 已阻止用户 71。这意味着连接表上的条目如下所示：

user_id_1       user_id_2       blocked
71              62              0
71              65              0
71              89              1

当我运行此查询时，我得到以下结果：

user_id_1       blocked
62              0
65              0
89              1
90              0

这正是我所期望的，但是如果我将 WHERE 子句更改为以下内容像这样：

WHERE u.id IN (62, 65, 89, 90) AND blocked=0

然后我得到这个：

user_id_1       blocked
62              0
65              0

这让我感到困惑，因为我期待三行：上面的两行和用户 90 的一行。我还尝试使用 GROUP BY 进行操作code>HAVINGblocked!=1 和 HAVINGblocked=0 它们都会产生相同的结果。我还尝试将创建的列的名称更改为 block 或 herpderp 只是为了检查与我的 JOIN 是否存在冲突，但这产生了相同的结果。

很想知道你们能想出什么:-)

Answer 1

更改为 IFNULL(c.blocked, 0)

SELECT u.id, 
       Ifnull(c.blocked, 0) AS blocked 
FROM   cms_users u 
       LEFT JOIN cms_connections c 
         ON u.id = c.user_id_2 
            AND c.user_id_1 = 71 
WHERE  u.id IN ( 62, 65, 89, 90 ) 
       AND Ifnull(c.blocked, 0) = 0 

则可以改进此查询

SELECT u.id, 
       Ifnull(c.blocked, 0) AS blocked 
FROM   cms_users u 
       LEFT JOIN cms_connections c 
         ON c.user_id_1 = 71  
            AND u.id = c.user_id_2 
       JOIN (SELECT 62 AS uu 
             UNION ALL 
             SELECT 65 AS uu 
             UNION ALL 
             SELECT 89 AS uu 
             UNION ALL 
             SELECT 90 AS uu) d 
         ON u.id = d.uu 
WHERE  Ifnull(c.blocked, 0) = 0 

如果重写为You need Indexs on

• (cms_users.id)
• (cms_connections.user_id_1,cms_connections.user_id_2,cms_connections.user_id_2.blocked)，

Answer 2

尝试： HAVING IFNULL(c.blocked,0) = 0