Best PaginateCount for CakePHP - with Group By Support

Posted on 08/07/2011 at 05:03 pm by Kevin Wentworth
Viewed 19,755 times | 0 comments

I'm been muddling my way through pagination with multiple joins, and complex filtering.  I finally got it all working (more on that later, maybe....) when I noticed that I couldn't paginate my results.  Everything was working fine until I added a "group" parameter to the find call.  Instead of getting the right count, I got a count of 1!  A quick look at my sql log tables (at the bottom of the page) and I saw that I had actually returned all the mysql records that were needed!  If only there was a way to use them...

Best PaginateCount Method for CakePHP

In my research I found some great posts here and here.  I've combined the two of them into one and added the function to my CakePHP app model class.  Now every paginateCount() query runs faster and factors in any group by clauses.  Here's my code for the best paginateCount() method in CakePHP (so far, anyway):

Update: I used to have $extra['contain'] = false; in the paginateCount function but have changed it to unset($extra['contain']).  Setting 'contain' to false has the same effect as setting recursive => -1.  This isn't want you want if you have a condition on the joined table, b/c the join needs to be there to get the paginate count correct.

  1. public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
  2.         $conditions = compact('conditions');
  3.         if ($recursive != $this->recursive) {
  4.             $conditions['recursive'] = $recursive;
  5.         }
  6.         unset( $extra['contain'] );
  7.         $count = $this->find('count', array_merge($conditions, $extra));
  9.             if (isset($extra['group'])) {
  10.                 $count = $this->getAffectedRows();
  11.             }
  13.         return $count;
  14.     }

That's it for now.

-Kevin Wentworth

public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
        echo 'dude';
        $conditions = compact('conditions');
        if ($recursive != $this->recursive) {
            $conditions['recursive'] = $recursive;
        $extra['contain'] = false;
        $count = $this->find('count', array_merge($conditions, $extra));
        if (isset($extra['group'])) {
            $count = $this->getAffectedRows();
           return $count;
Bookmark and Share

Tags for Best PaginateCount for CakePHP - with Group By Support

Cakephp | Behaviors | Web Programming | Usage | Database | Example | Mysql | Habtm | Hack

Comments for this Posting

No comments. Be the first to post a reply.

Sorry, comments are closed for this posting.

Please Email Kevin if you have any questions. Thanks!

Meet Site Avenger - Hosted Content Management System

Powered By: Site Avenger | Site Production: Saco Design