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?

Files: 
CommentFileSizeAuthor
#13 views-enable_groupby_in_query-1270064-13.patch623 bytesvalderama
PASSED: [[SimpleTest]]: [MySQL] 1,603 pass(es).
[ View ]
#9 views-enable_groupby_in_query-1270064-9.patch633 bytesblisteringherb
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-enable_groupby_in_query-1270064-9.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#8 1270064_views_plugin_query_default_1.patch1.12 KBGribnif
FAILED: [[SimpleTest]]: [MySQL] 561 pass(es), 47 fail(s), and 70 exception(s).
[ View ]
#7 1270064_views_plugin_query_default.patch1.35 KBGribnif
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1270064_views_plugin_query_default.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#2 querydump.png76.45 KBkeyiyek

Comments

Status:Active» Fixed

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.

StatusFileSize
new76.45 KB

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_nid
  AS 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

Status:Fixed» Active

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.

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

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 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!

StatusFileSize
new1.35 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1270064_views_plugin_query_default.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]

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.

StatusFileSize
new1.12 KB
FAILED: [[SimpleTest]]: [MySQL] 561 pass(es), 47 fail(s), and 70 exception(s).
[ View ]

I have revised my patch, since the previous version could produce an empty GROUP BY clause when DISTINCT was present in the query.

Version:6.x-3.x-dev» 6.x-3.0-rc3
StatusFileSize
new633 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-enable_groupby_in_query-1270064-9.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]

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.

Status:Active» Needs review

Title:Modify SQL of a view (GROUP BY)Modify SQL of a view (GROUP BY) through UI and programmatically

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');
  }
}

Status:Needs review» Fixed

Dear 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.

Status:Fixed» Needs review
StatusFileSize
new623 bytes
PASSED: [[SimpleTest]]: [MySQL] 1,603 pass(es).
[ View ]

Dear 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

Status:Needs review» Needs work

The last submitted patch, views-enable_groupby_in_query-1270064-13.patch, failed testing.

Version:6.x-3.0-rc3» 7.x-3.x-dev
Status:Needs work» Needs review

changing version for the test bot

Category:support» feature

I set this to feature request than :)

Issue tags:+Needs tests

If we really change any behavior, we need tests for existing and expected new behavior.

#13 patch then #11 implementation worked for me. Thanks!

I 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.