Add HAVING support

Congratulations! You're the first person to request support for the HAVING clause.

Currently Views doesn't support it. If you look at views_query.inc you'll see the various bits and pieces the query object supports. add_having is something I never had call to put in, since Views doesn't do a lot with GROUPing. There are a bunch of odd things you have to do to properly group and the views query object isn't even a little bit smart about that. So it gets complex.

Patches are welcome.

+1. The requests for grouping actually come up pretty often, usually to group nodes by a taxonomy term. There are handbook pages somewhere talking about how to do this in the theming layer. I have one of those things in my site.

However, this really should be supported in Views query engine and admin UI... I think this would be popular if/when available.

I'm working on a jpgraph integration module and seams obvious I need to make it "views compliant". :-)

I played a little with view's header and footer, and results are promising, but HAVINGs are the perfect solution to quantify the results, for instance, based on a taxonomy term.

I took a look to Karen's view_calc but it neither fit.

I managed in the specific cases required by my project but looks like I'm not good enough programmer to patch your module with the HAVINGs extension.

So are you thinking in include this in future releases? Any suggestion to extend views with graph support.

Thanks a lot for your help and for the wonderful views module.

Hmm, turns out I am now interested in this too :)

anytime you want to add a filter based on a calculated field (like a COUNT), this would be handy. I'm mostly only familiar with MySQL, are there any concerns regarding portability of this kind of statement?

I found this on the mysql docs:

the SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions.

looks like we could do something very simple like the add_groupby(), and insert the having clause between "group by" and "order by".
The supplied patch is very basic, and does no error checking, but works, and could be a starting point if anyone wants to add to it.

My testing has only been the 1 use case I have so far, but it worked out.

Query engine mod looks like a very good start.

What do you think is the best way to expose add_having through the API / UI ?

well, I dont think it will change anything for the UI.
But it already adds the add_having() method to the API,
so when module writers expose their variables to views, they can use a standard filter handler, and call that method in their handler.

