zen of coding

Let's help out CakePHP's pagination

While it would be sweet, if the paginated data was usually not much more involved than a simple SELECT * FROM some_table… Unfortunately it is not.

In many cases there are a few, JOINs to related models, and likely a GROUP BY statement somewhere (maybe DISTINCT ?, who knows).

What I am getting at, is an SQL issue if that’s what you’d call it…

count(*) (which CakePHP uses properly, indeed) will yield pretty strange results if you have JOINs and GROUP BY (or any variation thereof… or “worse” yet, things like HAVING, etc.).

The display of records to the end-user will “disagree” with the number, which count(*) produces.
This is because count(*) actually counts everything (meaning across various models in the case of a JOIN).
The main issue comes up when one attempts to paginate the result-set from a complex query. You might see only 20 records displayed due to GROUP BY, but count(*) will actually return 43 (for example) because it does not actually count in the same way SELECT returns the records.

However, with a little help this problem is quite easily resolved…

Remember, we can always override the paginateCount() method inside a model.

For example, if we have an Order Model, just to get the expected count of records we’ll add:

public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
    if($this->statusId) {
      $conditions = array('conditions' => array('Order.order_status_id' => $this->statusId));
    }
    $results = $this->find('count', array_merge(array('recursive' => -1), $conditions));
    return $results;
  }

With this scenario, we can get an accurate count of all orders, or just the ones that have some specific status.

The bottom line is that we know exactly what needs to be counted, and overriding paginateCount() allows us to do just that, regardless of what the actual record fetching query is like.

  • I have the same issue and had to write my own pagination functions to get it working

  • @Josh Oiknine

    Well, that’s kinda comforting to know I’m not alone out there ;)

  • Howard

    The built-in paginate function always seemed a bit limited to me, thus I’ve never used it – I’ve always used a custom element which I feed a few variables and then just use the ‘page’ key for find();

    simply put $this->Model->find(‘all’,array(‘limit’ => 25, ‘page’ => $page);

    the count I usually put on a moderate cache, it’s useful but would be impractical if you had some end page where there’s an entirely new page of content every minute, so caching it for 10 – 15 minutes is generally harmless.

    Anyway – good article, a good read as always, thanks tek!

  • @Howard

    Quite a great suggestion. Thank you.

  • Pingback: Tweets that mention Let’s help out CakePHP’s pagination « nuts and bolts of cakephp -- Topsy.com()

  • For my uses, I never wanted to Contain any related models on the paginateCount. It’s quite silly, I know, but the joins would kill performance. Anyways, I did the following (using Matt Curry’s custom __findMethods() )

    /**
    * Unsets contain key for faster pagination counts
    *
    * @param array $conditions
    * @param integer $recursive
    * @param array $extra
    * @return integer
    * @author Jose Diaz-Gonzalez
    */
    public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
    $conditions = compact(‘conditions’);
    if ($recursive != $this->recursive) {
    $conditions[‘recursive’] = $recursive;
    }
    $extra[‘contain’] = false;
    return $this->find(‘count’, array_merge($conditions, $extra));
    }

  • @Jose Diaz-Gonzalez

    Very nice. Thank you for sharing.

  • Where will we be in 10 years? 20 years?

  • Dan

    Hey thanks for the tip. I have been messing around trying to figure out how to override the paginate count for over a week.

    my cpu load went from 100% for 8 secs to 20% for 1 sec

  • teknoid

    @Dan

    Good improvement. Cool.

  • dythnote

    Great tips, it helps me. Thanks

  • Jean Luis

    Hello

    Im getting this error message when trying to paginate.

    Warning (512): Controller::paginate() – can’t find model equipos in controller EquiposController [CORE\cake\libs\controller\controller.php, line 1104]

    I’ve included this var in the controller

    var $paginate = array(‘limit’ => 25);

    This is the function in the controller (line with arrow)

    function index1()
    {
    $this->set(‘equipos’,$this->Equipo->find(‘all’));
    —->> $data = $this->paginate(‘equipos’);
    $this->set(‘equipos’, $data);
    }

    Anyone has a idea of whats happening????

    thx in advance……

  • Mathielo

    Awesome man! Worked like a charm.

    Thanks a lot!

  • Thank you! very helpful for me

%d bloggers like this: