Counter Cache behavior for HABTM relations

by danaki
CounterCache is a cool feature introduced in CakePHP 1.2, but I found that it doesn't work for HABTM (hasAndBelongsToMany) relations and AFAIK only planned in 1.3 (See ticket #5214). Using this bakery article: [url]http://bakery.cakephp.org/articles/view/countercache-or-counter_cache-behavior[/url]), I wrote my own CounterCache behavior for HABTM relations. This is my first article for Bakery and English isn't my mother tongue, so please be indulgent to my mistakes.
For those who is new to CakePHP or doesn't know about what CounterCache is, please read this Cookbook chapter first: http://book.cakephp.org/view/816/counterCache-Cache-your-count.

I will explain how to use my CounterCacheHabtmBehavior on a simple example. Imagine we want to build a blog with tags assigned to each article. There can be many tags assigned to many articles and each tag is unique. The database definition will look as follows:


CREATE TABLE articles (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR(100), body TEXT, tag_count INT DEFAULT 0);
CREATE TABLE tags (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), article_count INT DEFAULT 0);
CREATE TABLE articles_tags (article_id INT, tag_id INT);

As you can see, articles and tags are linked via HABTM association, and articles_tags is the join table. Notice also that article has the column called tag_count and tags has article_count, those we will count how many tags each article has and to how many articles each tag belongs.

On this step you should bake your blog application, but I assume that you've done it already.

Download the code below and save as models/behaviors/counter_cache_habtm.php

Model Class:

<?php 
/**
 * CounterCacheHabtmBehavior - add counter cache support for HABTM relations
 *
 * Based on CounterCacheBehavior by Derick Ng aka dericknwq
 *
 * @see http://bakery.cakephp.org/articles/view/counter-cache-behavior-for-habtm-relations
 * @author Yuri Pimenov aka Danaki (http://blog.gate.lv)
 * @version 2009-05-28
 */
