CakePHP: How can I use a "HAVING" operation when building queries with find method?

Question

I'm trying to use the "HAVING" clause in a SQL query using the CakePHP paginate() method.

After some searching around it looks like this can't be achieved through Cake's paginate()/find() methods.

The code I have looks something like this:

$this->paginate = array(
        'fields' => $fields,
        'conditions' => $conditions,
        'recursive' => 1,
        'limit' => 10, 
        'order' => $order,
        'group' => 'Venue.id');

One of the $fields is an alias "distance". I want to add a query for when distance < 25 (e.g. HAVING distance < 25).

I have seen two workarounds so far, unfortunately neither suit my needs. The two I've seen are:

1) Adding the HAVING clause in the "group" option. e.g. 'group' => 'Venue.id HAVING distance < 25'. 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 SELECT distinct(Venue.id HAVING distance < 25) which is obviously invalid syntax.

2) Adding the HAVING clause after the WHERE condition (e.g. WHERE 1 = 1 HAVING field > 25) This doesn't work as it seems the HAVING clause must come after the group statement which Cake is placing after the WHERE condition in the query it generates.

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!

Thanks in advance

Answer 1

You have to put it with the group conditions. like this

$this->find('all', array(
    'conditions' => array(
        'Post.length >=' => 100
    ),
    'fields' => array(
        'Author.id', 'COUNT(*) as Total'
    ),
    'group' => array(
        'Total HAVING Total > 10'
    )
));

Hope it helps you

Answer 2

I used the following trick to add my own HAVING clause at the end of my WHERE clause. The "dbo->expression()" method is mentioned in the cake sub-query documentation.

function addHaving(array $existingConditions, $havingClause) {
  $model = 'User';
  $db = $this->$model->getDataSource();

  // Two fun things at play here,
  // 1 - mysql doesn't allow you to use aliases in WHERE clause
  // 2 - Cake doesn't allow a HAVING clause separate from a GROUP BY
  // This expression should go last in the WHERE clause (following the last AND)
  $taut = count($existingConditions) > 0 ? '1 = 1' : '';
  $having = $db->expression("$taut HAVING $havingClause");

  $existingConditions[] = $having;

  return $existingConditions;
}