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

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.

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.

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!

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.

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

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.

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

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.

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

changing version for the test bot

#13: views-enable_groupby_in_query-1270064-13.patch queued for re-testing.

I set this to feature request than :)

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.

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

Checking patch plugins/views_plugin_query_default.inc...
error: while searching for:

    list($non_aggregates) = $this->compile_fields($fields_array, $query);

    if (count($this->having)) {
      $this->has_aggregate = TRUE;
    }
    if ($this->has_aggregate && (!empty($this->groupby) || !empty($non_aggregates))) {

error: patch failed: plugins/views_plugin_query_default.inc:1319
error: plugins/views_plugin_query_default.inc: patch does not apply

Patch rerolled.