Error message

Deprecated function: implode(): Passing glue string after array is deprecated. Swap the parameters in drupal_get_feeds() (line 394 of /home1/tylerfra/public_html/includes/common.inc).

Drupal - Views hook_views_query_alter Group By

tyler's picture
Submitted by tyler on Fri, 02/22/2013 - 12:12
Updated: 2013-04-02
Category: 
Code
Tags: 
drupal 7.x
views
planet drupal

So my story goes like this.... I was building a view which showed a slideshow of content. Due to some views' relationships and complex content types, we were getting duplicate results. No big deal? This happens from time to time, the views module doesn't rule the world, yet. So sometimes we can just adjust the views settings to use DISTINCT, and that will solve our problem, other times not. This was a time where DISTINCT didn't seem to be helping. Usually when this happens, I fall back to my good old friend, hook_views_query_alter().

I knew for sure that all I needed to do was get this into my query:

GROUP BY nid

I thought this would be pretty easy, just programatically add on a GROUP by to the $query object. Oh wait, nevermind. Apparently, this isn't quite as straight forward as I'd hoped.

http://drupal.org/node/1365688

Luckily though, turning the 'Use aggregation' setting on my view to 'Yes' and then tinkering with the 'Aggregation settings' on my field added the GROUP BY nid for me. No need for code, awesome. Once again I bow down to the greatness of views.

Comments

Behzad Ravanbakhsh (not verified)

Fri, 05/31/2013 - 19:24

 "Group By" is not working on hook_views_query_alter and it is views module issue. that is reported at https://drupal.org/node/590534, http://drupal.org/node/1608742, http://drupal.org/node/389230#comment-2637200

So, You should use general hook_query_alter.

 

function zz_dev_tools_query_alter( &$query) {

  if($query->alterMetaData['view']->name == "your_views_name"){
     //also you can unset other group by critera by using, unset($query->getGroupBy());
     $query->groupBy("domain_source.domain_id");
  }
}

tyler's picture

tyler

Fri, 05/31/2013 - 19:41

Thank you Behzad, I will give this a try next time I need to add a group by to a view query through alteration.

Niels de Feyter (not verified)

Wed, 02/05/2014 - 10:04

This post helped me a lot! Thx! I had an issue to find the proper 'domain_source.domain_id' part. In my case the group_by had to be done on a normal field that was added to the views "fields" area. But that way it's not part of the query! Yet. To ensure that the field that you want to group_by on is available add it to "filter criteria" and give it an operator "Is not empty (NOT NULL)".When you enable 'Show the SQL query' on admin/structure/views/settings you can see the actual SQL query. In the 'where' part now you should see "field_data_FIELD_YOUR_FIELD.FIELD_YOUR_FIELD_value IS NOT NULL".'field_data_FIELD_YOUR_FIELD.FIELD_YOUR_FIELD_value' is the 'domain_source.domain_id' part.