CakePHP: Paginating Queries With GROUP BY clause and getAffectedRows()

In CakePHP v1.3 (and probably earlier), you need to work a little more to correctly paginate queries that use GROUP BY clause. This is because Model::find('count') doesn't get the record count right when with that. The most common solution I found online to get the correct count was to add a paginateCount() method on the model and use Model::getAffectedRows(), like what was done in this article: http://wiltonsoftware.com/posts/view/custom-group-by-pagination-and-a-calculated-field.

Pagination was fine until I encountered a case where the solution did not seem to work. I traced the problem down to the wrong count given by Model::getAffectedRows(). Digging inside CakePHP's Model source reminded me that Model::afterFind() callbacks (including those provided by any Behaviors attached to the model) are also executed. If there was at least one callback that queried the db, calling Model::getAffectedRows() after a Model::find('count') would give a different result.

To workaround this, I had to disable callbacks before doing the count:

	
function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
    $parameters = compact('conditions');
    $this->recursive = $recursive;
		
    if ( isset($extra['group']) ) {
        $extra['callbacks'] = false; // Disable callbacks
        $count = $this->find('count', array_merge($parameters, $extra));
        $count = $this->getAffectedRows();
    } else {
        $count = $this->find('count', array_merge($parameters, $extra));
    }

    return $count;
}

The solution varies depending on the callbacks: how they affect the number of records Model::find('count') returns and/or if the make any queries to the database that can change the count from Model::getAffectedRows(). One might have to detach some behaviors and leave others to run their afterFind() callback, or manually do the callback after the call to Mode::getAffectedRows() has been made. In my case, I can simply disable all callbacks.

 

Comments

No comments so far.

Leave a Reply

 
(will not be published)
 
 
Comment
 
 

 

Resources