so for ex, currently one would have something like...

  tables['table_name'] = array(
    'name' => 'table_name',
    ...
    'fields' => array(
      'num_rows' => array(
        'name' => t('Fake Table: Num Rows'),
        'notafield' => TRUE,
        'query_handler' => 'module_views_handler_query_num_rows',
        'handler' => 'module_views_handler_field_num_rows',
      ),

and as handlers:

function module_views_handler_query_num_rows'($field, $fieldinfo, &$query) {
  $query->add_field("COUNT(1)", null, "num_rows");
  $query->add_groupby("table_name.nid");
}
function module_views_handler_field_num_rows($fieldinfo, $fielddata, $value, $data) {
  return $data->num_rows;
}

so that would set up your table, and make available a field that counts the number of rows and returns it. so far so good. But there's no way to add it to filters, since you need a HAVING clause, and a WHERE just won't do. So with the attached patch:

    ...
    'filters' => array(
      'num_rows' => array(
        'name' => t('Fake Table: Num Rows'),
        'operator' => 'views_handler_operator_gtlt',
        'handler' => 'module_views_handler_filter_num_rows'
      ),
    ...

with handler

function module_views_handler_filter_num_rows($op, $filterdata, $filterinfo, &$query){
  $query->add_having('num_rows '. $filterdata['operator'] .' '. $filterdata['value']);  
}

So the filter values and operators are still selectable in the UI as normal, the only change is the handler calls the new add_having() method, and later when the query is run, the HAVING clause is inserted into the SQL statement.

Yes, this example is helpful. Any chance you could you post sample code for a completed implementation?

The reason I brought up the API issue is because HAVING is dependent on GROUP BY, which makes filters dependent on fields. This also does not solve the challenge of grouping in the output - now requiring theme hacking.

Maybe in addition to fields/filters we need a separate equal-level entity called "groups" to integrate group-able fields, filtering groups by having clause and recognizable by views plugins for intelligent theming (default output grouping behavior).

This seems like an obvious next step to me.

The example I posted is very basic, and does no error checking, but is functional. Could you explain a little more about what a completed implementation means to you?

Case in point - I want to output nodes grouped by taxonomy (from specific vocab). This means that instead of a single node list (or table or whatever), I want to generate a list of groups (per taxonomy) with each of those groups comprising a list of nodes.

Right now I have to do this grouping by hacking the view theming function, which is error prone, redundant and hard to maintain. A better approach would be to have a Views API / UI for generating "lists of groups of nodes" instead of just a "lists of nodes".

This could include several things: 1) Picking which field(s) to use for grouping; 2) Picking which filters to use on groups (that's where having clause comes in); 3) Sorting groups (which could be different from sorting nodes); 4) Calling different theming functions to theming each group vs. output list of these groups; 5) Good theming defaults for group handing in standard view types (table, list, etc).

So this is about extending views / views_ui to replace custom hacking of theming functions.

Ah, thank you. I think we are talking about 2 completely separate things in this case. The use of a MySQL GROUP BY clause doesn't really do what it might sound like (grouping a bunch of nodes by taxonomy term for example). All it really does is tell MySQL what fields it should use when calculating aggregate functions. And my patch is really just concerned with adding a HAVING clause to the query, I don't think it is useful for your purpose at all.

These things are separate but related.

Whatever goes into GROUP BY clauses could/should be recognized by and interact with management of node groups.

Your patch is still one of the first steps to smarter group handing.

Tracking.

I don't think anything is happening with this thread. And Views has undergone some major changes since these patches were discussed, so none of this thread is likely to apply. should we close this issue and open a new one if there's still need/demand?

Since this would not affect existing implementations I have only one issue with this patch:

The caller is responsible for ensuring that the fields and tables are fully qualified and added

We can do better here, since Views already provides the corresponding functions to check whether a field or table has been added.

Also, I need at least one confirmation from someone who did not have this patch applied on a larger Views-based Drupal site to be RTBC.

Also, better title.

Thanks for the patch, it's exactly what I was about to cobble together for my patch of location 5.x-3.x-dev.

It might also be a good idea to have the notion of "having_args", to be consistent with "where_args".
For example, add_having should support the equivalent of this:

$query->add_where("$table.latitude > %f", 44.32);

In implementing this, I just got bitten by another bug. http://drupal.org/node/290132

My calculated distance field was not being pulled into the count query.
Views was trying to optimize the count query by not including any fields, even if there's a GROUP BY clause.
This was fixed in D6 but not D5.

No changes are being made to 1.x at this point.

Still no HAVING support in Views.

Another good reason is that we have to repeat whole complex expressions in a WHERE clause, just because WHERE doesn't support aliases, while HAVING does.

SELECT node_field_data.nid AS nid, (
      ACOS(LEAST(1,
        0.79042312203335
        * node__field_geolocation_demo_single.field_geolocation_demo_single_lat_cos
        * COS( -2.136621868842 - node__field_geolocation_demo_single.field_geolocation_demo_single_lng_rad  )
        +
        0.61256125257402
        * node__field_geolocation_demo_single.field_geolocation_demo_single_lat_sin
      )) * 6371
    ) AS proximity
FROM {node_field_data} node_field_data
LEFT JOIN {node__field_geolocation_demo_single} node__field_geolocation_demo_single ON node_field_data.nid = node__field_geolocation_demo_single.entity_id
WHERE (((
      ACOS(LEAST(1,
        0.79042312203335
        * node__field_geolocation_demo_single.field_geolocation_demo_single_lat_cos
        * COS( -2.136621868842 - node__field_geolocation_demo_single.field_geolocation_demo_single_lng_rad  )
        +
        0.61256125257402
        * node__field_geolocation_demo_single.field_geolocation_demo_single_lat_sin
      )) * 6371
    ) <= '8046.72'))

could be:

SELECT node_field_data.nid AS nid, (
      ACOS(LEAST(1,
        0.79042312203335
        * node__field_geolocation_demo_single.field_geolocation_demo_single_lat_cos
        * COS( -2.136621868842 - node__field_geolocation_demo_single.field_geolocation_demo_single_lng_rad  )
        +
        0.61256125257402
        * node__field_geolocation_demo_single.field_geolocation_demo_single_lat_sin
      )) * 6371
    ) AS proximity
FROM {node_field_data} node_field_data
LEFT JOIN {node__field_geolocation_demo_single} node__field_geolocation_demo_single ON node_field_data.nid = node__field_geolocation_demo_single.entity_id
HAVING proximity <= '8046.72'

Same holds of course for aggregate functions. By the official SQL standard, I even think it's only allowed for aggregates, so we might also consider a CTE, which we currently aren't supporting either. D'oh!

Patch is over 10 years old and against Views 6.x-1.x, so marking active. There's however no valid reason to close this perfectly valid feature request just because a particular branch is no longer supported.