Tag clouds are a great way to allow users to navigate/search through your site but if you don’t design your application correctly from the ground up they can become a serious nightmare.
So - here’s my solution for creating a scalable tagging system in CakePHP (using MySQL in this instance but the theory applies to any other DB… yes… even you PostGRE’ers)
My example is a cookbook of recipes.
Most people that attempt a tagging system the first time around just go for a HABTM relationship - A recipe has and belongs to many tags. So - you have you “recipes”, “tags”, and “recipes_tags” tables… all you have to do to find your tags is:
And to find what tags go with what recipes (assuming you setup your models correctly) is:
-
id(int)
-
tag(varchar)
-
count(int)
-
created(datetime)
-
modified(datetime)
I pretty much always add fields for “created” and “modified” and set them to datetime - Cake fills them in automatically and it can be useful sometimes to have that history (you never know when a client wants to see something organized by the date they were added to the system - kinda sucks not to capture that info). Also - always, always, always create an “id” field that’s a unique, auto-incrementing, primary key for EVERY table… don’t call it something silly like uid or user or anything stupid like that. It’s just best practices. Period.
But, I digress;
So - your “recipes” table should look something like this:
-
id(int)
-
title(varchar)
-
description(text)
-
ingredients(text)
-
method(text)
-
tags(text)
-
created(datetime)
-
modified(datetime)
Notice that the “tags” field here is a text field - this is where we are going to store the tags, as written, by the user as one big block of text.
So - whenever a user creates a recipe we’re going to save all of the pertinent data to the database and then run a function called generate_tags:
-
-
function generate_tags($id=null)
-
{
-
//get recipes by $id
-
$recipe = $this->Recipe->findById($id);
-
-
//get all tags in the db
-
$tagsOld = $this->Tag->find(‘all’);
-
-
-
$tagsOldArrayValues =
array();
-
-
//loop thru tags in db and create an array like so: $tag = array (’id’=>$id,’count’=>$count)
-
foreach($tagsOld as $key => $values)
-
{
-
$tagsOldArray[$values[‘Tag’][‘tag’]] =
array(‘id’=>
$values[‘Tag’][‘id’],
‘count’=>
$values[‘Tag’][‘count’]) ;
-
}
-
-
-
foreach($tagsArray as $tag)
-
{
-
$tags[] = $tag;
-
}
-
-
-
-
-
-
foreach($countArray as $tag => $count)
-
{
-
if(in_array($tag,
$tagsOldArrayValues) ==
true)
-
{
-
$this->Tag->create();
-
$this->Tag->query(‘INSERT INTO `tags` (`tag`) VALUES (\’‘.$tag.‘\’) ON DUPLICATE KEY UPDATE `count` = `count`+1′);
-
} else {
-
$this->Tag->create();
-
$this->Tag->saveField(‘tag’,$tag);
-
$this->Tag->saveField(‘count’,$count);
-
}
-
}
-
}
-
What’s going on here? Well - first we find the recipe that you’re editing (or just inserted). Then we get all of the tags in the db. We foreach through the $tagsOldArray to format the array so that you have a nested array for each tag that contains the tag’s id and current count. We then explode the $tagsArray (the tags that are stored as plain text for the recipe at hand). We loop through the $tagsArray and then count the values of the keys. We then use array_keys to return the keys of $tagsOldArray which is used to determine whether or not a tag is already in the database. If it is in the array we execute the following query:
-
$this->Tag->query(‘INSERT INTO `tags` (`tag`) VALUES (\’`.$tag.`\’) ON DUPLICATE KEY UPDATE `count` = `count`+1′);
This updates the count of the tag value by 1. If the tag is in the array we insert the tag and count.
There’s probably a much more elegant way to do all of this - maybe one of these days I’ll pair this code down (I threw it together tonight for a project - and looking at it now I can see some obvious redundancy… but alas there’s not enough time in the world to optimize code and actually launch a website…).
But the theory is sound…
Now it’s your turn to play along - we have to remember to do is decrement the `Tag`.`count` for all tags associated to a recipe if a user deletes that recipe… and we have to update the counts for any tags a user edits when they edit a recipe… Not as big a deal as it sounds…
My next post will focus on the view for a tag cloud using this setup.
