cakephp分页使用mysql SQL_CALC_FOUND_ROWS [英] cakephp paginate using mysql SQL_CALC_FOUND_ROWS

问题描述

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

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

• http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

我把它放在我的app_model.php中，它基本上可以工作，它可以做得更好。有人可以帮我找出如何覆盖paginate / paginateCount这样它只执行1 SQL stmt？

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}`.*";

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

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);

问：我们可以直接从paginateCount（）获取$ cache_results_for_paginate吗？

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
}

推荐答案

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

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.

也许这些行的东西可能也适用于你？

Perhaps something along these lines might work for you too?

这篇关于cakephp分页使用mysql SQL_CALC_FOUND_ROWS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！