When i use a views field handler and invoke $this->query->add_groupby('node_vid') in the query function, 'node_vid' is properly added to the query->groupby array.

However, when the SQL gets generated, the GROUP BY clause contains ALL the fields in the SELECT clause.
When I remove the add_groupby('node_vid') call, the GROUP BY clause disappears completely.

I tracked this issue down to this clause in views/includes/query.inc, lines 942 - 947:

    if ($has_aggregate || $this->groupby) {
      $groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n";
      if ($this->having) {
        $having = $this->condition_sql('having');
      }
    }

Here's the SQL that that generates (unexpected):

SELECT node.nid AS nid, 
my_module.fid AS my_module, 
node.title AS node_title, 
image_attach.iid AS image_attach_iid, 
event.event_start AS event_event_start, 
event.has_time AS event_has_time, 
location.lid AS location_lid, 
node_revisions.teaser AS node_revisions_teaser, 
node_revisions.format AS node_revisions_format, 
node.vid AS node_vid FROM node node  
LEFT JOIN event event ON node.nid = event.nid 
LEFT JOIN flag_content my_module ON node.nid = my_module.content_id 
LEFT JOIN image_attach image_attach ON node.nid = image_attach.nid 
LEFT JOIN location_instance location_instance ON node.vid = location_instance.vid 
LEFT JOIN location location ON location_instance.lid = location.lid 
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid 
WHERE (node.type in ('event')) AND (node.status <> 0) AND (UNIX_TIMESTAMP(event.event_start) >= 1233464400) 
GROUP BY node_vid, nid, boboforce_flagimage_fid, node_title, image_attach_iid, event_event_start, event_has_time, location_lid, node_revisions_teaser, node_revisions_format 
ORDER BY event_event_start DESC

This issue is solved 100% for me when I change the block above to the following:

    if ($has_aggregate || $this->groupby) {
      $groupby = "GROUP BY " . implode(', ', array_unique($this->groupby)) . "\n";
      if ($this->having) {
        $having = $this->condition_sql('having');
      }
    }

And here's the SQL that that generates (expected):

SELECT node.nid AS nid, 
my_module.fid AS my_module, 
node.title AS node_title, 
image_attach.iid AS image_attach_iid, 
event.event_start AS event_event_start, 
event.has_time AS event_has_time, 
location.lid AS location_lid, 
node_revisions.teaser AS node_revisions_teaser, 
node_revisions.format AS node_revisions_format, 
node.vid AS node_vid FROM node node  
LEFT JOIN event event ON node.nid = event.nid 
LEFT JOIN flag_content my_module ON node.nid = my_module.content_id 
LEFT JOIN image_attach image_attach ON node.nid = image_attach.nid 
LEFT JOIN location_instance location_instance ON node.vid = location_instance.vid 
LEFT JOIN location location ON location_instance.lid = location.lid 
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid 
WHERE (node.type in ('event')) AND (node.status <> 0) AND (UNIX_TIMESTAMP(event.event_start) >= 1233464400) 
GROUP BY node_vid
ORDER BY event_event_start DESC

I'm confused about this behavior, and i'm not sure if this is a bug or whether my lack of in-depth SQL knowledge is cropping up.
Why are the fields in $non_aggregates getting added to the GROUP BY clause?
Can anyone explain?

In any case, I'm attaching a diff in case anyone runs into this issue and wants to solve it, however temporarily or incorrectly.

Files: 
CommentFileSizeAuthor
views_group_by_hackaround.diff696 bytesaaronbauman

Comments

merlinofchaos’s picture

Status: Active » Closed (won't fix)

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.

aaronbauman’s picture

merlin:
And, as far as I can tell, there's no workaround that doesn't involve hacking the views core?
In other words, even though I use mysql exclusively, which doesn't mind my SQL query, there's no way for me to override this behavior?

thanks for the quick response,
/a

merlinofchaos’s picture

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

andreiashu’s picture

Category: bug » support
Status: Closed (won't fix) » Active

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 ?

andreiashu’s picture

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 :)

andreiashu’s picture

Status: Active » Closed (won't fix)

setting the previous status

whitelancer’s picture

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";

bwynants’s picture

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....

mdixoncm’s picture

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.

bendenoz’s picture

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.

bwynants’s picture

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

mixman’s picture

Category: support » bug
Status: Closed (won't fix) » Needs work

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...

lotyrin’s picture

Version: 6.x-2.3 » 6.x-2.x-dev
Category: bug » feature
Status: Needs work » Closed (won't fix)

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.

Luciuz’s picture

#10 does not working on D7?

Luciuz’s picture

Status: Closed (won't fix) » Active

sup

dasjo’s picture

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);
    }
acontia’s picture

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-...

HansKuiters’s picture

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');
  }
}
dahousecat’s picture

#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!

-enzo-’s picture

#17 works perfectly