Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
By using hook_views_query_substitutions in the views_grouby.module I was able to rewrite finished SQL statement. The first thing that is rewriten is GROUP BY clause so that only the fields that were selected go after the GROUP BY. In views_groupby_handler_field_grooupfield.ind options for Sum and Avg functions are added. Since those values are stored in the COUNT variable, Views will build a query with COUNT aggregation function. In the above hook I rewrote COUNT (if needed) into SUM or AVG if COUNT variable held the appropriate value.
This solved quite a few of my issues, hope it helps.
Comment | File | Size | Author |
---|---|---|---|
#18 | views_groupby_PHP53.patch | 3.92 KB | DevElCuy |
#17 | views_groupby_PHP53.patch | 3.93 KB | DevElCuy |
#16 | views_groupby_PHP53.patch | 4.61 KB | florian.cathala |
#6 | Capture-agg-setting.JPG | 102.42 KB | bakr |
#5 | views_2.6.JPG | 204.2 KB | kartelusm |
Comments
Comment #1
singularoExcellent, makes the group by that much more usable!
Comment #2
bakr CreditAttribution: bakr commentedMy Evaluation:
I am using:
* view-group-by 6.x-1.0-rc2
* cck 2.6
* views 2.8
Before using the above patch:
- Aggregation worked only for field added from the "Node" group.
- Aggregation DID NOT work once any field was added from the "Content" group.
After patching:
- I just love it, it works on both cases.
But ...
No more I can use views-calc to show the the total of an aggregated column.
I am not sure is it a bug in v-calc or in v-aggregate, or they do not seem to work with each other.
Comment #3
bakr CreditAttribution: bakr commentedOops, I missed the the following screenshot of the table views calc settings under.
Comment #4
kartelusm CreditAttribution: kartelusm commentedI haven't been using views-calc module, but I'll get it, look into the problem and hopefully find the solution.
Could you post SQL Aggregation: Group By Fields screen?
Comment #5
kartelusm CreditAttribution: kartelusm commentedI tried views_calc out and I believe that the problem is not caused by the patch, but rather by the new version of views. The thing is that "Total count" line is not displayed even when views_groupby module is disabled. It should work with views 2.6. If you try it, please tell me if that solves your issue.
Here are some pictures (without views_groupby)
Comment #6
bakr CreditAttribution: bakr commentedThanks Nikola,
Please find the attached.
Best Regards
Comment #7
bakr CreditAttribution: bakr commentedBy the way,
Both modules are having their own strength area
:views calc
:views group by
But the v-g-b module is having a stronger shine in cross-referenced queries, that is a good muscle, and mind you, this is a critical edge for the drupal platform, to be able to use it in future in enterprise envinronment.
Quote from views-Cals page:
The complex queries created are not relationship-safe yet, so you can't do accurate calculations on fields coming from relationships.
Comment #8
bakr CreditAttribution: bakr commentedBased on above, and rationally enough:
At least, I confirm there is no harm of the above patch. (I really liked it, especially for building charts :-)
I shall be building more complex views reports that shall rely on more relationships, and shall exploit the added (sum,count,average,min,max) functionality.
As such, shall report back my finding with the stability of the above patch, which I hope it shall be eventually committed into the first release.
Regards
Bakr
Comment #9
redben CreditAttribution: redben commentedGreat !
Would be good if we could have COUNT and SUM on the same query
Comment #10
sabroff CreditAttribution: sabroff commentedI was having problems with the views_groupby module so I tried the patch by kartelusm.
But it left my GROUP BY without any fields. "GROUP BY ." was all I got.
I am very new to Drupal and almost as new to the forum so I may not be reporting my problems clearly enough.
Before the patch I had 'extra' fields in the group by causing problems with the query.
After the patch, I have none.
We are using views version = "6.x-2.6" and the views_groupby version = "6.x-1.0-rc2".
Comment #11
kartelusm CreditAttribution: kartelusm commentedI don't think I will be able to look into it before Tuesday (Easter holidays and all), but just to see if I understand you correctly: all the fields after the GROUP BY statement are gone? If that is the case, you could post some screen shots of views page with the form from this module and we'll see if we can solve the problem. One of the features of the patch is to remove the extra fields from GROUP BY, and as far as I know, it works fine.
As for the versions, I wrote the patch using the same versions you mentioned, but tried it since with Views 2.8 and it works.
Comment #12
christiaan_ CreditAttribution: christiaan_ commentedThank you so much for this patch - I was using the latest dev version - and had no joy. Then went back to RC-2 and applied the patch. Now everything is working perfectly. Been looking for a solution for a long time. Thanks again.
Comment #13
cashwilliams CreditAttribution: cashwilliams commentedThis is a great patch! Does exactly what I needed and saves me from having to write a custom views handler!
Thanks!
Comment #14
chrisirhc CreditAttribution: chrisirhc commentedI've posted an alternative at #669070: The rest of Aggregate Functions (AVG SUM MIN MAX) that doesn't require as much tweaking. Could anyone test it for me?
Comment #15
Tokoh CreditAttribution: Tokoh commentedHi, I know this is an old thread but I have the same problem with views calc and group by as you, Bakr, had in February. The suggestion from kartelusm was to use views 2.6 and the ask to say if this fixed the problem. Did it fix the problem?
Comment #16
florian.cathala CreditAttribution: florian.cathala commentedHi,
The patch is not PHP 5.3 compliant, here is the warning :
Here is the modified patch which won't trigger any warning.
Thank you,
Florian
Comment #17
DevElCuy CreditAttribution: DevElCuy commentedThere was a typo in patch at 16 (by @florian.cathala), attached is fixed version.
Comment #18
DevElCuy CreditAttribution: DevElCuy commentedthis time following coding standards.
Comment #20
shyam541 CreditAttribution: shyam541 commentedHi,
Is this patch commited....atleast to the development brach.
Thanks
Shyam
Comment #21
arithmetric CreditAttribution: arithmetric commentedA similar approach to this functionality was developed in #669070: The rest of Aggregate Functions (AVG SUM MIN MAX) and is now committed to the development branch.