just like the title says-- group by only works when each value of a multivalue cck field is identical. wierd.

Steps to reproduce:

1. add a cck integer field to page or story
2. set it to be unlimited
3. create a test node with several line items for that integer field all the same (ie 3 values all set to '1').
4. create view using current 6-3 dev
5. turn on grouping
6. add node title field
7. add the cck integer field and select the 'sum' function (count doesn't work either)
8. preview the view-- the math will be correct
9. edit the node, set one of the values to be different
10. preview the view again-- the node will be listed once for each individual value of the cck integer field

You can flip back and forth repeatedly by just making all values the same or not. When the same, the field is summed and the math is correct.

When I examine the query, the last groupby item is the cck field, but its grouping by node_data_field_{fieldname}_field_integers_value_1-- i'm thinking the 'value_1' might be causing the problem, but I really don't know enough to say.

CommentFileSizeAuthor
#2 views_export_903768.txt4.72 KBWorldFallz
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

WorldFallz’s picture

And here's the query:

<?php
SELECT node.title AS node_title,
node.nid AS nid,
SUM(node_data_field_integers.field_integers_value) AS node_data_field_integers_field_integers_value,
node_data_field_integers.field_integers_value AS node_data_field_integers_field_integers_value_1
 FROM node node 
 LEFT JOIN content_field_integers node_data_field_integers ON node.vid = node_data_field_integers.vid
 WHERE (node.type in ('story')) AND (node_data_field_integers.field_integers_value > 0)
 GROUP BY node_title, nid, node_data_field_integers_field_integers_value_1
?>
WorldFallz’s picture

FileSize
4.72 KB

And an export of the view.

WorldFallz’s picture

Actually, when they're not unique each node is not listed once for the number of items there are-- it's listed once for the number of unique items there are. If you have 4 values and 3 are the same it will list the node twice-- once with the one value and once with the sum of the 3 values that are the same.

dawehner’s picture

Did you tryed to remove the node title?

WorldFallz’s picture

No but I just did it now and the math is still wrong-- and I can't figure out what it's doing, lol.

dawehner’s picture

What are you trying to do?

You are grouping by nid and sum it up and groupb by the value? This confused me.

Do you have the current cck version, too?

WorldFallz’s picture

yep I'm using the current dev versions of both views and cck.

I'm just trying to sum all the values of a multivalue cck integer field per node. So if i have an integer field on node 1 with 3 lvalues (2, 2, 2) I should get 6. This works. If the values are not all the same (ie 1, 2, 2 or 1 ,2, 3 etc) it doesn't work. It sums the matching values. For example, in the 1-2-2 example, I would get one views row for the '1' and another for '4' (the two 2s added together).

braindrift’s picture

Title: groupby on multivalue cck integer fields only works when all values are the same » groupby on cck fields only works when all values are the same

Same here!

This issue is not limited to multivalue fields. The grouping function groups generally only by same values. This is due to the generated query. The field that should be aggregated appears also in the GROUP BY section of the query. That should not be like this.

For sombody like dereine this should be an easy to fix ;)

Thanks.

braindrift’s picture

A simple view like:

