Posted by keyiyek on September 5, 2011 at 3:04pm
8 followers
| Project: | Views |
| Version: | 6.x-3.0-rc3 |
| Component: | Code |
| Category: | support request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Issue Summary
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?
Comments
#1
Maybe 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.
#2
Thanks 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
SELECT node_data_field_tipo.field_tipo_nidAS node_data_field_tipo_field_tipo_nid,
MAX(node.nid) AS nid, node_data_field_luogo.field_luogo_nid AS node_data_field_luogo_field_luogo_nid,
SUM(node_data_field_ball_num.field_ball_num_value) AS node_data_field_ball_num_field_ball_num_value, node_data_field_ball_num.field_ball_num_value AS node_data_field_ball_num_field_ball_num_value_1
FROM node node
LEFT JOIN content_field_tipo node_data_field_tipo ON node.vid = node_data_field_tipo.vid
LEFT JOIN content_type_ingresso node_data_field_luogo ON node.vid = node_data_field_luogo.vid
LEFT JOIN content_field_ball_num node_data_field_ball_num ON node.vid = node_data_field_ball_num.vid
WHERE node.type in ('ingresso')
GROUP BY node_data_field_tipo_field_tipo_nid, node_data_field_luogo_field_luogo_nid, node_data_field_ball_num_field_ball_num_value_1
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
#3
#4
A 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.
#5
already 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
#6
I 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:
SELECT DISTINCT(node.nid) AS nid,node_node.type AS node_node_type,
node_node.nid AS node_node_nid,
node_node.vid AS node_node_vid
FROM node node
LEFT JOIN content_field_rif_sezione node2 ON node.nid = node2.field_rif_sezione_nid
LEFT JOIN node node_node ON node2.nid = node_node.nid
LEFT JOIN term_node term_node_value_0 ON node_node.vid = term_node_value_0.vid AND term_node_value_0.tid = 888
WHERE (node.status = 1) AND (node.type in ('lab')) AND (node.language in ('***CURRENT_LANGUAGE***')) AND (term_node_value_0.tid = 888)
GROUP BY nid
With the current version, the last line
GROUP BY nidis 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 nidline.Any hint is appreciated,
THanks!
#7
This 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.
#8
I have revised my patch, since the previous version could produce an empty GROUP BY clause when DISTINCT was present in the query.
#9
I 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.
#10
#11
To 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.
function MODULENAME_views_query_alter (&$view, &$query) {// Filter view by name.
if ($view->name == 'your_view_name'){
// Use add_groupby('field_name_as_appears_in_query')
$query->add_groupby('node_taxonomy_index_nid');
}
}