title: CakePHP: How can I use a "HAVING" operation when building queries with find method?
body: <p>I'm trying to use the "HAVING" clause in a SQL query using the CakePHP paginate() method.</p>
  
  <p>After some searching around it looks like this can't be achieved through Cake's paginate()/find() methods. </p>
  
  <p>The code I have looks something like this:</p>
  
  <pre><code>$this-&gt;paginate = array(
          'fields' =&gt; $fields,
          'conditions' =&gt; $conditions,
          'recursive' =&gt; 1,
          'limit' =&gt; 10, 
          'order' =&gt; $order,
          'group' =&gt; 'Venue.id');
  </code></pre>
  
  <p>One of the $fields is an alias "distance". I want to add a query for when distance &lt; 25 (e.g. HAVING distance &lt; 25).</p>
  
  <p>I have seen two workarounds so far, unfortunately neither suit my needs. The two I've seen are:</p>
  
  <p>1) Adding the HAVING clause in the "group" option. e.g. <code>'group' =&gt; 'Venue.id HAVING distance &lt; 25'</code>. This doesn't seem to work when used in conjunction with pagination as it messes up the initial count query that is performed. (ie tries to <code>SELECT distinct(Venue.id HAVING distance &lt; 25)</code> which is obviously invalid syntax.</p>
  
  <p>2) Adding the HAVING clause after the WHERE condition (e.g. <code>WHERE 1 = 1 HAVING field &gt; 25</code>) This doesn't work as it seems the HAVING clause must come <strong>after</strong> the group statement which Cake is placing after the WHERE condition in the query it generates.</p>
  
  <p>Does anyone know of a way to do this with CakePHP's find() method? I don't want to use query() as that would involve a lot of rework and also mean I'd need to implement my own pagination logic!</p>
  
  <p>Thanks in advance</p>