$view = new view;
$view->name = 'test_group_by';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = '3.0-alpha1';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'type' => 'type',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'type' => array(
    'sortable' => 0,
    'align' => '',
    'separator' => '',
  ),
);
$handler->display->display_options['style_options']['override'] = 1;
$handler->display->display_options['style_options']['sticky'] = 0;
/* Field: Node: Type */
$handler->display->display_options['fields']['type']['id'] = 'type';
$handler->display->display_options['fields']['type']['table'] = 'node';
$handler->display->display_options['fields']['type']['field'] = 'type';
$handler->display->display_options['fields']['type']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['type']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['type']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['type']['alter']['trim'] = 0;
$handler->display->display_options['fields']['type']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['type']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['type']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['type']['alter']['html'] = 0;
$handler->display->display_options['fields']['type']['hide_empty'] = 0;
$handler->display->display_options['fields']['type']['empty_zero'] = 0;
$handler->display->display_options['fields']['type']['link_to_node'] = 0;
$handler->display->display_options['fields']['type']['machine_name'] = 0;
/* Field: Content: Value (field_integer) */
$handler->display->display_options['fields']['field_integer_value']['id'] = 'field_integer_value';
$handler->display->display_options['fields']['field_integer_value']['table'] = 'node_data_field_integer';
$handler->display->display_options['fields']['field_integer_value']['field'] = 'field_integer_value';
$handler->display->display_options['fields']['field_integer_value']['group_type'] = 'sum';
$handler->display->display_options['fields']['field_integer_value']['label'] = 'Value';
$handler->display->display_options['fields']['field_integer_value']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_integer_value']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_integer_value']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['field_integer_value']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_integer_value']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_integer_value']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_integer_value']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_integer_value']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_integer_value']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_integer_value']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_integer_value']['link_to_node'] = 0;
/* Field: Node: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['group_type'] = 'count';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
/* Filter: Node: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'test_content_type' => 'test_content_type',
);

generates SQL like:

SELECT node.type AS node_type,
SUM(node_data_field_integer.field_integer_value) AS node_data_field_integer_field_integer_value,
node_data_field_integer.field_integer_value AS node_data_field_integer_field_integer_value_1,
COUNT(node.title) AS node_title
 FROM node node 
 LEFT JOIN content_type_test_content_type node_data_field_integer ON node.vid = node_data_field_integer.vid
 WHERE node.type in ('test_content_type')
 GROUP BY node_type, node_data_field_integer_field_integer_value_1

The right SQL would be:

SELECT node.type AS node_type,
SUM(node_data_field_integer.field_integer_value) AS node_data_field_integer_field_integer_value,
node_data_field_integer.field_integer_value AS node_data_field_integer_field_integer_value_1,
COUNT(node.title) AS node_title
 FROM node node 
 LEFT JOIN content_type_test_content_type node_data_field_integer ON node.vid = node_data_field_integer.vid
 WHERE node.type in ('test_content_type')
 GROUP BY node_type
WorldFallz’s picture

Fwiw, in the meantime, I've figured out how to use views_query_alter to fix it:

function custom_views_query_alter(&$view, &$query) {
  if($view->name == 'name_of_view'){
    unset($query->fields["node_data_field_fieldname_field_fieldname_value_1"]);
  }
}

Where you replace 'fieldname' with whatever field you're trying to aggregate, 'name_of_view' with whatever your view is named, and 'custom' with whatever you name your custom module..

WorldFallz’s picture

I don't know enough about views code to know where to look yet, but there's definitely something wrong with the formation of the 'groupby' part of the query. There's lots of stuff being added there that I've not put there via the UI (ie node_type, node_vid, field values, etc) and that is messing things up. So far I've been able to fix my views by unsetting the unnecessary groupby variables with query alter, but I'm going to see if I can try and find where the problem is with the code.

Another related question-- when I do a dsm($query) in views_query_alter the 'groupby' array is blank-- so I can't seem to get at those items except by unsetting them in $query->fields. Is that correct behavior? Shouldn't a query_alter allow me to access the entire query?

dawehner’s picture

If you use groupby in sql you have to add everything to groupby which isn't counted/summed up. This is part of sql. I found http://ss64.com/ora/select_group.html

Columns in the SELECT clause which are not in the GROUP BY clause must be part of an AGGREGATE function.
WorldFallz’s picture

That's definitely what that page says, but since I'm out of my depth here I can only give my empirical results. I have to remove all those extra groupings to get the query to sum properly. Maybe it's the order of the items in the groupby clause?

I'll see if I can play around with that some... but I'm not sure how I can change the order if $query->groupby is empty in hook_query_alter.

dawehner’s picture

I think instead of removing the groupby part node_data_field_integer.field_integer_value AS node_data_field_integer_field_integer_value_1, should be removed from the query. This is what you do on your custom code.

I guess this part is a bug in the cck field handler, but i'm not sure here.

braindrift’s picture

@dereine Your are right, I have not seen the additional field in the SELECT section.

Thanks

Will White’s picture

I'm seeing this extra field as well. Temporary solution has been to manually remove it with hook_survey_views_query_alter().

function hook_survey_views_query_alter(&$view, &$query) {
  if ($view->name == 'view_name') {
    unset($query->fields['node_data_field_field_name_name_name_1']);
  }
}
sreher’s picture

The same here.
Workaround works also. Thanks.
Is there a better solution by now?

iamjon’s picture

Status: Active » Closed (fixed)

sreher,
I talked to merlinofchaos about this on irc, he doesn't think there is a better solution currently. using hook_query_alter is a great solution.
If you want to write up a patch or custom module that will always be appreciated.

Closing this as a suitable workaround was provided.

un11imig’s picture

Status: Closed (fixed) » Active

Hi,

I'm experiencing the same issue documented here. As I see there is not a solution yet explained here. Any clue to solve this not using the unset?.

Regards,

MustangGB’s picture

Issue summary: View changes
Status: Active » Closed (won't fix)