Planet CakePHP

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.