Problem/Motivation

I'm not certain if this is a documentation issue, or if Views is just busted, but the following code does not work:


function my_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'course_listing') {
    $title_alias = 'alias_to_content_table.title';
    $query->addGroupBy($title_alias);
  }
}

I can see in the debugger that the code executes for the view of that ID, and title field is correctly named. But addGroupBy() is simply ignored when the query object actually renders out the query. I'm using code here because using the Views UI's Aggregation settings creates a monster GROUP BY that makes the query useless.

What needs to be true so that calling $query->addGroupBy($title_alias); will actually lead to a query with a GROUP BY? I can't find this documented anywhere, and I can't tell if I need to do more here, or if Views should render the GROUP BY, but is not.

Steps to reproduce

Proposed resolution

TBD

Remaining tasks

Confirm the problem on a supported version of Drupal.

User interface changes

API changes

Data model changes

Release notes snippet

Comments

Torenware created an issue. See original summary.

Torenware’s picture

Torenware commented

I've also put this up on Drupal Answers, if that's a more appropriate place:

Deduping Views by forcing Views to add a simple GROUP BY to a query. This will also explain a bit more as to why I need to do this.

Torenware’s picture

Torenware commented

Part of the problem here is described here: http://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by. Apparently:

SQL92 requires that all columns (except aggregates) in the select clause is part of the group by clause. SQL99 loosens this restriction a bit and states that all columns in the select clause must be functionally dependent of the group by clause.

I'm not sure how to dedupe these extra rows, but it would appear that the GROUP BY approach is likely impossible :-(

Torenware’s picture

Torenware commented

I don't think there's a general solution of this, but you can get around some of the limitations of GROUP BY through the use of correlated sub-queries.

I was trying to use MIN(entity_id) = n.entity_id as a way of choosing a single row of a group -- a de-duping problem. This cannot work unless you don't use ORDER BY, due to the limits of the standard. But using a sub-query removes the need to JOIN with the additional table:

function my_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {

  if ($view->id() == 'course_listing') {
    $where_clause = <<<WHERE
    (
      SELECT COUNT(*)
      FROM {node__field_session_course} link_to_course
      WHERE node_field_data.nid = link_to_course.entity_id
    ) > 0
WHERE;

    $query->addWhereExpression(0, $where_clause);
  }
}

This will make sure that there is at least one dependent row (our "link_to_course" table) for each course we display. By not using a JOIN, we solve our de-duping problem.

Version: 8.0.x-dev » 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.9 was released on September 7 and is the final bugfix release for the Drupal 8.1.x series. Drupal 8.1.x will not receive any further development aside from security fixes. Drupal 8.2.0-rc1 is now available and sites should prepare to upgrade to 8.2.0.

Bug reports should be targeted against the 8.2.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.6 was released on February 1, 2017 and is the final full bugfix release for the Drupal 8.2.x series. Drupal 8.2.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.3.0 on April 5, 2017. (Drupal 8.3.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.3.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.6 was released on August 2, 2017 and is the final full bugfix release for the Drupal 8.3.x series. Drupal 8.3.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.4.0 on October 4, 2017. (Drupal 8.4.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.4.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

anpolimus’s picture

anpolimus
Primary language Ukrainian
Location Kyiv
commented

Expecting the same issue at my code.

anpolimus’s picture

anpolimus
Primary language Ukrainian
Location Kyiv
commented

I've found how to fix this issue.

Just use 3rd variable at the addField method, called $params and group by would be affected.

Here is how I've done it at my hook_views_query_alter():

    $query->addField(NULL, 'SUM(' . implode('+', $match_statement) . ')', 'mtch', ['function' => 'groupby']);
    $query->addGroupBy('nid');
anpolimus’s picture

anpolimus
Primary language Ukrainian
Location Kyiv
commented

Another solution is to add query_tag and use it at the hook_query_TAG_alter().
In this case you are altering SQL query on low level without any additional subqueries from views sql AddField method.

function your_module_views_query_alter() {
....
    $query->addField(NULL, 'SUM(' . implode('+', $match_statement) . ')', 'rmatch');
    $query->addTag('bridge_match');
}

/**
 * Implements hook_query_TAG_alter().
 */
function your_module_query_bridge_match_alter(AlterableInterface $query) {
  $query->groupBy('nid');
  $query->orderBy('rmatch', 'DESC');
}

This approach seems to be more stable and flexible.

anpolimus’s picture

anpolimus
Primary language Ukrainian
Location Kyiv
commented
Status: Active » Closed (works as designed)
robert.duplock’s picture

robert.duplock commented

If anyone is experiencing this problem in a custom views filter - I adapted the above solution to fix the groupBy (E.g. it didn't actually groupBy unless I did the following:

    $this->query->addField(NULL, 'field_name_example', '', ['function' => 'groupby']);
    $this->query->addGroupBy('table_name_example.field_name_example');

¯\_(ツ)_/¯

rcodina’s picture

rcodina
Primary language Catalan
Location Barcelona
commented

@robert.duplock Thank you so much!

chamilsanjeewa’s picture

chamilsanjeewa commented

@robert.duplock It works, Thank you

ihor_allin’s picture

ihor_allin commented

#13 worked for me. Thanx @robert.duplock

liquidcms’s picture

liquidcms commented
Status: Closed (works as designed) » Active

Not sure what @anpolimus solutions are about.. they use a variable $match_statement but no explanation of what that is.. also not sure why he marked it as closed.

I have used the solution in #13 on numerous views with a bit of trial and error on which field to group on (although it is obvious from the view; not always obvious in the groupby). I think having an Sort added to the view generally messes the solution in #13 up though.

quietone’s picture

quietone commented
Status: Active » Postponed (maintainer needs more info)

Drupal 8 is no longer supported. Is this reproducible on a supported release?

liquidcms’s picture

liquidcms commented

my post in #17 was re: D9.3. Pretty sure little has changed from D8 to D10 regarding Views' battle with duplicate results.

quietone’s picture

quietone commented
Version: 8.4.x-dev » 9.3.x-dev
Issue summary: View changes
Status: Postponed (maintainer needs more info) » Active
Issue tags: +Bug Smash Initiative

@liquidcms, thanks. It helps us all if you update the issue meta data as well. This is currently filed on an unsupported version of Drupal, 8.4.x.

I am changing the version to 9.5.x.

Version: 9.3.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 11.x-dev » main

Drupal core is now using the main branch as the primary development branch. New developments and disruptive changes should now be targeted to the main branch.

Read more in the announcement.