Select posts by the most identical tags?

Question

SQL

Post <-> Tag manyToMany. So there is a Pivot table in which post_id + tag_id lie.
In recommendations, display posts that have the most identical tags with the current one.
That's how I understand

SELECT *
FROM posts 
INNER JOIN tags
WHERE posts.tags IN [1,5,8,12,56,4,44] // take posts which hase most alike tags
COUNT(posts.tags.id)
ORDER BY posts.tags.id

This is a rough draft, in a doctrine 2.6, symfony 4.1, pgsql 9.6 project.
It doesn’t work for me, because IN takes all posts where at least 1 has such a tag, but at least 55% are the same, I don’t understand how to do it yet. Most likely, you still need to group somehow and do a subquery, but then O (n) will be long since there are more joins than in the example and the sample size is many times larger. It might be better to split it into several queries.

Answer 1

SELECT p.uuid, count(tg.tag_uuid) as TagCount
FROM episode_details p
         INNER JOIN episodes_tags tg
                    ON p.uuid = tg.episode_uuid
         INNER JOIN anime_tag t
                    ON tg.tag_uuid = t.uuid
WHERE t.uuid in (1, 54, 32)
GROUP BY p.uuid
ORDER BY TagCount DESC
limit 3

https://stackoverflow.com/questions/3876251/need-h...