Counter Cache behavior for HABTM relations
This article is also available in the following languages:
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: http://bakery.cakephp.org/articles/view/countercache-or-counter_cache-behavior), 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:
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
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
to both article and tag models. Models must now look like:
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.
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.
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).
Clone it, test it, enhance it, let me know how it goes.
$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')
)
);
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(
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($assocData['unique'] ? 'COUNT(*)' : 'COUNT(DISTINCT '.$assocData['associationForeignKey'].')').' AS count',
array($assocData['foreignKey'] => $model->id)
);
...
$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?
[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
));
http://blog.gate.lv/2009/05/19/cakephp-countercache-habtm/
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.
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:
<?phpvar $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.
Thank you!
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.
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?
Comments are closed for articles over a year old