postgreSQL-在多对多关系中进行筛选

Question

我在多对多关系中遇到了一个正确的过滤查询问题，我认为最好通过示例来理解：

我有四个表：Post、PostTag、Tag和User，其中PostTag是Posts和Tags之间的联接表。标记是特定于User的-它们只属于一个用户，其他用户不应该访问。

我想获取Post及其Tag的列表，但只获取属于已登录User的Tag。

SELECT *
FROM post
LEFT JOIN post_tag ON post.id = post_tag.post_id
LEFT JOIN tag ON post_tag.tag_id = tag.id;

上面的查询返回所有的Posts，但它也返回所有的Users标记，这是不需要的。我的第一反应是把它放在哪里查询：

SELECT *
FROM post
LEFT JOIN post_tag ON post.id = post_tag.post_id
LEFT JOIN tag ON post_tag.tag_id = tag.id
WHERE tag.user_id = :user_id;

但上面的查询基本上将LEFT联接转换为INNER联接，并且不会选择没有标记的帖子。所以我试着把它放入JOIN条件：

SELECT *
FROM post
LEFT JOIN post_tag ON post.id = post_tag.post_id
LEFT JOIN tag ON post_tag.tag_id = tag.id
AND tag.user_id = :user_id;

上面确实返回了所有只带有登录用户标签的帖子，这很好，但我仍然有来自其他用户的所有post_tag，这并不理想。所以我找到了一种方法，用一个简单的WHERE来排除那些：

SELECT *
FROM post
LEFT JOIN post_tag ON post.id = post_tag.post_id
LEFT JOIN tag ON post_tag.tag_id = tag.id
AND tag.user_id = :user_id
WHERE NOT (post_tag.id IS NOT NULL AND tag.id IS NULL)

上面的查询似乎有效，但我不确定它是否是正确和最简单的解决方案。

Answer 1

是时候使用一种鲜为人知的SQL构造了，我甚至不记得这种类型的联接叫什么。基本上你这样做：

SELECT * 
FROM post
LEFT OUTER
JOIN post_tag
INNER
JOIN tag
ON tag.id = post_tag.tag_id -- these join conditions apply to tag - post_tag
AND tag.user_id = :user_id
ON post_tag.post_id = post.id -- these apply to post_tag to post

奇怪的是，INNER JOIN直接位于LEFT OUTER JOIN之后，ON子句以相反的顺序(基本上是先进先出(位于其后。这样做的结果是，它试图左联接post标签，但只有那些内部联接到标签的标签，我们通过user_id进行过滤。因此，post_tag联接仍然是外部的，并且将返回没有标记的posts，但如果它确实有任何标记，则它需要是用户的标记之一。

这是一个关于db fiddle 的工作演示

Answer 2

替代方法，避免结果集中的post_tag连接表。(我想您只对post和tag中的字段感兴趣，这些字段已经包含post_id和tag_id列，尽管它们都命名为id(：

---

SELECT *
FROM post p
LEFT JOIN tag t 
ON t.user_id = :user_id
AND EXISTS (
SELECT *
FROM post_tag pt 
WHERE pt.post_id = p.id
AND  pt.tag_id = t.id
)
;