连接多个表，保留NULL [英] Join multiple tables, keeping NULLs
问题描述
我有4个表user，posts，follows，notifications. posts表具有一个名为privacy的字段，其中值1表示任何人都可以看到"，值2表示只有朋友可以看到".
I have 4 tables user, posts, follows, notifications. The posts table has a field called privacy in which a value of 1 means "anyone can see" and 2 means "only friends can see".
我的notifications表如下所示:
id user_id tonotify_id notification post_id
1 2 3 --- 1
2 3 2 --- 2
3 2 4 --- 3
我的follows表如下所示:
id user_id tofollow_id status fstatus
1 1 2 1 1
2 1 3 1 1
字段fstatus的值为1表示他们也是朋友.
The field fstatus value of 1 means that they are also friends.
现在，我正在尝试获取由某个特定用户关注的人发出的所有通知(比如说一个ID为1的用户).假设1跟随用户2和3，我需要从上表中获取所有通知.但是，在此之前，我需要确保该帖子(由用户在tonotify_id列中发布)可用.也就是说，我需要检查隐私设置.我有以下代码:
Now I am trying to get all notifications sent out by people who a certain user is following (lets say a user with id 1). Assuming 1 is following user 2 and 3, I need to get all notifications from the above table. However, before this I need to make sure that the post (posted by the users in the tonotify_id column) is available. That is, I need to check the privacy setting. I have the following code:
SELECT
u.username AS sender,
ux.username AS receiver,
p.id
FROM notifications n
JOIN follows f ON (n.user_id = f.tofollow_id)
JOIN follows fr ON (n.tonotify_id = fr.tofollow_id)
JOIN user u ON (u.id = n.user_id)
JOIN user ux ON (ux.id = n.tonotify_id)
LEFT JOIN posts p ON (n.posts_id = p.id)
WHERE f.user_id = 1
AND fr.user_id = 1
AND f.status = 1
AND p.privacy = 1 OR (p.privacy = 2 AND fr.fstatus = 1)
ORDER BY n.id DESC
除了一个小故障，它似乎在工作.由于用户1没有跟随用户4，因此所有针对4的通知都不会出现，即使帖子是公开的也不会出现.我的猜测与JOIN follows fr ON (n.tonotify_id = fr.tofollow_id)有关，因为由于用户1没有关注4，所以没有匹配该联接的行.有解决此问题的建议吗?
It seems to be working except for one glitch. Since user 1 is not following user 4 all notifications aimed at 4, even if the posts are public don't show up. This my guess has to do with JOIN follows fr ON (n.tonotify_id = fr.tofollow_id) because, since the user 1 hasn't followed 4, there are no rows matching this join. Any suggestions to solving this?
推荐答案
我确实尝试过[外部联接]，但是输出是相同的.
I did try [the outer join], but the output is the same.
使用外部联接，然后在WHERE子句的相等性检查中使用外部"列之一时，会将外部联接转换为内部联接.这是因为您检查帖子私密性的条件要求帖子在那儿:
When you use an outer join, and then use one of the "outer" columns in an equality check in the WHERE clause, you convert your outer join to an inner join. This is because your condition that checks post's privacy requires the post to be there:
AND p.privacy = 1 OR (p.privacy = 2 AND fr.fstatus = 1)
当外部联接将要产生与没有帖子的通知相对应的行时，它将检查以上条件.由于该职位不在那里，因此p.privacy的评估结果为NULL，污染"了OR的两侧，最终使整个条件的评估结果为false.
When an outer join is about to produce a row that corresponds to a notification without a post, it would check the above condition. Since the post is not there, p.privacy would evaluate to NULL, "contaminating" both sides of the OR, and eventually making the whole condition evaluate to false.
将此条件移到联接的ON条件中将解决此问题:
Moving this condition into the ON condition of the join will fix the problem:
SELECT
u.username AS sender,
ux.username AS receiver,
p.id
FROM notifications n
JOIN follows f ON (n.user_id = f.tofollow_id)
JOIN follows fr ON (n.tonotify_id = fr.tofollow_id)
JOIN user u ON (u.id = n.user_id)
JOIN user ux ON (ux.id = n.tonotify_id)
LEFT JOIN posts p ON (n.posts_id = p.id)
AND (p.privacy = 1 OR (p.privacy = 2 AND fr.fstatus = 1))
WHERE f.user_id = 1
AND fr.user_id = 1
AND f.status = 1
ORDER BY n.id DESC
解决此问题的另一种方法是在OR中添加一个IS NULL条件，如下所示:
Another way to fix this would be adding an IS NULL condition to your OR, like this:
SELECT
u.username AS sender,
ux.username AS receiver,
p.id
FROM notifications n
JOIN follows f ON (n.user_id = f.tofollow_id)
JOIN follows fr ON (n.tonotify_id = fr.tofollow_id)
JOIN user u ON (u.id = n.user_id)
JOIN user ux ON (ux.id = n.tonotify_id)
LEFT JOIN posts p ON (n.posts_id = p.id)
WHERE f.user_id = 1
AND fr.user_id = 1
AND f.status = 1
AND (p.privacy IS NULL OR p.privacy = 1 OR (p.privacy = 2 AND fr.fstatus = 1))
ORDER BY n.id DESC
这篇关于连接多个表，保留NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
