2

How do I add the equivalent of GROUP BY using drupal 6 and views 2?

2

Views 3 supports grouping.

For views 2, aside of Views Group By, you can also write a custom module, implement hook_views_query_alter(&$view, &$query) and do something like:

function MYMODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'view_name') {
    if ($view->current_display == 'page_XXXXXX') {
      // Tell the query constructor these fields must be used with the GROUP BY clause
      $query->add_groupby('field1');
      $query->add_groupby('field2');

      // hack to make the query constructor just ignore these fields (not adding them to the GROUP BY clause. It's needed to let it continue without problems
      $query->fields['fieldalias3']['aggregate'] = TRUE;
      $query->fields['fieldalias4']['aggregate'] = TRUE;
      // ... all but the grouped and aggregated fieldaliases must have this.

      // Configure the fields that perform the aggregation (SUM, AVG, etc)
      $query->fields['aggregatedfieldaliasA']['table'] = '';
      $query->fields['aggregatedfieldaliasA']['field'] = "SUM(fieldA)";
    }
  }
}

You now should be able to see the resulting query in the preview when editing the view and make sure it is correct. Field aliases in the code refer to "SELECT table.field1 as fieldalias1 ...", it's easy to get them just by checking the query shown in the preview.

1

There is a module, Views Group By that adds the GROUP BY functionality to Views

  • I've tried this and it adds extra fields to the GROUP BY function. This issue has been documented with other users, so it does not work for my needs. – kylex Sep 30 '11 at 19:26
  • 3
    If your question more accurately reflected your needs and what you have already tried, perhaps we would have known not to suggest this. You simply asked "how".. – Laxman13 Sep 30 '11 at 19:29
0
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');    
    }
}
  • Your answer is appreciated, but don't try to post spam - or your account, and any contributions, will simply be deleted. FYI all contributed links are rel="nofollow", so there's really no point. – Clive Nov 12 '13 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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