After adding the following expression in a query() function of a field handler, other fields get added in the GROUP BY query clause:

$this->query->add_groupby("cpnvc_tnvc.nid");

Everything else in the handler works as expected (joins, relationships, fields, aliases, etc.)!

However when I add the above code line, instead of having one field referenced in a GROUP BY clause, other fields from the query get added in the clause. If I exclude the above code line, there is no GROUP BY clause in the query, thus that line alone seams to be the source of this "issue".

I am not sure if this is default behavior of the views module, but is there any workaround to gain control over the GROUP BY part of the query?

Thanks.

Elvin.

Comments

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Closed (won't fix)

By ANSI SQL rules, any fields in a SELECT query must either be part of the GROUP BY or be aggregate functions. PGSQL enforces this behavior. So this is the standard behavior of Views at this time. In 3.0 we've been working to provide better grouping support but in 2.0 there is not a lot you can do.

miraclestyle’s picture

miraclestyle commented
Status: Closed (won't fix) » Active

Is there any way to prevent a 'SUM(some_filed) AS some_filed_alias' aggregated filed from being added to GROUP BY clause, or other solution to this matter?

The current behavior causes an error: Can't group on 'some_filed_alias'

Even when I don't include the filed in the SELECT, but use it for sorting:

$this->query->add_orderby('', 'SUM(some_filed)', 'DESC');

I get the same error, thus the filed is added by views to the GROUP BY.

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

Add array('aggregate' => TRUE) as an additional argument to the $query->add_field() call.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

tomgf’s picture

tomgf commented

In the meantime, this could be a {dirty} way of achieving it:

function module_views_pre_execute(&$view) {

  if ( $view->name == 'your_particular_view' ) {
    $search = '/ORDER BY/';
    $replace = 'GROUP BY the_unique_field ORDER BY';
    $view->build_info['query'] = preg_replace($search, $replace, $view->build_info['query']);
  }

}