query->add_groupby & aggregate vs. nonaggregate fields

Yes, and the 'expected' SQL that you generate is not ANSI legal SQL.

When using GROUP BY items that appear in the SELECT must either use aggregate functions OR appear in the GROUP BY. This is enforced by postgres, therefore Views must enforce this.

That is correct. Views enforces ANSI SQL where necessary. Whatever you're doing should not be adding a GROUP BY on node_vid.

Hi and sorry to reopen this up.
I need the same functionality from views and I'm banging my head to the desk because I cannot find a way to make this working.
I made a custom module that implements a Views Field from the {sessions} table: hostname.
Here is the query that gets generated:

SELECT users.uid AS uid,
   users.name AS users_name,
   users.login AS users_login,
   sessions.hostname AS sessions_hostname
 FROM d_users users 
 INNER JOIN d_users_roles users_roles ON users.uid = users_roles.uid
 INNER JOIN d_sessions sessions ON users.uid = sessions.uid
 WHERE users_roles.rid = 3

but if a user is logged in from more than 1 browser/computer/location at the same this means that he will appear more than 1 time.
I tried using DISTINCT(users.uid) but it is not working. Also the add_groupby function does what aaronbauman said above.
So how can I mimic this behaviour ?

I solved my issue with a prequery like: "SELECT sid FROM {sessions} GROUP BY uid" and then selected only those sids.

Thanks for this wonderful module merlin :)

setting the previous status

OK, here's a question -- I've run into the same issue here, but can we change the order and have things work? My question is whether this would still be legal SQL.

If you take line 957:

$groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n";

and flip the order of the merge:

$groupby = "GROUP BY " . implode(', ', array_unique(array_merge($non_aggregates, $this->groupby))) . "\n";

I do not understand how I can fix this.

I have a table that has multiple matches for the relation with vid of node. I want to take out the match where a certain field is the highest /lowest value.

in the past I did

  function query() {
    $this->ensure_my_table();
    $this->add_additional_fields();
    $this->field_alias = $this->table . '_' . $this->field;
    $this->query->add_groupby($this->aliases['vid']);
    
    $join = new views_join();
    $join->construct('clubmember_belt', $this->table_alias, 'vid', 'vid');
    
    $this->belts_table = $this->query->ensure_table('clubmember_belt', $this->relationship, $join);
    $this->query->add_field(NULL, "MIN($this->belts_table.date)", $this->field_alias);
  }

This won't work anymore now and I have no clue how to fix it....

Just stumbled across this post when looking into an issue with views and the search index filter. Our view is pretty standard stuff, exposing the search index field and sorting by the search score - BUT - we are running into issues because the query generated by views adds every field in the SELECT portion of the query into the GROUP BY. We have this

SELECT DISTINCT(node.nid) AS nid,
   SUM(search_index.score * search_total.count) AS score,
   node.title AS node_title,
   node.created AS node_created
 FROM node node 
 LEFT JOIN search_index search_index ON node.nid = search_index.sid
 LEFT JOIN search_total search_total ON search_index.word = search_total.word
 INNER JOIN search_dataset search_dataset ON search_index.sid = search_dataset.sid AND (search_index.type = search_dataset.type)
 WHERE (node.status <> 0) AND (search_index.word = 'test' OR search_index.word = 'terms') AND (search_index.type = 'node') AND (search_dataset.data LIKE '% test %' AND search_dataset.data LIKE '% terms %')
GROUP BY search_index.sid, search_index_score, node_created, nid, score, node_title
 HAVING COUNT(*) >= 2
 ORDER BY search_index_score DESC, node_created DESC

The problem being that the groupby is too "strict" and the having condition is never met even if both search terms are found in the search index.

Just spent some time solving the same problem...
The answer to bwynants' problem is to add an aggregate parameter to the add_field call :

$this->query->add_field(NULL, "MIN($this->belts_table.date)", $this->field_alias, array('aggregate' => TRUE));

At least it worked for me.

this is exactly what I did :-) thanks anyway!

OK, this is a bug - the add_groupby() DOESN'T NEED TO ADD ALL THE SELECT FIELDS to the GROUP BY statement.

Take a look at this:
http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myth...

The linked article simply explains how a query like this can make sense in a specific subset of situations. However, I don't see how those situations can't also be handled with aggregation.

This doesn't make sense in most cases, and doesn't work on many SQL servers. (It even will or will not work on MYSQL, depending on the configuration of the server.)

The whole thing reads as a feature request for the ability to make MYSQL-specific views which will have undefined behavior.

#10 does not working on D7?

sup

not sure if its directly related, but for d7, i had success using the following code.

      $params = array(
        'function' => 'avg',
      );
      $view->query->add_field($table, $field, '', $params);
    }

I had the same problem and finally solved it implementing hook_query_alter (note that is different than hook_views_query_alter).

function YOURMODULE_query_alter(QueryAlterableInterface $query) {
  $view_name = 'your_view_name';
  if ($query->hasTag('views_' . $view_name)) {    
    $query->groupBy('uid');
  }
}

source: http://lechronicles.blogspot.co.uk/2011/11/how-to-use-hookqueryalter-to-...

Same problem here. I guess my view doesn't have a tag, so I couldn't use hook_query_alter. Combining all sorts of help on the web, I came to add $query->add_having() to the function:

function YOURMODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'calendar' && $view->current_display == 'block_2') {
    $query->add_having(0, 'node_title', '', '!=');
    $query->add_groupby('node_title');
  }
}

#17 works perfectly - it's just impossible to add a group by in hook_views_query_alter but easy in hook_query_alter.

Shame that took so long to work out!

#17 works perfectly

#17 works perfect, even in D7. Thanks @acontia !

#17 works perfect D7. I love you @acontia!

The Drupal 6 branch is no longer supported, please check with the D6LTS project if you need further support. For more information as to why this issue was closed, please see issue #3030347: Plan to clean process issue queue