Thanks!

As for the how it is done, that requires that you first know my table structure...

The tags table is pretty simple and looks like this:

#
# create new tags table
#
CREATE TABLE `tags` (                     \
 `id` int(11) NOT NULL auto_increment,    \
 `user_id` int(11) NOT NULL default '0',  \
 `tag` varchar(25) NOT NULL default '',   \
  root` int(11) NOT NULL default '0',     \
 `type` char(1) default NULL,             \
  PRIMARY KEY (`id`, `type`, `root` ),    \
  KEY `id` (`user_id`),                   \
  KEY `tag` (`tag`),                      \
  KEY `root` (`root`)                     \
 );

Here the fields are as follows:

• id - The ID of this tag entry. (Not really used.)
• user_id - The ID of the user who added this tag.
• tag - The text of the tag itself.
• root - The root node upon which this tag has been applied
• type - The type of tag.

As you know tags can be applied to articles, polls, and weblog entries. Each of these things is a different type. So for example:

• A tag on article 1: root=1, type='a'.
• A tag on poll 1: root=1, type='p'.
• A tag on a weblog entry: root=x, type='w'.

(In the dating site there are more tagging types; "i" for image, "p" for person, "l" for location, "k" for kink ..)

So to find a related tag what we want to do is find tags with the same root and type as one with the given text. This is done with a self-join, and the actual query looks like this:

  SELECT DISTINCT(a.tag) FROM tags AS a \
    JOIN tags b \
      ON b.root=a.root \
        AND a.type=b.type WHERE b.tag=?

This gives a nice list of all tags applied to the same object - and which are therefore related.

In terms of code the complete change can be found in this changeset.

Steve