title: cakephp paginate using mysql SQL_CALC_FOUND_ROWS
body: <p>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().</p>
  
  <ul>
  <li><a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows</a></li>
  </ul>
  
  <p>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?</p>
  
  <pre><code>/**
   * 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-&gt;alias}`.*";
  </code></pre>
  
  <p>Q: how do you get the SAME limit value used in paginate()?</p>
  
  <pre><code>    $options['limit'] = 1;   // ideally, should return same rows as in paginate()     
  </code></pre>
  
  <p>Q: can we somehow get the query results to paginate directly, without the extra query?</p>
  
  <pre><code>    $cache_results_from_paginateCount = $this-&gt;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-&gt;query("/* do not cache for {$this-&gt;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);
  </code></pre>
  
  <p>Q: can we somehow get $cache_results_for_paginate directly from paginateCount()? </p>
  
  <pre><code>    return $cache_results_from_paginateCount;  // paginate in only 1 SQL stmt
      return $this-&gt;find('all', $options);   // ideally, we could skip this call entirely
  }
  </code></pre>