class CounterCacheHabtmBehavior extends ModelBehavior {
    
/**
     * Array to store intermediate results
     *
     * @var array
     * @access public
     */
    
var $foreignTableIDs = array();

    
/**
     * For each HABTM association using given id, find related foreign ids
     * that represent in the join table. Save results to $foreignTableIDs array.
     *
     * @param mixed $model
     * @access private
     * @return void
     */
    
function findForeignIDs(&$model) {
        foreach (
$model->hasAndBelongsToMany as $assocKey => $assocData) {
            
$assocModel =& $model->{$assocData['className']};
            
$field Inflector::underscore($model->name).'_count';

            if (
$assocModel->hasField($field)) {       
                
$joinModel =& $model->{$assocData['with']};
               
                
$joinIDs $joinModel->find('all', array(
                    
'fields' => array($assocData['associationForeignKey']),
                    
'conditions' => array($assocData['foreignKey'] => $model->id),
                    
'group' => $assocData['associationForeignKey']
                ));

                
$this->foreignTableIDs[$assocData['className']] = array_keys(
                    
Set::combine($joinIDs'{n}.'.$assocData['with'].'.'.$assocData['associationForeignKey'])
                );
            }
        }
    }

    
/**
     * For each HABTM association, using ids from $foreignTableIDs array find
     * counts and update counter cache field in the associated table
     *
     * @param mixed $model
     * @access private
     * @return void
     */
    
function updateCounters(&$model) {
        foreach (
$model->hasAndBelongsToMany as $assocKey => $assocData)
            if (isset(
$this->foreignTableIDs[$assocData['className']])
                && 
$this->foreignTableIDs[$assocData['className']]) {

                
$assocModel =& $model->{$assocData['className']};
                
$joinModel =& $model->{$assocData['with']};

                
$field Inflector::underscore($model->name).'_count';

                if (
$assocModel->hasField($field)) {               
                    
$saveArr = array();
   
                    
// in case of delete $rawCounts array may be empty -- update associated model anyway
                    
foreach ($this->foreignTableIDs[$assocData['className']] as $assocId)
                        
$saveArr[$assocId] = array('id' => $assocId$field => 0);

                    
// if 'unique' set to false - update counter cache with the number of only unique pairs
                    
$rawCounts $joinModel->find('all', array(
                        
'fields' => array(
                            
$assocData['associationForeignKey'],
                            (
$assocData['unique'] ? 'COUNT(*)' 'COUNT(DISTINCT '.$assocData['associationForeignKey'].','.$assocData['foreignKey'].')')
                            .
' AS count'),
                        
'conditions' => array(
                            
$assocData['associationForeignKey'] => $this->foreignTableIDs[$assocData['className']]
                        ),
                        
'group' => $assocData['associationForeignKey']
                    ));
                                       
                    
$counts Set::combine($rawCounts'{n}.'.$assocData['with'].'.'.$assocData['associationForeignKey'], '{n}.0.count');
   
                    
// override $saveArr with count() data
                    
foreach ($counts as $assocId => $count)
                        
$saveArr[$assocId] = array('id' => $assocId$field => $count);
       
                    
$assocModel->saveAll($saveArr, array(
                        
'validate' => false,
                        
'fieldList' => array($field),
                        
'callbacks' => false
                    
));
                }
            }       
    }

    
/**
     * On update fill $foreignTableIDs for each HABTM association from user form data
     *
     * @param mixed $model
     * @access public
     * @return boolean
     */   
    
function beforeSave(&$model) { 
        if (! empty(
$model->id)) {
            
// this is an update, we handle creates in afterSave(), this saves us some CPU cycles           
            
$this->findForeignIDs($model);

            foreach (
$model->hasAndBelongsToMany as $assocKey => $assocData)
                if (isset(
$model->data[$assocData['className']])
                    && isset(
$model->data[$assocData['className']][$assocData['className']])
                    && 
is_array($model->data[$assocData['className']][$assocData['className']])) {

                    
$this->foreignTableIDs[$assocData['className']] = Set::merge(
                        isset(
$this->foreignTableIDs[$assocData['className']]) ? $this->foreignTableIDs[$assocData['className']] : array(),
                        
$model->data[$assocData['className']][$assocData['className']]
                    );
                }
        }

        return 
true;       
    }
   
    
/**
     * Update counter cache after all data saved
     *
     * @param mixed $model
     * @param boolean $created
     * @access public
     * @return void
     */       
    
function afterSave(&$model$created) {
        if (
$created) {
            foreach (
$model->hasAndBelongsToMany as $assocKey => $assocData) {
                
$assocModel =& $model->{$assocData['className']};
                
$field Inflector::underscore($model->name).'_count';     

                if (
$assocModel->hasField($field))
                    
$this->foreignTableIDs[$assocData['className']] = $model->data[$assocData['className']][$assocData['className']];
            }
        }

        
$this->updateCounters($model);
       
        foreach (
$model->hasAndBelongsToMany as $assocKey => $assocData) {
            
$field Inflector::underscore($assocKey).'_count';
           
            if (
$model->hasField($field)) {
                
$joinModel =& $model->{$assocData['with']};

                
// if 'unique' set to false - update counter cache with the number of only unique pairs
                
$count $joinModel->field(
                    (
$assocData['unique'] ? 'COUNT(*)' 'COUNT(DISTINCT '.$assocData['associationForeignKey'].')').' AS count',
                    array(
$assocData['foreignKey'] => $model->id)
                );

                
$model->saveField($field$count, array(
                    
'validate' => false,
                    
'callbacks' => false
                
));
            }
        }
       
        
$this->foreignTableIDs = array();
    }

    
/**
     * Fill $foreignTableIDs array just before deletion
     *
     * @param mixed $model
     * @access public
     * @return boolean
     */   
    
function beforeDelete(&$model) {
        
$this->findForeignIDs($model);

        return 
true;
    }

    
/**
     * Update counter cache after deletion
     *
     * @param mixed $model
     * @access public
     * @return void
     */     
    
function afterDelete(&$model) {
        
$this->updateCounters($model);
       
        
$this->foreignTableIDs = array();       
    }
}
?>

