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.
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:
<?php
class Comment extends AppModel {
var $name = 'Comment';
var $actsAs = 'CounterCache';
var $belongsTo = array('Post');
}
?>
<?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));
}
}
}
}
?>
That count isn't going to change so using counterCache cuts out a lot of reporting overhead. Say for example, that you're wanting to get some information regarding totals in about 6 different tables and then you only want to see the results that have numbers within a certain range.
Without the counterCache, you have to run multiple count() queries using joins and HAVING clauses on the results of the counts...every time you want to see that information. If you just have the counterCache, the information is already there.
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!
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. :)
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
This should definitely be added to the core functionality.
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