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.
Comment | File | Size | Author |
---|---|---|---|
#2 | views_export_903768.txt | 4.72 KB | WorldFallz |
Comments
Comment #1
WorldFallz CreditAttribution: WorldFallz commentedAnd here's the query:
Comment #2
WorldFallz CreditAttribution: WorldFallz commentedAnd an export of the view.
Comment #3
WorldFallz CreditAttribution: WorldFallz commentedActually, 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.
Comment #4
dawehnerDid you tryed to remove the node title?
Comment #5
WorldFallz CreditAttribution: WorldFallz commentedNo but I just did it now and the math is still wrong-- and I can't figure out what it's doing, lol.
Comment #6
dawehnerWhat 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?
Comment #7
WorldFallz CreditAttribution: WorldFallz commentedyep 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).
Comment #8
braindrift CreditAttribution: braindrift commentedSame 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.
Comment #9
braindrift CreditAttribution: braindrift commentedA simple view like:
generates SQL like:
The right SQL would be:
Comment #10
WorldFallz CreditAttribution: WorldFallz commentedFwiw, in the meantime, I've figured out how to use views_query_alter to fix it:
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..
Comment #11
WorldFallz CreditAttribution: WorldFallz commentedI 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?
Comment #12
dawehnerIf 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
Comment #13
WorldFallz CreditAttribution: WorldFallz commentedThat'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.
Comment #14
dawehnerI 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.
Comment #15
braindrift CreditAttribution: braindrift commented@dereine Your are right, I have not seen the additional field in the SELECT section.
Thanks
Comment #16
Will White CreditAttribution: Will White commentedI'm seeing this extra field as well. Temporary solution has been to manually remove it with
hook_survey_views_query_alter()
.Comment #17
sreher CreditAttribution: sreher commentedThe same here.
Workaround works also. Thanks.
Is there a better solution by now?
Comment #18
iamjon CreditAttribution: iamjon commentedsreher,
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.
Comment #19
un11imig CreditAttribution: un11imig commentedHi,
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,
Comment #20
MustangGB CreditAttribution: MustangGB commented