Unlike hasMany association, in HABTM both tables are linked to each other and none of them are master or slave. So you have to add the following line

    var $actsAs = array('CounterCacheHabtm');

to both article and tag models. Models must now look like:

Model Class:

<?php 
class Article extends AppModel {
    var 
$name 'Article';
    var 
$actsAs = array('CounterCacheHabtm');

    var 
$hasAndBelongsToMany = array(
        
'Tag' => array('className' => 'Tag',
            
'joinTable' => 'articles_tags',
            
'foreignKey' => 'article_id',
            
'associationForeignKey' => 'tag_id',
            
'unique' => true
        
)
    );
}
?>

Model Class:

<?php 
class Tag extends AppModel {
    var 
$name 'Tag';
    var 
$actsAs = array('CounterCacheHabtm');

    var 
$hasAndBelongsToMany = array(
        
'Article' => array('className' => 'Article',
            
'joinTable' => 'articles_tags',
            
'foreignKey' => 'tag_id',
            
'associationForeignKey' => 'article_id',
            
'unique' => true
        
)
    );
}
?>

That's all, so simple. Now you can create/update/delete articles and tags and behavior will count related rows and update each model.

Important notice: if you want to have CounterCache enabled for only one of the models, say you don't want to cache number of articles in the tag model, just don't create the appropriate _count column (in our example you can safely drop article_count in tags table) and the behavior won't try to update it. But even in this case remember that you must have $actsAs in both models in order the code to work properly.

Report

More on Behaviors

Advertising

