How to make such a query with a search by tags?

Question

SQL

Good afternoon.
I am writing a post filter, and I ran into a big problem. You can filter posts by user, date, status, tags. The last one has a problem. I have an intermediate table postTags. It has the following columns:
id
post_id
tag_id I

filter for now like this:

SELECT p.*, u.name FROM posts p
LEFT JOIN users u ON u.id = p.user_id
LEFT JOIN postTags pt ON pt.post_id = p.id
WHERE p.user_id = 4
AND pt.tag_id IN (3,5,7)

Of course, I am shown posts that are present in at least one of the tags with id 3, 5, 7.
But it is necessary that the post MUST be with tags that have id 3, 5, 7. If at least one of these tags is missing, then The post is not eligible.

I dug through the documentation, but I didn’t find any analogues of IN ... I saw some example with having count, but I didn’t understand it at all ...

Answer 1

Solution 1 - with modification of the request for a certain set of parameters.
How many simultaneous tags should be present at the input, so many joins of the tag table will be.

SELECT p.*, u.name FROM posts p
JOIN users u ON u.id = p.user_id
JOIN postTags pt1 ON pt1.post_id = p.id and pt1.tag_id = 3
JOIN postTags pt2 ON pt2.post_id = p.id and pt2.tag_id = 5
JOIN postTags pt3 ON pt3.post_id = p.id and pt3.tag_id = 7
WHERE p.user_id = 4

PS: If a specific user is specified in where, then we join users completely (without left), so it will work faster.
Solution 2 - control the number of unique tags in the output.

Select a.*
from
(
SELECT p.*, u.name, count (distinct  pt1.tag_id) over (partition by p.id) unik_tag_count
 FROM posts p
JOIN users u ON u.id = p.user_id
JOIN postTags pt1 ON pt1.post_id = p.id
WHERE p.user_id = 4
and pt1.tag_id in (3, 5, 7)
) a
where  a.unik_tag_count = 3 -- уникальное кол-во тегов на один пост