Return items with data intersections over a many-to-many join (See related posts)

If you have three tables, 'posts', 'tags', and a join table 'posts_tags', and you wish to find all posts which are all associated with a number of different tags, you can use this SQL trick:

   1  SELECT p.* FROM posts_tags pt, posts p, tags t WHERE pt.tag_id = t.id AND (t.name IN ('tag1', 'tag2', 'tag3')) AND p.id=pt.post_id GROUP BY p.id HAVING COUNT(p.id) = 3;

.. where 3 is the number of tags in total. In Ruby/Rails, if you have an array called 'tags' containing the tags, you could use this code:

   1  @posts = Post.find_by_sql ("SELECT p.* FROM posts_tags pt, posts p, tags t WHERE pt.tag_id = t.id AND (t.name = '" + tags.uniq.join ('\' OR t.name=\'') + "') AND p.id=pt.post_id GROUP BY p.id HAVING COUNT(p.id) = " + tags.uniq.length.to_s)

This is how Snippets itself works. You can also add a p.user_id check to the HAVING operator to only find posts with certain tags by a certain user.