counterCache or counter_cache Behavior
I wrote this because I needed something simple for my recent project and CakePHP have not implemented the counterCache option yet. This is my first time writing a Behavior and largely based on http://groups.google.com/group/cake-php/msg/74508dad38d3d623 but it doesn't work for me though. You may wish to learn more about why counterCache is useful at http://groups.google.com/group/cake-php/msg/971ced72abe96b03
This is just a quick solution for me, but I thought of just sharing it so anyone could use and improve on it. There are currently no configuration for the behavior yet though because I don't need it. Feel free to bring up a new copy of the code or suggest something so I probably could improve upon mine. ;)
Usage is very simple for now and really "convention over configuration". It expects the `comments` table to have a `post_id`, and the `posts` table to have a `comments_count` column.
Download code
Usage is very simple for now and really "convention over configuration". It expects the `comments` table to have a `post_id`, and the `posts` table to have a `comments_count` column.
Model Class:
Download code
<?php
class Comment extends AppModel {
var $name = 'Comment';
var $actsAs = 'CounterCache';
var $belongsTo = array('Post');
}
?>
Download code
<?php
/**
* CounterCacheBehavior
*
* @author Derick Ng aka dericknwq
* @version 2007-05-01
*/
class CounterCacheBehavior extends ModelBehavior {
var $foreignTableIDs = array();
function setup(&$model, $config = array()) {
}
function afterSave(&$model, $created) {
if ($created) {
foreach ($model->belongsTo as $assocKey => $assocData) {
$assocModel =& $model->{$assocData['className']};
$field = Inflector::tableize($model->name) . '_count';
if (!empty($model->data[$model->name][$assocData['foreignKey']]) && $assocModel->hasField($field)) {
$this->foreignTableIDs[$assocData['className']] = $model->data[$model->name][$assocData['foreignKey']];
$count = $model->findCount(array($model->name . '.' . $assocData['foreignKey'] => $this->foreignTableIDs[$assocData['className']]));
$assocModel->id = $this->foreignTableIDs[$assocData['className']];
$assocModel->save(array($field => $count), false, array($field));
}
}
}
}
function beforeDelete(&$model) {
foreach ($model->belongsTo as $assocKey => $assocData) {
$this->foreignTableIDs[$assocData['className']] = $model->field($assocData['foreignKey']);
}
return true;
}
function afterDelete(&$model) {
foreach ($model->belongsTo as $assocKey => $assocData) {
$assocModel =& $model->{$assocData['className']};
$field = Inflector::tableize($model->name) . '_count';
if ($assocModel->hasField($field)) {
$count = $model->findCount(array($model->name . '.' . $assocData['foreignKey'] => $this->foreignTableIDs[$assocData['className']]));
$assocModel->id = $this->foreignTableIDs[$assocData['className']];
$assocModel->save(array($field => $count), false, array($field));
}
}
}
}
?>
Comments
Comment
1 Excellent bit of code
This should definitely be added to the core functionality.
Comment
2 although I have hit an issue....
The 3 models that I am having an issue with are my Comment, User and Post models. When a user add's a comment to a post, I want to increase the comments_count on both Post and User. In my standard set up, the Post model comments_count does increase, but the User one throws out an SQL error:
Query: SELECT COUNT(*) AS count FROM `comments` AS `Comment` LEFT JOIN `posts` AS `Post` ON (`Comment`.`post_id` = `Post`.`id`) LEFT JOIN `users` AS `User` ON (`Comment`.`user_id` = `User`.`id`) WHERE `user_id` = 1
Warning (512): SQL Error: 1052: Column 'user_id' in where clause is ambiguous [CORE/cake/libs/model/datasources/dbo_source.php, line 479]
From what I can tell, it's not trying to increase the users overall comments_count, it's trying to do it in relation to the post_id. If I unbind the models, and only use the User model, it works fine. I've also tried reducing the recursion, but again no joy with it.
For example, if I unbind to use just the User model the SQL looks like this:
SELECT COUNT(*) AS count FROM `comments` AS `Comment` LEFT JOIN `users` AS `User` ON (`Comment`.`user_id` = `User`.`id`) WHERE `user_id` = 1
Comment
3 try this
Try:
Query: SELECT COUNT(*) AS count FROM `comments` AS `Comment` LEFT JOIN `posts` AS `Post` ON (`Comment`.`post_id` = `Post`.`id`) LEFT JOIN `users` AS `User` ON (`Comment`.`user_id` = `User`.`id`) WHERE `User.user_id` = 1
Comment
4 table names and queries
maybe it's just me, but why not set these conventions:
1) if A belongs to B then B should have a field named A_count, ok that's already done.
2) whenever we save or delete, we have one query per associated model, using the model->query() method.
3) We don't need to know the value of a field to increment/decrement it, since
we can write something like:
$model->query("UPDATE $model->table SET comments_count=comments_count+1 WHERE post_id='$id')
See what I mean? that's only one query!
What do you think?
P.S. Ok, okay... I hear you say: "don't talk, code!"
Promise, i'll try to bring my vision of things in a new behavior. :)
Comment
5 One suggestion...
Since a model can belong to another model with a specified foreignKey and conditions, you should also incorporate any conditions when doing a the findCount calls. Here's what I suggest you change in afterSave and afterDelete, so that any declared conditions in the model association as used. Change...
$count = $model->findCount(array($model->name . '.' . $assocData['foreignKey'] => $this->foreignTableIDs[$assocData['className']]));
to...
$conditions = am(array($model->name . '.' . $assocData['foreignKey'] => $this->foreignTableIDs[$assocData['className']]), $assocData['conditions']);
$count = $model->findCount($conditions);
Otherwise, congrats on the great behavior!
Comment
6 count