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

Iain Mullan wrote on 10/7/09:

Great tip! This is just what I was looking for. Although I do have a couple of questions...

If you're using the Cake's core counterCache facility, why are you including a CounterCache behaviour?

And also, it would be good to be able to customise the name of the field being summed. In your case it's vote but in mine it's amount.

I've tweaked the code a little to allow for this, and also a default. So, in the belongsTo definition:

'counterCache' => true,
'sumCache' => true,

'sumField' => 'amount' // the field to perform the SUM operation on

And in the updateCounterCache method, replace your line 26 with:

     if (!isset($assoc['sumField'])) {
          $assoc['sumField'] = 'amount'; // default value
     }
     $fields = 'SUM('.$assoc['sumField'].') AS '.$assoc['sumField'].'';

and replace your line 29 with:

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

As you see i've used amount as the default ... maybe there's a better word to use as a default??

Thanks again,

Iain

will wrote on 28/4/11:

dude! thanks so much. Saved my evening!!!

essay topics wrote on 27/7/11:

Could you make more comments in your code it hard for me to undersatnd it.

Paul Gardner wrote on 28/7/11:

A great little snippet, kudos to you and the person who gave you the original inspiration.

I needed to sum multiple fields for my shopping cart and order tables, so I've modified the idea slightly so I can specify multiple sumCaches using the following format

sumCache => array(
'sum_field_one'=>'value_field_one',
'sum_field_two'=>'value_field_two'
)

Then in app_model I simply loop through the sumCache array and save each value. This also helps as the fields are specified within the array whereas your example has one of the fields hard-coded into the function.

Thanks,

Paul (Webbed IT).

BertieorBirdie wrote on 27/8/11:

I rlaely needed to find this info, thank God!

cnghsrp wrote on 27/8/11:

EBPXlG qpjtqdzlrxsg

shaneissohot wrote on 30/8/11:

priligy side affects rql propecia =-[[[

hendlhaxn wrote on 31/8/11:

seroquel results :OOO ordering cymbalta %-OOO

ultrahi wrote on 31/8/11:

purchase prednisone akkoh topamax %]]]

thepantoufle wrote on 2/9/11:

colchicine et thc 872225 nexium xenical ed wetrackit 8-DDD

Missfirewall wrote on 4/9/11:

buy cheep valtrex 02111 topamax, zonegran, weight loss 418

christian louboutin outlet wrote on 1/11/11:

Your current blog website possesses definitely prompted my family, primarily a recent document. be grateful for you actually pertaining to expressing ones thought processes in addition to a impression connected with these individuals has got also been incredible to help everyone.Be grateful for you actually additional time period along with persist in in place by using a good deliver the results.

Cheap UGGs Outlet wrote on 9/11/11:

The best Cheap UGGs Outlet allotment of this bike does not crave gasoline or any added blazon of ammunition to run, like the added bikes do. It works on a bartering battery, so if the array runs down, you can adeptness UGGs Boots For Sale bikes to ability your goals.

Northface jackets wrote on 9/11/11:

Clay turned professional and, in 1964, he stopped Sonny Liston to win the World Heavyweight championship. <a href="http://www.northerfacejackets.com/north-face-denali-jackets-j-43.html">North Face Denali Jackets</a> Over the next four years he defended his title nine times.

snapback new era wrote on 18/11/11:

snapback new era who makes you smile because it takes only a smile to make a dark day seem bright. Find the one

snapback new era wrote on 21/11/11:

If you don't exert yourself in youth, you will regret it in old age.。

discount shoes online wrote on 9/12/11:

Styles adjust therefore carry out women's likes. Whether or not high heel pumps or even systems, sq, circular or perhaps directed toes, sandals as well as sports, your lure of getting discount shoes online is irresistible. You can find just so several forms of sneakers most serving various functions. Several hours are usually put in footwear sectors across the world looking for shoes or boots.

discount north face jackets wrote on 16/12/11:

north face discount else estimated at half the night side of Earth will instantly into State into the day, and the gold light intensity did not say black people burned to death, point the general problem is definitely not flammable, so the three together they appear, if the full power launch.

Trix wrote on 24/12/11:

I never thought I would find such an everaydy topic so enthralling!

lidohe wrote on 24/12/11:

fBVfm3 xyabtqnictsg

penny wrote on 27/12/11:

It can be after they have a good laugh along with have a good laugh just to your brothel to acquire music. could many people most manage internet sites endorsing unnatural GHD Mini? GHD Green Orchid straightforward process elderly by simply recommends involving recommends of the one along with almost any one amongst awaited will certainly wait GHD Diamond almost every along with almost any one amongst find out and about offered your cosmetic foundations using this type of book. Bogus ghd white will not have this sort of vehicle volt attribute. ghd midnight collection have been across associated with regarding decade at this stage and still have transform into by far the most renowned a lock hair straighteners acquired throughout the united kingdom. GHD Green Orchid it is very important help ghd gold maintain preserve on a number of numerous specifics underneath component. So this means probably that may be anything ghd pink could try and accomplish extra sooner or later.The GHD Pink Butterfly above pointed out elements are just plenty of belonging towards the various that may possibly effectively possibly possess the capability to help you pick out no concern regardless of whether GHD Deluxe Midnight or not joining an concerning the internet additional fat burning strategy is worth the price.

nike air max wrote on 28/12/11:

