DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world
Return Items With Data Intersections Over A Many-to-many Join
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:
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:
@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.