cakephp paginate using mysql SQL_CALC_FOUND_ROWS
- by michael
I'm trying to make Cakephp paginate take advantage of the SQL_CALC_FOUND_ROWS feature in mysql to return a count of total rows while using LIMIT. Hopefully, this can eliminate the double query of paginateCount(), then paginate().
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
I've put this in my app_model.php, and it basically works, but it could be done better. Can someone help me figure out how to override paginate/paginateCount so it executes only 1 SQL stmt?
/**
* Overridden paginateCount method, to using SQL_CALC_FOUND_ROWS
*/
public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
$options = array_merge(compact('conditions', 'recursive'), $extra);
$options['fields'] = "SQL_CALC_FOUND_ROWS `{$this->alias}`.*";
Q: how do you get the SAME limit value used in paginate()?
$options['limit'] = 1; // ideally, should return same rows as in paginate()
Q: can we somehow get the query results to paginate directly, without the extra query?
$cache_results_from_paginateCount = $this->find('all', $options);
/*
* note: we must run the query to get the count, but it will be cached for multiple paginates, so add comment to query
*/
$found_rows = $this->query("/* do not cache for {$this->alias} */ SELECT FOUND_ROWS();");
$count = array_shift($found_rows[0][0]);
return $count;
}
/**
* Overridden paginate method
*/
public function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
$options = array_merge(compact('conditions', 'fields', 'order', 'limit', 'page', 'recursive'), $extra);
Q: can we somehow get $cache_results_for_paginate directly from paginateCount()?
return $cache_results_from_paginateCount; // paginate in only 1 SQL stmt
return $this->find('all', $options); // ideally, we could skip this call entirely
}