nike air max air max 90 nike air max 90 air max 2009 nike air max 2009 air max 24-7 air max 2011 nike air max 2011 air max tn wholesale hats wholesale new era hats cheap hats discount hats wholesale jeans cheap jeans discount jeans wholesale scarves wholesale scarf cheap scarves cheap scarf new era hats new era caps cheap ugg boots discount ugg boots ugg boots clearance ugg boots sale ugg boots outlet ugg boots on sale kids ugg boots ugg boots online sale ugg boots leather ugg boots cheap ugg for sale ugg for cheap ugg for sale ugg boots ugg outlet fake ugg boots black ugg boots ugg boots men wholesale ugg boots us ugg boots ugg boots uk buy ugg boots wholesale nike shoes cheap nike shoes wholesale cheap nike shoes nike shoes wholesale cheap nike air max wholesale nike air max wholesale cheap nike air max nike air max shoes wholesale nike air max 2009 wholesale nike air max 90 wholesale nike air max 24-7 wholesale nike air max shoes cheap nike air max 2009 cheap nike air max 90 cheap nike air max 24-7 cheap nike air max shoes nike air max 2009 nike air max 90 nike air max 24-7 nike air force shoes nike running shoes nike shoes on sale nike basketball shoes nike shoes cheap nike running shoes sale nike shox shoes nike shox r4 nike shox r4 sales cheap nike shox

north face outlet store wrote on 30/12/11:

Earth of door connection of is Earth of mother of Hou Garden, but this so-called of garden, actually is a area quite does not small of land. in this tablets regional of are north face outlet store just has a area quite broad of Lake, will the north face were before this Lake of water are pumping to Earth of mother of that Garden in of Lake in the words, at most is let that Lake of water level rose a one or two m only, this on around of environment due to the no too large effect, the North Face of the earth mother should not mind. even if she is really unhappy, the north face is to pour water out somewhere.

434 wrote on 2/1/12:

Today's start off to really feel slightly bit like Groundhog Day, louis vuitton sale is not it? A few hours ago, we brought to you a story about Louis Vuitton suing a media organization for violating its trademarks, and now we have news of a provider that thinks it is a very good concept to print the visage of an Hermes Birkin on a cheap louis vuitton canvas bag: the V73 Canvas Printed Tote. The day's handbag news is downright retro. you probably could have guessed how this campaign would search louis vuitton speedy sale pale, valuable and ever so sweet. The louis vuitton sale brand tapped Steven Meisel to shoot Daria Strokous and Kati Nescher in the world's most luxurious ice cream shop, and the outcome match the mood in the louis vuitton speedy 25 clothes and accessories perfectly. Check out another shot right after the jump.the Kelly form louis vuitton neverfull MM a straightforward, arm-carried bag having a front flap closure and a major deal withlouis vuitton neverfull is often a traditional form that will not definitely belong to louis vuitton speedy 30 a lot as it belongs to handbag layout in general. Significantly like the louis vuitton luggage, the shape has reached this kind of a classic degree that attributing it to only a single business no longer seems fair. And when the reinterpretations yield bags as unique in the original since thelouis vuitton speedy Postman's Lock Satchel, I believe it is simple to see why I take that opinion

north face outlet wrote on 6/1/12:

I have been reading your posts regularly. I need to say that you are doing a fantastic jo north face outletb. Please keep up the great work.

Cheap oakleys wrote 3 weeks, 3 days ago:

The oakley oil rig, radar, oakley goggles of <a href="http://www.cheapoakleysonsale.com/">cheap oakleys</a> are hot sale online, and the more and more new product of <a href="http://www.cheapoakleysonsale.com/">fake oakleys</a> are waitting for your choice, If you want to experience the new product brings the protection and the pleasant sensation, <a href="http://www.cheapoakleysonsale.com/">fake oakley sunglasses</a> can let you spend less money and experience more different style, No matter you are men and women, you will get your favorite style on the outlet <a href="http://www.cheapoakleysonsale.com/">cheap oakley sunglasses</a>

loeger <a href="http://payday24loans.com/ ">payday loan</a> %-[[[ <a href="http://payday24loans.co.uk/ ">payday loan UK</a> APAEp <a href="http://paydayloanscheckrates.com/ ">Payday loans</a> 5876 wrote 6 days, 13 hours ago:

loeger payday loan %-[[[ payday loan UK APAEp Payday loans 5876

tmlwrccy <a href="http://accutane-skin.com/ ">accutane</a> iuosez <a href="http://bcheap-cialis.com/ ">generic cialis</a> VphMx <a href="http://bcheap-viagra.com/ ">viagra</a> CZFIqx <a href="http://her-propecia.com/ ">propecia </a> xdghi wrote 1 day, 5 hours ago:

tmlwrccy accutane iuosez generic cialis VphMx viagra CZFIqx propecia xdghi

designer jeans wrote 22 hours, 57 minutes ago:

It is an awesome article and the site; I am touched by your post so much

« Articles

Article Tags

Show all articles, or just those tagged as:

Apache (1)
CakePHP (10)
Domains (1)
Freeware (1)
Graphics (1)
Life (1)
Open Source (2)
Servage (1)
SMS (1)
Software (3)
Ubuntu (2)
WAMP (2)
Web Development (13)
Windows (2)
Work (2)

Feed

The articles RSS feed is available.

Elsewhom

If Then Else.
A weblog by Eduardo Sousa, a film and video geek from Porto, Portugal, with a knack for design and a keen interest in the affairs of mankind
GNOME Coder.
Modern open source software development
FFFFOUND!.
Image bookmarking
Mashable!.
Social software and social networking 2.0
Create Digital Motion.
Motion graphics, live visuals, VJing, video production, and interactive art