I'm sure I can relate to a lot of people when I say that we sometimes run into problems that drive us absolutely bonkers until we solve them. Such is the day-to-day for us code nerds. Usually we can solve them relatively quickly, in a few hours, but some of them plague our existence until we figure it out. And then there's the kind that you kind of want to figure out, can't, and then they rest in the back of your head for a while. I solved one such problem just the other day, and I wanted to share it with you in case anyone has been losing sleep over the same thing.

So here's the scenario: my blog has a tag system. Who knows if it's actually useful, but I wanted to build it to keep my posts organized, and it's pretty much a proof of concept kind of thing. Not only that, but my del.icio.us bookmarks are organized into a tag system as well. The problem I wanted to solve was to be able to search my blogs and/or del.icio.us bookmarks by tag, using an 'all' or 'any' specifier so that I could, say, enter a bunch of tags separated by a space or a comma, and find all posts/links that have any of the tags I entered, or that have all of the tags I entered.

The Tables

I organized all of my tags into a table for themselves so that I can associate them to any other set of data using a many to many relationship. My tags table looks like this:

CREATE TABLE `tags` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `idx_name` (`name`)
)

Then my join table, the table that relates tags to my blogs, looks like this:

CREATE TABLE `blogs_tags` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `blog_id` int(11) default NULL,
  `tag_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_blog_id` (`blog_id`),
  KEY `idx_tag_id` (`tag_id`)
)

And my blogs table looks something like this:

CREATE TABLE `blogs` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `slug` varchar(255) default NULL,
  `entry` longtext NOT NULL,
  `posted` datetime NOT NULL,
  `created` datetime default NULL,
  `modified` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_slug` (`slug`)
)

The Magical Query

Once you have some blog entries, some tags, and some tagged blogs, maybe you want to search them. So the solution I finally found was a query that looks like the following. This is to search for blogs that have both 'Javascript' and 'MooTools' tags associated with them:

SELECT
    GROUP_CONCAT(tags.name) AS tag_list,
    blogs.id,
    blogs.name
FROM
    blogs
    LEFT JOIN blogs_tags ON (blogs.id = blogs_tags.blog_id)
    LEFT JOIN tags ON (blogs_tags.tag_id = tags.id)
GROUP BY
    blogs.id
HAVING
    FIND_IN_SET('Javascript', tag_list)
    AND FIND_IN_SET('MooTools', tag_list)

I had put together most of the query a while ago, but finally figured out the GROUP_CONCAT function and the 'HAVING' part. It's definitely a beefy query. Not only is it a 3-table JOIN, but it also uses a temporary table, and I believe the 'HAVING' part is evaluated after the entire table is scanned anyway. But after clocking this query, along with breaking the query into 2 and timing that, this way takes about 2/3 the times that the 2 queries together take. The above is an 'all' style query, to switch to 'any', just with the 'AND' to an 'OR' and you're set.

And I can easily apply this to my delicious links by sucking them into a local 'links' table by placing a cURL request to the delicious API like so:

<?php

$url = 'https://{username}:{password}@api.del.icio.us/v1/posts/all/';

$curl = curl_init($url);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
$result = curl_exec($curl);
curl_close($curl);

?>

Be careful when testing though, them Delicious folks are pretty conservative with their request throttling. Just cache the result and you're solid. I then parse the resulting XML, make a bunch of inserts into 'links' and 'links_tags' tables and I can search away. The original idea was to make a little app for myself on my site where I could search my links. That is until the Delicious plugin implemented basically the same thing.

And that's it! Go nuts with your tags searching, or any other many-to-many relationships. Please ask questions if you have any, and let me know if you might know of any other spiffier methods.

P.S. The tattoo is finally done! I will post some pictures once it heals up. Yipee!