Return Items With Data Intersections Over A Many-to-many Join
Join the DZone community and get the full member experience.Join For Free
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.
Opinions expressed by DZone contributors are their own.