Deduping Views by forcing Views to add a simple GROUP BY to a query

Question

In D8, I need a way to "dedupe" a view with a relationship. This involves a "Course" content type, and a "Sessions" entity (actually, a Commerce Product) that points back to the Course.

I've tried to create views that are based off the content type and related to the Session entity, and visa versa. In both cases, what I really want to do is to get a list of courses searched by both the attributes of the Course (let's say, type of course) and of the session (location).

Since Course :: Session is 1 :: N, we're getting N rows for each Course. This is not what we want. Using the Advanced settings has proved useless so far. Query Setting "Distinct" does not dudupe. The Field Format Setting "Grouping field" does not appear to trigger a GROUP BY in the query. And using Aggregation creates a monster GROUP BY that appears to pull in just about all the fields in the tables, with no way to remove the useless fields from the GROUP BY.

Here's what I'd like to do: use a simple GROUP BY (say, the title of the Course), and compare the entity ID of the sessions with the minimum entity_id of that group of sessions; if it's not equal, I want to exclude the row either in the WHERE clause or in a HAVING clause if it will work. I'm trying to modify the query via the views hook

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

The problem here is that while the code is definitely called, the call to addGroupBy() is ignored when the query is actually rendered out. Views thinks I didn't mean it :-)

What exactly do I need to clobber Views with to get it to add a GROUP BY statement? If I can get it to do that, I can insert a MIN field into the SELECT statement (see http://tech-tamer.com/drupal-7-using-sql-expressions-in-views-3/ for someone's clever idea of how to do that in D7; it appears to work fine in D8, but will generate a bad query w/o the correct GROUP BY).

Answer 1

It turns out that the solution is similar to what I tried: you do need to use hook_views_query_alter. But playing with GROUP BY is useless unless you can guarantee that you will not be using ORDER BY at all (i.e., you will do no sorting beyond your GROUP BY key). GROUP BY will contain not only the variable you want to group by; it will also contain every variable you use in ORDER BY. This appears to be required by the current SQL standard.

The way to do this is this is build your view around the "parent" type ("Course" in my example), and do any filtering you need to do in hook_views_query_alter, using a correlated subquery. This will look something like this:

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

where node_field_data is the base table for Course.

Since there is no left joins or inner joins with the Session table, there are no duplicates, making the deduping problem go away.

Answer 2

This feature is working out of the box, but documentation is not very good. AddField method has third variable, called $params https://api.drupal.org/api/drupal/core%21modules%21views%21src%21Plugin%21views%21query%21Sql.php/function/Sql%3A%3AaddField/8.2.x

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

Similar code is fixing this issue.