Related tags, search by tags in Cheesecake-Photoblog

An often repeated question on the CakePHP Group relates to filtering hasAndBelongsToMany associations or finding related hasAndBelongsToMany associations. To give a real life example and make it more clear - In Cheesecake-Photoblog a photo has and belongs to many tags. Simple tasks like finding all tags that belong to a photo or all photos that belong to a tag is handled easily by CakePHP but there are two common requirements of our tagging system

#1 Finding all photos which belong to a combination of tags, eg: all photos which have the tags kanha and tiger
#2 Finding related tags - all other tags which belong to photos returned for a particular tag

And I am sure these are common requirements of any hasAndBelongsToMany relationship... I have always believed that while a framework can do a lot when it comes to automating SQLs there will always be times when you need to write your own SQL and these two problems were apt for writing a bit of custom SQL rather than creating models on join tables etc.

To solve the #1 we have the following method in the Photo model

PLAIN TEXT
PHP:
  1. function findAllByTags($tags = array(), $limit = 50, $page = 1, $criteria = null) {
  2.         if (count($tags) <= 0) {
  3.             return false;
  4.         }
  5.  
  6.         if (!empty($criteria)) {
  7.             $criteria = ' AND '.$criteria;
  8.         }
  9.  
  10.         $prefix = $this->tablePrefix;
  11.         $offset = $limit * ($page-1);
  12.         $photos = $this->query("SELECT Photo.id,Photo.filename,Photo.title,Photo.created, COUNT(DISTINCT tags.tag) AS uniques
  13.                                 FROM {$prefix}photos Photo, {$prefix}photos_tags photos_tags, {$prefix}tags tags
  14.                                 WHERE Photo.id = photos_tags.photo_id
  15.                                   AND tags.id = photos_tags.tag_id
  16.                                   AND tags.tag IN ('".implode("', '", $tags)."') $criteria
  17.                                 GROUP BY photos_tags.photo_id
  18.                                 HAVING uniques = '".count($tags)."'
  19.                                 ORDER BY Photo.created DESC
  20.                                 LIMIT $offset, $limit");
  21.         return $photos;
  22.     }

and for #2 we have the following in the Tag model

PLAIN TEXT
PHP:
  1. function findRelatedTags($tags=array()){
  2.         $prefix = $this->tablePrefix;
  3.         $tagsCount = count($tags);
  4.         if($tagsCount> 0){
  5.              $tagsString = "'".implode("','",$tags)."'";
  6.              $query = "SELECT Tags.*, COUNT(pt.photo_id) AS count
  7.                          FROM {$prefix}photos_tags pt, {$prefix}tags Tags
  8.                          WHERE pt.photo_id IN (
  9.                                SELECT pt.photo_id FROM {$prefix}photos_tags pt, {$prefix}tags Tags
  10.                                      WHERE pt.tag_id = Tags.id
  11.                                         AND Tags.tag IN ({$tagsString})
  12.                                         GROUP BY pt.photo_id
  13.                                         HAVING COUNT(pt.photo_id)={$tagsCount})
  14.                          AND Tags.tag NOT IN ({$tagsString})
  15.                          AND Tags.id = pt.tag_id
  16.                          GROUP BY pt.tag_id
  17.                          ORDER BY count DESC";                           
  18.             $relatedTags = $this->query($query);
  19.             return $relatedTags;
  20.         }
  21.     }

The usage of the function and the code itself is pretty self explanatory anyone can easily adapt it use with their own HABTM associations, however there is room for improvement - this code can be made into a behavior and thus be more generic and reusable. If anybody reading this does accomplish it do drop a line here.....

About this entry

You’re currently reading “ Related tags, search by tags in Cheesecake-Photoblog ,” an entry on SANIsoft – PHP for E Biz

Published:
7.18.07 / 12:39pm
Category:
CakePHP, Cheesecake, MySQL
Author:
Tarique Sani
RSS for this Author