Counting number of articles within a category using CakePHP?

Question

Hi

I have a simple cakephp app with table "articles" that has a "cat_id" column to reference a "id" column of categories in a "cats" table.

What I would like to do is display all categories names, each followed with a count of the number of articles belonging to that category.

I know there is a find('count') function in cakephp, but that only working on one table/field. How do I link the two tables to get a list of category names and count of articles for each category for my view?

Thanks

Answer 1

it might be better to do this in the afterFind callback on the model but can be done in the controller aswell.

$cats - $this->Cat->find('all',array('recursive'=>-1));
foreach($cats as $key=>$cat){
  $cats[$key]['ArticleCount'] = $this->Cat->Article->find('count',array(
     'conditions'=>array('Article.cat_id'=>$cat['Cat']['id']))
  );
}

or see if this works

$cats = $this->Cat->find('all',array('contain'=>array(
          'Article'=>array('fields'=>array('COUNT(*) AS ArticleCount')
        )));

obviously you will need to add the containable behavior on the category model.

Answer 2

Thanks, this worked:

     $cats = $this->Article->Cat->find('all',array('recursive'=>-1));
 foreach($cats as $key=>$cat){
   $cats[$key]['ArticleCount'] = $this->Article->find('count',array(
      'conditions'=>array('Article.cat_id'=>$cat['Cat']['id']))
   );
 }

Answer 3

You could also use the countercache feature: http://book.cakephp.org/view/816/counterCache-Cache-your-count

Answer 4

You also might consider checking out counterCache to cache the count within a column value.

Answer 5

COme on! Use counterCache! In table "cats" create field article_count. In Model write

/app/models/cat.php
class Cat extends AppModel
{
   var $name = 'Cat';
   var $belongsTo = array('Article'=>array('counterCache'=>true));
}

That's all! every time you add/remove from articles, it writes to cats table count of articles. don't forget to include article_count into fields list