finding the posts related by tags with one specific post in mysql

Question

I have this tables: Posts (post_is, title and text), Tags (tag_id, tag), Post_tag_nn (id, tag_id, post_id). I want for a specific post having for example 4 tags all the posts with those tags, then all the posts with any three of those tags, then all posts with any two of those tags and so on. How can i build a SQL query for this purpose (in php it seems like a backtracking problem=all the subsets of a given set).

Answer 1

If you're going to be pulling down every post with even a single one of the tags regardless, you might be best off just running a single query per tag to pull all of the posts with that tag, and then generating the sets yourself.

Answer 2

Something like:

select t.id, t.tag_id, p.post_id, p.title, p.text
  from post_tag_nn as t, posts p
  where p.id = t.post_id
  order by t.id

And then do the group in your code. You could of course do two different queries, one where you figure out the order and count of your tags and then one where you pull back the post for each tag.

Answer 3

Have a query to find the tags of the current post, something like

SELECT tag_id
FROM Post_tag_nn
WHERE post_id = $post_id;

Then using those tag id's, this query should return you the id's of posts with 4,3,2,... matching tags:

SELECT post_id, COUNT(post_id) AS tag_count
FROM Post_tag_nn
WHERE tag_id IN ($array_of_tag_ids)
GROUP BY post_id
ORDER BY tag_count DESC;