In the last few weeks I tried to make the "group by" clause work properly, but after applying patches and upgrades I'm still unable to build the proper query by UI.
For what I understand what is not working in the sql is that the field on which I'm doing the SUM is also in the GROUP BY clause so equal numbers are summed but different are not.
So now I'm trying to modify this last bit by hand. I was hoping that by intercepting $query with hook_views_query_alter() I could be able to take the GROUP BY clause, strip out the unnecessary field, and let it go back into the stream.
The problem is that I can't see how to do that, the $query object I find has no Group By clause. Where should I look?
Comment | File | Size | Author |
---|---|---|---|
#22 | views-enable_groupby_in_query-1270064-22.patch | 615 bytes | Andrew Answer |
|
Comments
Comment #1
merlinofchaos CreditAttribution: merlinofchaos commentedMaybe you're experiencing the issue where CCK has not yet upgraded to be able to support GROUP BY properly? Assuming "the field" you refer to is a CCK field, that is. See #695298: Allow CCK to work with GROUP BY support in Views
And I don't know why the query object wouldn't have a group by clause. It should.
Comment #2
keyiyek CreditAttribution: keyiyek commentedThanks for the reply.
I applied your patch from the linked post (the second @ #8) to cck 2.8, 2.9 and 3.dev also. But I couldn't manage to make it work properly in any case (I'm the poster of #17). You can read another part of the story at http://drupal.org/node/1260530
Now, back to the issue in the title the query as shown in the views preview is
from my little understanding of sql that last line should not be there.
so I call hook_views_query_alter() and in the attachment you can find the dmp($query)
I don't understand what I should modify, I was expecting something referring to the last couple of lines of the query quoted above.
Any suggestion?
btw since my question is about modifying sql manually I don't think that suggesting a workaround with the ui qualify for "fixing" the problem
Comment #3
keyiyek CreditAttribution: keyiyek commentedComment #4
dawehnerA question, might be silly, but did you cleared the cache before re-running the view?
You would have to clear the views cache on admin/build/views/tools.
Comment #5
keyiyek CreditAttribution: keyiyek commentedalready done. as I wrote here http://drupal.org/node/1260530#comment-4957682 the problem seems a duplication of the field on which I make the sum. Trying to figure out why
Comment #6
bohz CreditAttribution: bohz commentedI have a very similar issue after updating 6.x-3.x-dev from the Feb 25 version (datestamp: 1298620633).
With previous version i built a view returning the following query:
With the current version, the last line
GROUP BY nid
is gone and the view displays duplicates.Please note that I did not use the 'Group by' feature at all.
I also noticed an API version change, from 3.0-alpha1 to 3.0.
I am also interested in modifying the query in order to "restore" the
GROUP BY nid
line.Any hint is appreciated,
THanks!
Comment #7
Gribnif CreditAttribution: Gribnif commentedThis seems to be the same as #1284982: no "group by" clause being added for custom views field handler. It refers to the D7 version but is, I believe, the same bug.
Here is my attempt at a patch against 6.x-3.0-alpha4. It seems to work for me, in the case where a view handler is adding a GROUP BY and the UI does not have one. I believe it will also fix the case where just the UI has one.
Comment #8
Gribnif CreditAttribution: Gribnif commentedI have revised my patch, since the previous version could produce an empty GROUP BY clause when DISTINCT was present in the query.
Comment #9
blisteringherb CreditAttribution: blisteringherb commentedI applied the last patch, and still ran into the empty GROUP BY clause when DISTINCT is present in the query. This patch is against 6.x-3.0-rc3.
Comment #10
markdorisonComment #11
alex.skrypnykTo summarize, altering query to add a groupby should be done in hook_views_query_alter and it will work only if patch from #9 is applied. This has been tested on 7.x-3.1.
Comment #12
Kars-T CreditAttribution: Kars-T commentedDear fellow Drupal enthusiasts,
this issue is now lasting for a very long time in the issue queue and was unfortunately never solved. As Drupal is a open source project everyone is helping on voluntary basis. So that this is was not solved is nothing personal and means no harm. But perhaps no one had time to deal with this issue, maybe it is too complex or did not pose the problem comprehensible.
But this issue is not the only one. There are thousands of issues on Drupal.org that have never been worked on or could not be processed. This means that we are building a wave that is unmanageable and just a problem for the Drupal project as a whole. Please help us keep the issue queue smaller and more manageable.
Please read again, "Making an issue report" and see if you can improve the issue. Test the problem with the current Core and modules. Maybe the problem doesn't exist anymore, is a duplicate or has even been solved within this issue but never closed.
Help can also be found for it on IRC and in the user groups.
In order this issue no longer remains, I put this issue to "fixed".
If there is new information, please re-open the issue.
Comment #13
valderama CreditAttribution: valderama commentedDear Karsten,
this Issue is still valid - at least in Drupal 7 with Views 7.x-3.5 (and also in dev).
I can confirm the patch provided in #9 is working. I fully verify comment #11. I was also trying to add a group-by in hook_query_alter but it did not work until I added the changes proposed in #9.
With the hope to fix this issue for the broader community as well I made a new patch against 7.x-3.x-dev.
Let finally fix this issue!
Thanks + best,
Walter
Comment #15
valderama CreditAttribution: valderama commentedchanging version for the test bot
Comment #16
valderama CreditAttribution: valderama commented#13: views-enable_groupby_in_query-1270064-13.patch queued for re-testing.
Comment #17
Kars-T CreditAttribution: Kars-T commentedI set this to feature request than :)
Comment #18
dawehnerIf we really change any behavior, we need tests for existing and expected new behavior.
Comment #19
Prine CreditAttribution: Prine commented#13 patch then #11 implementation worked for me. Thanks!
Comment #20
awolfey CreditAttribution: awolfey commentedI want to bring your attention to #1615438: Search terms: nodes dont appear when using multiple arguments, which also deals with the same piece of code.
Comment #21
Chris Matthews CreditAttribution: Chris Matthews as a volunteer commentedThe 6 year old patch to views_plugin_query_default.inc does not apply to the latest views 7.x-3.x-dev and if still applicable needs to be rerolled.
Comment #22
Andrew Answer CreditAttribution: Andrew Answer as a volunteer commentedPatch rerolled.