How to filter/restrict selected posts using an arbitrary number of multiple tags in MySQL

I'm building a site that has posts and each post can have zero or more tags. Users can select zero or more tags to block. If a tag is blocked it means that queries that select posts will not select any posts which are tagged with any of the user's blocked tags. I have been able to make a query that excludes posts with any specific tag, but when I try to add a clause that excludes posts with any of the user's blocked tags, it doesn't work. Not only are the posts with blocked tags still shown, but I get duplicated posts (one for each tag that the post is tagged with) and I do not get any posts that have no tags. How do I allow users to designate an arbitrary number of blocked tags and then filter queries of posts based on the tags the posts have?

Here are examples relevant tables, simplified to only show the relevant columns:

posts

id	title
1	Post A
2	Post B
3	Post C

tags

id	name
1	Tag 1
2	Tag 2
3	Tag 3

posts_tags

id	tag	post
1	1	1
2	2	1
3	3	2

blocked_tags

id	tag	user
1	1	user1
2	2	user1
3	3	user2

and my query looks something like this (there's a lot more in the real query, I'm just showing the basics and the relevant clause):

SELECT *
FROM posts
JOIN tags_posts ON tags_posts.post = posts.id
WHERE tags_posts.tag NOT IN
     (SELECT blocked_tags.tag
      FROM blocked_tags
      WHERE blocked_tags.user = :user)

And :user is replaced with the current user's id. Let's say for example the current user is user1 then based on the data in the table above, I'd expect this query to return the following:

id	title
2	Post B
3	Post C

and if the current user is user2 then I'd expect it to return the following:

id	title
1	Post A
2	Post B

But instead, no matter which user I am, I get the following:

id	title
1	Post A
1	Post A
2	Post B

What is the correct way to filter the posts so that they exclude any that are tagged with any of the user's blocked tags? Thank you.

Solution

You can use NOT EXISTS to check that none of the tags associated with a post are in a given users blocked list:

SELECT  *
FROM    posts AS p
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    tags_posts AS tp
                    INNER JOIN blocked_tags AS bt
                        ON bt.tag = tp.tag
            WHERE   bt.user = :user
            AND     tp.post = p.id
        );

Example on db<>fiddle

If you actually need to return the tags associated with the post, you can avoid a second join (one in the select and one in the not exists) by doing a similar thing with HAVING:

SELECT  p.id, 
        p.title,
        GROUP_CONCAT(t.name) AS tags
FROM    posts AS p
        LEFT JOIN tags_posts AS tp
            ON tp.post = p.id
        LEFT JOIN tags AS t
            ON t.id = tp.tag
        LEFT JOIN blocked_tags AS bt
            ON bt.tag = tp.tag
            AND bt.user = 'user2'
GROUP BY p.id, p.title
HAVING COUNT(bt.tag) = 0 /*No blocked tags*/

Example on db<>fiddle