SQL - Filtering in many to many relationships-CodePudding

I have an issue coming up with the correct query for filtering in a many-to-many relationship, I think it's best understood through example:

I have four tables: Post, PostTag, Tag, and User, where PostTag is the join table between Posts and Tags. The tags are User-specific - they belong to one user only and shouldn't be accessible by others.

I want to fetch the list of Posts with their Tags, but fetch only the Tags belonging to the logged in User.

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

Above query returns all the Posts, but it also returns all of the Users tags, which is unwanted. My first instinct was to put it in a where query:

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;

But the above query basically turns the LEFT joins into INNER joins, and Posts without Tags do not get selected. So I tried putting it into JOIN condition:

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;

The above does return all posts with only logged in user's tags, which is great, but I still have all the post_tags from other users which isn't ideal. So I found a way for excluding those with a simple 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)

The above query seems to work, but I'm not sure if it's the right and the simplest solution.

CodePudding user response：

It's time for one of the lesser known SQL constructs, I don't even remember what this type of join is called. Basically you do this:

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

The weird thing is the INNER JOIN comes directly after LEFT OUTER JOIN, and the ON clauses come after that in reversed order (basically first in first out). What that gets you is it tries to left join the post tags, but only the ones that inner join to tag where we filter by user_id. So the post_tag join is still left outer and will return posts without tags, but if it does have any tag, it needs to be one of the user's tags.

Here's a working demo on db fiddle