Speeding up count queries with counterCache

Today while working in a CakePHP application, I needed to hide some records based on the number of associated records they had. I basically needed to get a count of associated records. Fortunately, CakePHP has a feature called counterCache that makes this really easy. It saves the record count in the database and updates the count any time you do a save or delete. The details can be found here:  http://book.cakephp.org/view/490/counterCache-Cache-your-count

Basically you do the following:

  1. Create an INT field on the parent table called <childmodelname>_count
  2. In the $belongsTo array of the child model, add “‘counterCache’ => true” to the parent array
  3. Enjoy!

This works great for new installations. However, if you want to add it to an existing site, you’ll need to run through all the records and save the current count. Here is a sample method for doing just that.

[code=’php’]
function admin_update_counters() {
$this->Category->recursive = 0;
$categories = $this->Category->find(‘all’);
foreach ($categories as $category) {
$question_count = $this->Category->Question->find(‘count’, array(‘conditions’ => array(‘Question.category_id’ => $category[‘Category’][‘id’])));
if ($question_count) {
$this->Category->create();
$this->Category->id = $category[‘Category’][‘id’];
$this->Category->saveField(‘question_count’, $question_count);
}
}

echo “Counters updated”; exit;
}
[/code]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.