12

I'm using Views 7.x-3.6 and I tried altering the GROUP BY clause with hook_views_query_alter() as below:

function mymodule_views_query_alter(&$view, &$query) {
    if ($view->name == "view_name"){
      $query->add_groupby('field_name');
      dpm($query);    
    }
}

When I look in $query, the groupby clause is correctly enabled but the SQL query is not affected: the GROUP BY clause doesn't appear:

enter image description here

What I eventually did is using a Drupal core hook (hook_query_alter()) and it worked fine: the SQL is now affected.

function mymodule_query_alter(QueryAlterableInterface $query) {
  $view_name = 'view_name';
  if ($query->hasTag('views_' . $view_name)) {    
    $query->groupBy('field_name');
  }
}

Any reason why my hook_views_query_alter() doesn't work? I'm wondering if there's a cleaner way to do it and.

7 Answers 7

12

Use add_field() with array('function' => 'groupby') parameter.

$query->add_field('node', 'nid', 'node_nid', array('function' => 'groupby'));
$query->add_groupby('node.nid');

Aditional information:

After using add_groupby you might see the next code after GROUP BY:

GROUP BY node.nid, nid

See the next issue: https://drupal.org/node/1578808

To avoid unnecessary GROUP BY conditions add:

$query->distinct = TRUE;
4
  • 1
    How can we remove un-necessary group by without adding $query->distinct = TRUE;
    – Cool
    Jan 4, 2014 at 14:17
  • I spent few hours debugging views group by. But currently I don't know other solution.
    – milkovsky
    Jan 5, 2014 at 16:09
  • Thanks for your comment. hmm.. i am asking because, if we add $query->distinct = TRUE; then it will add nid with group by. It is not necessary for me. Any other option? Do you know how to add unique in views query?
    – Cool
    Jan 6, 2014 at 9:04
  • It was views_handler_filter::query() afair. Try $query->distinct(); in hook_query_alter
    – milkovsky
    Apr 29, 2015 at 7:25
1

If you look are the documentation of hook_views_query_alter, I believe the query is already prepared to run. Eg, replacements have been made by the database API and its about to be sent over the wire to MySQL. Another example of hook_views_query_alter usage can be seen the BTMash blog.

This being the case you no longer have a DB Query Object but the pieces of the SQL statement with expressions, variables, etc as an Array.

I dont have a query in front of me but something like the following untested code is what you want:

$query->groupby[0]['field'] = 'field_name';
2
  • Thanks for your answer! I tried but it doesn't work either: again, the $query object is altered (differently than the above of course) but again, the SQL query doesn't change!
    – jozi
    May 20, 2013 at 16:00
  • did you try a different views hook? _pre_render, or _pre_build? .... I would try hook_views_pre_execute() ...api.drupal.org/api/views/views.api.php/function/…
    – tenken
    May 20, 2013 at 18:08
1

This is the way it is done in views_query_alter; the node_access part is not necessary.

function xdas_target_audience_views_query_alter(&$view, &$query) {
  $controlled_views = variable_get('xdas_target_audience_views', array(
    'landing_products',
    'promotions',
    'landing_coupons',
    'landing_coupons_belvita',
    'landing_coupons_lulu',
    'related_coupon',
    'cuponazo',
    'banner',
    'brands',
  ));
  if (in_array($view->name, $controlled_views) && $view->base_table == 'node') {
    //add node_access to views so we can control access.
    $query->add_tag('node_access');
    $join = new views_join();
    $join->construct('xdas_target_audience_boost', 'node', 'nid', 'nid');        
    $query->add_relationship('xdas_target_audience_boost', $join, 'node');
    $query->add_field('xdas_target_audience_boost', 'score', 'score' , array('function' => 'max'));
    $query->add_orderby(NULL, NULL, 'DESC', 'score');   
    $query->add_groupby('nid');    
  }
}
1

Late to the party but this might help someone else.

In my case I was providing a list of users (using additional module Profile2) with a Taxonomy Term field (https://drupal.org/node/1808320). I was seeing multiple results I could not get rid of through the Views 3 API.

In my custom module I added:

/**
    Implementation of hook_views_query_alter()
**/
function MYMODULE_views_query_alter(&$view, &$query) {

  if($view->name == "provider_results_list" && $view->current_display == "page_1") {

    $query->distinct = TRUE;

    // User the provider uid to make the results distinct: using a taxonomy field for
    // keyword searches brings up multiple results. 
    $query->add_field('profile', 'uid', 'provider_uid', array('function' => 'groupby'));

  }
}

The parameters for add_field are

add_field(TABLE_NAME, FIELD, FIELD_ALIAS, array('function' => 'groupby'))
1

If you are still facing with unnecessary GROUP BY statements added to query, you could remove them in hook_query_alter(). I solved the issue with ordering comments by most commented parent comments for example.

So in hook_views_query_alter() I have:

/**
 * Implements hook_views_query_alter().
 */
function MODULENAME_views_query_alter(&$view, &$query) {
  if ($view->name == 'reviews' && $query->orderby[0]['field'] == 'comment_thread') {
    $join = new views_join;
    $join->construct('comment', 'comment', 'cid', 'pid', NULL, 'LEFT');
    $query->add_relationship('c1', $join, 'comment');
    $query->add_field('comment', 'pid', 'c1_pid', array('function' => 'groupby'));
    $query->add_groupby('c1.pid');
    $query->add_orderby(NULL, 'COUNT(c1.cid)', 'DESC', 'comments_count');
    $query->distinct = TRUE;
    $query->orderby[0] = $query->orderby[count($query->orderby) - 1];
    unset($query->orderby[count($query->orderby) - 1]);
  }
}

I got error related to inability to group by

comments_count

field that is actually a result of SQL COUNT() function.

I ended up with this:

/**
 * Implements hook_query_alter().
 */
function MODULENAME_query_alter(QueryAlterableInterface $query) {
  if (isset($query->alterMetaData['view']->name) && $query->alterMetaData['view']->name == 'reviews') {
    $fields =& $query->getGroupBy();
    foreach (array_keys($fields) as $key) {
      // Remove group by statements from generated query which were actually not set in view query.
      if (!in_array($key, $query->alterMetaData['view']->query->groupby)) {
        unset($fields[$key]);
      }
    }
  }
}
1

I wondered that no one provided a real solution that just works. There is one such method that I've found here, many thanks to @b-ravanbakhsh:

/**
 * Implements hook_query_alter().
 */
function MODULENAME_query_alter(QueryAlterableInterface $query) {
  if (isset($query->alterMetaData['view'])) {
    if($query->alterMetaData['view']->name == 'YOUR_VIEW_NAME'){
      // also you can unset other group by critera by using, unset($query->getGroupBy());
      $query->groupBy('users.uid');
    }
  }
}
1

In Drupal 8 and thanks to graceman9 approach, it will be :

use Drupal\Core\Database\Query\AlterableInterface;

function MYMODULE_query_alter(AlterableInterface $query) {
  if (isset($query->alterMetaData['view'])) {
    if($query->alterMetaData['view']->id() == 'replace_by_view_machine_name') {
      // Group by UID to remove duplicates from View results
      $query->groupBy('users_field_data.uid');
      // If multilingual site, you should add the following
      // $query->groupBy('users_field_data.langcode');
    }
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.