For our photoblog, at http://cheesecake-photoblog.org/demo/tags/, we use something like following query
SELECT tags.*, COUNT(photos_tags.tag_id) AS totalPhotos FROM tags LEFT JOIN photos_tags ON tags.id = photos_tags.tag_id GROUP BY tags.id ORDER BY tags.tag ASC to generate tag cloud Hope this helps 2009/1/17 RichardAtHome <[email protected]> > > Given the following Model relations: > > article <-- HABTM (articles_tags) --> tag > > How do I fetch back a list of Tags with the count of associated > Articles? > > I've tried (in the Tag Model): > > $data = $this->find("all", array( > "fields"=>array( > "Tag.id", > "Tag.name", > "COUNT(Article.id) AS tag_count" > ), > "conditions"=>array( > "Article.published" => 1 > ), > "group" => "Tag.id" > )); > > But that doesn't generate the HABTM join in the SQL: > > SQL Error: 1054: Unknown column 'Article.id' in 'field list' > SELECT `Tag`.`id`, `Tag`.`name`, COUNT(`Article`.`id`) AS tag_count > FROM `tags` AS `Tag` WHERE `Article`.`published` = 1 GROUP BY > `Tag`.`id` > > Thanks in advance :-) > > > -- Amit http://amitrb.wordpress.com/ http://coppermine-gallery.net/ http://cheesecake-photoblog.org/ http://www.sanisoft.com/blog/author/amitbadkas --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
