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

how about `conditions => array('Venue.distance <=' => 25)` ? — Piotr Chabros, Oct 13 '11 at 09:05
@Chetan I mentioned this workaround in the question...it doesn't work for me.. — cowls, Nov 09 '11 at 16:13
@Piotr, distance is an alias so can't be used in a WHERE clause — cowls, Nov 09 '11 at 16:13

score 36 · Accepted Answer · edited Jul 04 '14 at 10:57

36

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

edited Jul 04 '14 at 10:57

David Yell

11,756
13
61
100

answered Oct 13 '11 at 12:48

api55

11,070
4
41
57

Thanks for the suggestion but this is the first workaround I found as described in my question. It doesnt work with pagination as it screws up the COUNT query that is made. – cowls Oct 13 '11 at 17:53
yep, the pagination breaks with that but, you can still do pagination, you just need to override paginate and paginationCount. [Look here](http://www.mainelydesign.com/blog/view/best-paginatecount-cakephp-with-group-by-support) the solution (remember having is in group) – api55 Oct 13 '11 at 18:06
This is close to what I want. However the count query is trying to filter on the distance alias but that's not getting set up. `SELECT COUNT(*) AS count` I think I need that to be something like: `SELECT COUNT(*) AS count, (some sum) AS distance`. Any ideas on this one? – cowls Oct 13 '11 at 18:23
instead of count(*) put the sum you want and in having Total put having Total <= 10 or any other distannce you want – api55 Oct 13 '11 at 19:03

score 1 · Answer 2 · answered May 29 '12 at 04:38

1

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

answered May 29 '12 at 04:38

Ben

1,620
18
11

Writing a function to overwrite the convention seems a quite extreme solution when you can simply set `order` properly. – rlcabral Sep 09 '12 at 11:47
@rlcabral I don't see the `order` solution mentioned in the answers. Can you describe how that works and still provides the ability to have proper pagination? This is not to say that I disagree about this being extreme. However, as far as I could tell, the framework does not provide a way to accomplish the requirement... so sometimes you need to take extreme measures! – Ben Nov 16 '13 at 01:29
1

You can use the `order` to add `having` like: `'order' => $order . " HAVING $havingClause"`. Not pretty, but it will put the `having` in the right place for the query to work. – rlcabral Nov 16 '13 at 16:11

score 1 · Answer 3 · answered Oct 03 '18 at 11:45

As per the manual, CakePHP/2 supports having at last. It was added as find array parameter on version 2.10.0, released on 22^nd July 2017.

From the 2.10 Migration Guide:

Model::find() now supports having and lock options that enable you to add HAVING and FOR UPDATE locking clauses to your find operations.

score 0 · Answer 4 · edited May 20 '12 at 06:56

Just had the same problem. I know, one is not supposed to modify the internal code but if you open the PaginatorComponent and you modify line 188:

$count = $object->find('count', array_merge($parameters, $extra));

to this:

$count = $object->find(
             'count', 
             array_merge(array("fields" => $fields),$parameters, $extra)
         );

Everything will be fixed. You will be able to add your HAVING clause to the 'group' and the COUNT(*) won't be a problem.

Or, make line:

$count = $object->paginateCount($conditions, $recursive, $extra);

to include the $fields:

$count = $object->paginateCount($fields,$conditions, $recursive, $extra);

After that, you can "override" the method on the Model and make sure to include the $fields in the find() and that's it!, =P

score 0 · Answer 5 · answered Oct 29 '12 at 18:40

Here is another idea that doesn't solve the pagination issue, but it is clean since it just overrides the find command in AppModel. Just add a group and having element to your query and this will convert to a HAVING clause.

public function find($type = 'first', $query = array()) {
    if (!empty($query['having']) && is_array($query['having']) && !empty($query['group'])) {
      if ($type == 'all') {
        if (!is_array($query['group'])) {
          $query['group'] = array($query['group']);
        }

        $ds = $this->getDataSource();
        $having = $ds->conditions($query['having'], true, false);
        $query['group'][count($query['group']) - 1] .= " HAVING $having";

        CakeLog::write('debug', 'Model->find: out query=' . print_r($query, true));
      } else {
        unset($query['having']);
      }
    }

    return parent::find($type, $query);
  }

Found it here

https://groups.google.com/forum/?fromgroups=#!topic/tickets-cakephp/EYFxihwb55I

score 0 · Answer 6 · answered Jan 03 '21 at 17:34

0

Using 'having' in find did not work for me. Instead I put into one string with the group " group => product_id, color_id having sum(quantity) > 2000 " and works like a charm. Using CakePHP 2.9

answered Jan 03 '21 at 17:34

Mlv

11
3

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

6 Answers6

Linked