Counting Users' Votes with a CakePHP Sum Cache

Tweaking Cake's inbuilt counter cache functionality is a handy way of tracking user-submitted votes

Tagged with Web Development and CakePHP
Posted on 6/6/09 by Paul Herron

CakePHP's counter cache functionality is a handy way of keeping track of how many associated records something has. If I wanted to know the number of comments an article has, for example, I could do the following in my model association:

<?php

class Comment extends AppModel {

  

  var $actsAs = array('CounterCache');

  

  var $belongsTo = array(

      'Article' => array(
                        'className' => 'Article',

        'foreignKey' => 'article_id',

        'conditions' => '',

        'fields' => '',

        'order' => '',

        'counterCache' => true

      )

  );

  

}

?>

This would update a comment_count field in my articles table each time a comment is added or removed. Caching this value at the update stage is desirable, as it stops us having to do expensive count operations in PHP or MySQL each time we retrieve the records.

We can tap into Cake's counter cache logic for a related purpose: keeping track of the votes something has. The way I tend to implement this is a slight variation on the theme. It's handy to allow both up and down votes, so instead of simply storing how many votes something has, the aim is to store the overall sum of those votes. If one person votes an image up (+1) and one votes it down (-1), the vote sum is 0. If 20 people all vote up (+1), the vote sum would be 20. Of course, we can keep a regular counter cache alongside this so we always know how many votes were cast. This lets us say something like "this image has a score of 10, based on 20 up votes and 10 down votes".

So, we can represent this with Image and Vote models based on the following schema:

CREATE TABLE images (

  id int(8) UNSIGNED NOT NULL AUTO_INCREMENT,

  title varchar(255) DEFAULT NULL,

  vote_count int(8) DEFAULT 0,

  vote_sum int(8) DEFAULT 0,

  created datetime,

  modified datetime,

  PRIMARY KEY (id)

);

CREATE TABLE votes (

  id int(8) UNSIGNED NOT NULL AUTO_INCREMENT,

  image_id int(8) DEFAULT NULL,
  score int(2) DEFAULT 0 COMMENT="Holds the value of the vote, e.g. 1 for an up vote, -1 for a down vote.",

  created datetime,

  modified datetime,

  PRIMARY KEY (id)

);

Then, we can extend the Model::updateCounterCache() method into our AppModel, adding some logic to sum the appropriate fields. The code is a variation of something I originally saw posted by Andy Dawson.

/**

 * Updates the sum and counter cache of belongsTo associations after a save or delete operation

 *

 * @param array $keys Optional foreign key data, defaults to the information $this->data

 * @param boolean $created True if a new record was created, otherwise only associations with

 *                                  'counterScope' defined get updated

 * @return void

 * @access public

 */

  function updateCounterCache($keys = array(), $created = false) {

    parent::updateCounterCache($keys);

    

    if (empty($keys)) {

      $keys = $this->data[$this->alias];

    }

    foreach ($this->belongsTo as $parent => $assoc) {

      if (!empty($assoc['sumCache'])) {

        if ($assoc['sumCache'] === true) {

          $assoc['sumCache'] = Inflector::underscore($this->alias) . '_sum';

        }

        if ($this->{$parent}->hasField($assoc['sumCache'])) {

          $conditions = array($this->escapeField($assoc['foreignKey']) => $keys[$assoc['foreignKey']]);

          if (isset($assoc['sumScope'])) {

            $conditions = array_merge($conditions, (array)$assoc['sumScope']);

          }

          $fields = 'SUM(score) AS score';

          $recursive = -1;

          list($edge) = array_values($this->find('first', compact('conditions', 'fields', 'recursive')));

          $sum = ife(empty ($edge['score']), 0, $edge['score']);

          

          $this->{$parent}->updateAll(

            array($assoc['sumCache'] => $sum),

            array($this->{$parent}->escapeField() => $keys[$assoc['foreignKey']])

          );

        }

      }

    }

  }

With that done, we can make sure our sum cache and counter cache get used by adding them to our belongsTo model association:

<?php

class Vote extends AppModel {

  

  var $actsAs = array('CounterCache');

  

  var $belongsTo = array(

      'Image' => array(
                                'className' => 'Image',

        'foreignKey' => 'image_id',

        'conditions' => '',

        'fields' => '',

        'order' => '',

        'counterCache' => true,

        'sumCache' => true

      )

  );

  

}

?>

All being well, we now have a way of accessing vote totals without hammering the database. Also, because the values are stored in regular database fields, we can do standard Cakey things with them. For example, $this->Image->find('all', array('order' => 'vote_sum desc')); would grab the images in descending order of popularity.

Comments

Leave a Comment

Your Name

Your Website

Enter the characters shown in the image below
Not case sensitive.[Refresh]

Your Comment

« Articles