Duplicate nodes when using Views relationships

function your_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
if ($view->id() == 'myviewid' && $view->getDisplay()->display['id'] == 'block_1' ) {
$query->addField('', 'nid', '', ['function' => 'groupby']);
$query->addGroupBy('nid');
}
}

I have a very big query with lots of sorting and exposed fields so I was not comfortable with adding custom query to this, potentionally breaking things.

I came up with elegant solution without coding. You basicly only need to know which multi-valued field is creating the duplicates. Often it is some taxonomy or entity reference or reference to image field or so, and then you just add filter with DELTA lower than 1. This will eventually filter-out the duplicates completely. No aggregation or coding needed.

Agree with @lubwn ... usually properly limiting with Deltas the presence of Multivalue fields (also from relationships) and DISTINCT query option solves the duplications.

Comment #13 🐛 Duplicate nodes when using Views relationships Closed: duplicate worked for me.

The only detail is, I had to modify a little bit the query.

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;

/**
 * Implements hook_views_query_alter().
 */
function mymodule_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  switch($view->storage->id()){
    case 'my_view_id';
      $query->addField('node', 'nid', '', ['function' => 'groupby']);
      $query->addGroupBy('node.nid');
      break;
  }
}

#24 (based on #18) works perfectly, thanks!

Is there in the meantime a module that solves this problem in a generic way for views with this kind of relationships?

#53 is working well for me.

#51 set me on the right track. I had a view with a relationship to another entity with multiple cardinality. Each additional reference was causing a another row for the exact same entity. By setting a filter on the delta and only showing those with delta = 0, it limited the results to one per actual entity, despite having multiple references to another entity.

I tried to implement hook_views_query_alter() but I kept getting weird results with ViewsBulkOperations so I opted for the filter against the delta instead.

@perfectcu.be thanks, #41 works for me.

#13 works for me too. Thanks for the tip!

IMHO, once one's understood the underlaying mechanisms #18 is the best solution! It is clean and effective.

I had a similar issue: trying to list custom entities, which reference multiple values from three taxonomies (i.e., each entity has 3 separate fields, and each field is set with Allowed number of values > 1 - or unlimited).
I wanted to show those linked taxonomies, as well as filtering by their label values (not simply their term-ids). Thus I had add relationships to each taxonomy... ending in i x j x k rows per each entity record - where i, j and k are the number of taxonomy terms referenced by that record (sometimes, 3 x 2 x 2, resulting in 12 rows for a single entity!).

Setting Use aggregation (in Advanced > Other) to Yes, then adjusting Group column to Entity ID in Aggregation settings for each involved field (i.e., only the fields taken from those multi-referenced taxonomy terms, no need to set this for all fields of my view) was enough to finally reduce each record to a single row.
By the way, no need to set Distinct on within the Query settings.

@hodba Thanks a lot!