cakephp paginate使用mysql SQL_CALC_FOUND_ROWS

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().

我正在尝试使Cakephp paginate利用mysql中的SQL_CALC_FOUND_ROWS功能在使用LIMIT时返回总行数。希望这可以消除paginateCount（）的双重查询，然后是paginate（）。

• http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
• 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?

我把它放在我的app_model.php中，它基本上可以工作，但它可以做得更好。有人可以帮我弄清楚如何覆盖paginate / paginateCount所以它只执行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()?

问：如何获得paginate（）中使用的SAME限制值？

    $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()?

问：我们能不能直接从paginateCount（）获取$ cache_results_for_paginate？

    return $cache_results_from_paginateCount;  // paginate in only 1 SQL stmt
    return $this->find('all', $options);   // ideally, we could skip this call entirely
}

1 个解决方案

#1

The way I solve this for paginating results from a web service which returns the results for the current page and the total number of results available in the same response is to copy the paginate property from the controller to the model in your controller action, before you call $this->paginate(); so that my paging settings are available in the model's paginateCount() method. Then in paginateCount() issue the call to the webservice to get the results. Then store the results in a property of the model then return the total number of results available. Then in the model's paginate() method, I return the results I fetched and stored in the paginateCount() method.

我通过返回当前页面结果的Web服务的分页结果以及同一响应中可用结果总数来解决此问题的方法是在控制器操作中将paginate属性从控制器复制到模型中调用$ this-> paginate（）;这样我的分页设置在模型的paginateCount（）方法中可用。然后在paginateCount（）中发出对webservice的调用以获得结果。然后将结果存储在模型的属性中，然后返回可用结果的总数。然后在模型的paginate（）方法中，我返回我获取并存储在paginateCount（）方法中的结果。

Perhaps something along these lines might work for you too?

也许这些方面的东西也可能对你有用吗？