For the folks I promised an article on how to implements tags in SQL. Sorry, I never finished it. I got sucked into some more esoteric problems, and never got back to writing down the basics. However, all is not lost. Peter has done a very nice job of writing about how he implemented tagging for his snippets code libraries.
Matching on data intersection across a many-to-many join – aka, how to find an object (e.g. a post or a recipe) with two or more tags (i.e. show my all posts tagged with
sql). For the lazy, skip to bottom for a solution. (but it’s worth reading through) This is what enables tag combos. (Also available from snippets)
Find items with similar (or as many as possible) relationships – for a ‘related posts’ box etc – aka, find other recipes tagged with similar tags to the recipe I’m currently looking at I’m currently looking at. In Recipes on Rails, if I’m looking at my recipe for hot chocolate (tags:
chocolate), use this query to also show tea and coffee. (both tagged
Find all many-to-many relationships which are tied to an arbitrary number of other many-to-many relationships – I’m looking at all recipes with the tags
hot, what are related tags I could use? How about
curry(my curry carrot soup is tagged
curry, not to mention
favorite), or maybe
sillywith which I’ve tagged my boiled water recipe.
After some futzing I came up with the same query that Peter did, but I was really hoping there was another solution, as I’ve been seeing some of the same worrying numbers using MySQL 4.1.x’s subqueries that Kevin is.
There you go, you no longer have any excuse for building a sub-par tagging system.