Comments

  • raimondreichert posted on 05/01/11 06:28:41 PM
    Your counter cache behaivour works just fine.

    Unless I try to combine it with saving extra fields on the habtm association as in http://nuts-and-bolts-of-cakephp.com/2008/09/24/saving-extra-fields-in-the-join-table-for-habtm-models/. That is, if articles_tags had additional fields, and they were saved using the dynamic model bind method described in the blog entry. Model::saveAll() then throws an “undefined index PostsTag”.

    Do you have any idea why that does not work? I'd greatly appreciate because I need both behaviours (I want to have publications and authors, and publications_authors needs an extra field for the order of the authors).

  • Cairlinn posted on 12/11/09 10:38:03 AM
    I tried this behaviour an it seemes to work, but not all counts are updated, if I save a record. It updates the count for all associations that are deleted, but I have to save a second time to update the counts for the new associations as well.
  • neilc posted on 11/15/09 06:48:53 PM
    It's not really rigorously tested yet, but I needed counterScope functionality, so wrote an alternative to, but inspired by this behavior (thanks Yuri). If you need counterScope functionality too, check it out at: http://gist.github.com/235599
    Clone it, test it, enhance it, let me know how it goes.
  • webbedit posted on 08/14/09 06:45:14 AM
    When looping through the models hasAndBelongsToMany associations and creating an the assocModel I had to change the

    $assocModel =& $model->{$assocData['className']};
    to

    $assocModel =& $model->{$assocKey};
    This then allows for associations where the association name differs from the className, for example

    var $hasAndBelongsToMany = array(
      'Tag' => array(
        'joinTable' => 'vacancies_tags'
      ),
      'Category' => array(
        'className' => 'OptionListItem',
        'joinTable' => 'vacancies_categories',
        'with' => 'VacanciesCategory',
        'foreignKey' => 'vacancy_id',
        'associationForeignKey' => 'category_id',
        'unique' => true,
        'conditions' => array('Category.option_list_id'=>11),
        'fields' => array('id', 'value')
      ),
      'Region' => array(
        'className' => 'OptionListItem',
        'joinTable' => 'vacancies_regions',
        'with' => 'VacanciesRegion',
        'foreignKey' => 'vacancy_id',
        'associationForeignKey' => 'region_id',
        'unique' => true,
        'conditions' => array('Region.option_list_id'=>3),
        'fields' => array('id', 'value')
      )
    );
  • webbedit posted on 08/10/09 04:33:20 PM
    Great behaviour, unfortunately it has taken me hours to implement. The issues I have identified are fixed as follows:

    1) function updateCounters() ...
    $counts = Set::combine($rawCounts, '{n}.'.$assocData['with'].'.'.$assocData['associationForeignKey'], '{n}.0.count'); 
    needs to be changed to
    $counts = Set::combine($rawCounts, '{n}.'.$assocData['with'].'.'.$assocData['associationForeignKey'], '{n}.'.$assocData['with'].'.count');
    because model::find now returns count fields as part of the main model array rather than creating a 0 array.

    2) function afterSave() ...
    $count = $joinModel->field(
      ($assocData['unique'] ? 'COUNT(*)' : 'COUNT(DISTINCT '.$assocData['associationForeignKey'].')').' AS count',
      array($assocData['foreignKey'] => $model->id)
    );
    is not passing back a value as model->field() fails to see the value when a field is named 'COUNT(*) AS count' ... added the following two lines into /cake/lib/models/model.php after line #1043 to resolve this
    ...
    $nameExplode = explode(' AS ', $name);
    if (isset($nameExplode[1])) $name = $nameExplode[1];
    ...

    3) The behaviour was making my controller's saveAll() command return false even though all the data is saved and all counts are updated. There were no validationErrors being passed to the view which made it hard to diagnose. Eventually I nailed it down to the $assocModel->saveAll() command within updateCounters() which I replaced with a loop running saveField() commands instead. I am guessing it was still running some sort of validation or callbacks the other way, but I'm too tired to go hunting for the full reason, it is now working!!

    Started using behaviour with CakePHP Version 1.2.3.8166, then upgraded to 1.2.4.8284 to see if any recent updates would resolve, but was in same boat. Anyone else have the above issues?
    • sasquall posted on 10/20/10 05:06:33 AM
      [quote] 3) The behaviour was making my controller's saveAll() command return false even though all the data is saved and all counts are updated. There were no validationErrors being passed to the view which made it hard to diagnose. Eventually I nailed it down to the $assocModel->saveAll() command within updateCounters() which I replaced with a loop running saveField() commands instead. I am guessing it was still running some sort of validation or callbacks the other way, but I'm too tired to go hunting for the full reason, it is now working!!
      [end quote]
      I had same problem too. Helped adding $options['atomic'] => true in $assocModel->saveAll(), and now it should be ( in method updateCounters() ):


                          $assocModel->saveAll($saveArr, array(
                              'validate' => false,
                              'fieldList' => array($field),
                              'callbacks' => false,
                  'atomic'=> false
                          ));
  • punkelias posted on 06/10/09 04:18:53 PM
    Thanks a lot for this model behavior, I registered just to thank you :)
  • danaki posted on 05/28/09 01:14:27 PM
    This article is also available in Russian:
    http://blog.gate.lv/2009/05/19/cakephp-countercache-habtm/
  • m3nt0r posted on 05/28/09 10:38:42 AM
    Since you are using tablelize, the fieldname is in plural form

    Inflector::tableize($assocKey).'_count';

    I suggest to change this to singular form, so that it lines up nicely with other counter cache fields. It's more natural that way.

    Inflector::underscore($assocKey).'_count';

    - Kjell
    • danaki posted on 05/28/09 01:05:49 PM
      Since you are using tablelize, the fieldname is in plural form

      Inflector::tableize($assocKey).'_count';

      I suggest to change this to singular form, so that it lines up nicely with other counter cache fields. It's more natural that way.

      Inflector::underscore($assocKey).'_count';

      - Kjell

      Thanks. I've updated the article.
  • elventails posted on 05/27/09 09:03:38 AM
    I was just looking for this!
    How would one add a condition for the counter? e.g. only count articles if is_published = 1?
    Thanks!
    • danaki posted on 05/27/09 11:48:17 AM
      I was just looking for this!
      How would one add a condition for the counter? e.g. only count articles if is_published = 1?
      Thanks!

      There're could be two choices, depends what do you mean:

      * The behavior counts rows of the join table, e.g. articles_tags and conditions could be applied to this table if you had an extra field for example 'is_active' which means "is the association active?" in the join table.
      * The second case is more difficult. You could have additional conditions in both models Tag and Article, for example you want to count tags for only active articles or count articles for only administrator approved tags. In this case count(*) must be done using join of each model depending on conditions and this leads to slow down and bad design.

      Imagine if one wants to have count of tags of only approved articles and count of articles that have approved tags? Fortunately the second case can be "emulated" using first case, e.g. if you have unapproved article with assigned tags to it, just set "is_active" to "0" in the appropriate join table records, but you should handle this by yourself and that requires more coding from your side.

      If you agree, I can easily implement conditions for the first case and update the behavior.
      • senseBOP posted on 06/11/09 05:36:19 PM
        I was just looking for this!
        How would one add a condition for the counter? e.g. only count articles if is_published = 1?
        Thanks!

        There're could be two choices, depends what do you mean:

        * The behavior counts rows of the join table, e.g. articles_tags and conditions could be applied to this table if you had an extra field for example 'is_active' which means "is the association active?" in the join table.
        * The second case is more difficult. You could have additional conditions in both models Tag and Article, for example you want to count tags for only active articles or count articles for only administrator approved tags. In this case count(*) must be done using join of each model depending on conditions and this leads to slow down and bad design.

        Imagine if one wants to have count of tags of only approved articles and count of articles that have approved tags? Fortunately the second case can be "emulated" using first case, e.g. if you have unapproved article with assigned tags to it, just set "is_active" to "0" in the appropriate join table records, but you should handle this by yourself and that requires more coding from your side.

        If you agree, I can easily implement conditions for the first case and update the behavior.

        I think what he means is that he would prefer to use this the same way Cake's CounterCache behavior works. Meaning you set the behavior in the association, and optionally include a condition under which the counter should add/subtract to the total count. Say you have a Groups model and a Users model where each HABTM to the other.

        You want your groups to count how many users that are not banned from the site belong to each group. With counterCache, you'd use counterScope and set it to array('User.banned' => 0) and that's it. My guess is that elven is looking for a similar behavior in your counterCacheHabtm.

        So what he wants, basically, is this:

        Model Class:

        <?php 
        var $hasAndBelongsToMany = array(
            
        'Group' => array(
                
        'className' => 'Group',
                
        'joinTable' => 'groups_users',
                
        'foreignKey' => 'user_id',
                
        'associationForeignKey' => 'group_id',
                
        'conditions' => '',
                
        'fields' => '',
                
        'order' => '',
                
        'counterCacheHabtm' => true,
                
        'counterScopeHabtm' => array('User.banned' => 0),
            ),
        );
        ?>

        Personally, I think making this behavior work in as similar as possible a way to the original counterCache behavior is a very good idea, and I don't think it should take a whole lot of extra coding to do.
  • brightball posted on 05/20/09 12:34:18 PM
    I'm eager to try this out as I'm making extensive use of counter cache in a system that I'm building, auto-detecting _count columns for quick reporting.

    Thank you!
  • ADmad posted on 05/19/09 07:55:30 AM

    Since your behavior doesn't use any php5 only constructs I have removed the "private" keyword from your code. It was inconsistent to use use php5 keywords in behavior and php4 keywords (var) in your examples.


    Since cake supports both php 4 and 5 we like to keep code compatible with both as far as possible.

  • alkemann posted on 05/15/09 08:10:05 AM
    If you need help, please look for us in the irc channel #cakephp on the Freenode server.
  • ADmad posted on 05/14/09 02:21:57 PM
    You are not resetting the "foreignTableIDs" to empty array after completing the operation. This could potentially lead to problems if the behavior is invoked again by another model in the same request, as a single behavior object is shared by all models.
  • alkemann posted on 05/14/09 02:08:57 PM
    Thank you for your contribution. Before I can publish this, please reread the guidelines for bakery ( http://bakery.cakephp.org/pages/guidelines ) and make sure you are following the rules. Specifically, I need you to add doc blocks to your methods and remove out documented code.

    Also I was wondering if it might not be a good idea to skip the find('count') when 'unique' => true, since then you can just count the number of id's you have in the dataset you are saving?
login to post a comment.