Group By not working due to vid

I don't fully understand the function query() in views_groupby_handler_field_groupfields.inc; but changing:

            // if ($key != $bfield) {
           //  unset($this->query->fields[$key]);
            // } 

to:

           //  if ($key != $bfield) {
             unset($this->query->fields[$key]);
           //  } 

seems to deal with the vid problem. Perhaps when the $bfield line was commented out, too the unset line was commented out by accident.

    /** Add all other fields into count, to make sure they do not mess up group_by **/
    if (is_array($this->query->fields)) {
      foreach ($this->query->fields as $key => $field) {
        if ( !in_array($key, $to_group) &&
             !in_array($key, $to_aggregate) ) {

             if ($key != $bfield) {
           //  unset($this->query->fields[$key]);
             }             
        } 
        else if (in_array($key, $to_aggregate)) {
            $this->query->fields[$key][$sql_func] = TRUE;
            $this->query->fields[$key]['alias'] = $key;
        }
      }
    }

I think the problem is the new VIEWS version (6.x-2.8)

in views/includes/query.inc line 977
$groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n";

it adds an "non_aggregate" array to the group by fields, so there were added nid and vid too

To the non_aggregate array are in this query-function added all fields, that have not attribute
$field['count'] or attribute $field['aggregate']

A pathc with a aggregate attribute for all fields ($this->query->fields[fields]->aggregate='no') would break the views patch for ANSI SQL compliance (e.g. with postgres) see http://drupal.org/node/385158#comment-1296924

But we can trick it if all results would be distinct, then a patch would do like:

--- views_groupby/handlers/views_groupby_handler_field_groupfields.inc.ORIG 2009-12-11 03:14:10.000000000 +0100
+++ views_groupby/handlers/views_groupby_handler_field_groupfields.inc 2009-12-11 06:27:53.000000000 +0100
@@ -261,6 +261,10 @@
$sz_to_group .= $field;
}
$this->query->add_groupby($sz_to_group);
+ if ($count) { // issue #385158: query->add_groupby & aggregate vs. nonaggregate fields und issue #651546: Group By not working due to vid
+ $this->query->distinct=TRUE;
+ $this->query->no_distinct='views_groupby'; //prevent views-behaviour to add nid(base_field) allways
+ }
}

$views_fields = $this->_get_views_fields($just_aliases=TRUE);

Re comment #3 -> we are facing the same problem, but with Views 2.6 (not 2.8)

We tried with Views Group By dev versions of 6 November and 22 November 2009

Was anyone able to solve this problem? For us it makes Views Group By completely unusable for the moment.

#3 works for me. Here's a patch, credit to neffets.

Same problem here with RC2 and Views 2.8. Will try this patch out!

I think the patch in #6 is a MySQL only solution, as it breaks on Postgres.

mineshaftgap: Could your issue be related to this Views issue? #506818: [Postgres] GROUP BY uses field names instead of aliases

Glenn,

I think that you are correct. Thanks for the input, unfortunately it seems like it is still an open issue, so for now I will be using hook_views_query_alter/hook_views_pre_render/etc or jumping out of Views for my needs.

Changing to needs review, hoping to get more eyes on this.

Patched worked beautifully for me. (mySQL)

Patch worked perfect for me to.

#6 works for me, too. (MySQL 5.0)

This patch worked well for me. However, I also had some trouble with having a date field set in the sort field which was populating the groupby array.

Thank you Glenn - that worked perfectly for me too =o)

#6 worked for me. I was banging my head trying to figure this out for a few hours. Thanks.

#6 works for me too. Please, make release with it!

This patch is working for me too. Marking it as RTBC.

#6 patch works for me too. Please, make release with it!

This patch was committed with this change: http://drupal.org/cvs?commit=484634

It's now part of the dev release (6.x-1.x).