Duplicate nodes when using Views relationships

I can confirm this is happening. I have exactly the same issue.

I'm also getting duplicate notes. Distinct and aggregation not working. Any fix yet?

This is a duplicate of the issue about Views support in Entity Reference Revisions.

After trying two patches that do not fix this issue, I now strongly doubt that this is a duplicate. When it is, then the patch in the related issue should have fixed this one. I therefore reopen this issue to force explanation of this discrepancy.

I can confirm this is not a duplicate. I'm having the same issue as #8.
Patches from entity_reference_revisions didn't solve the problem

It seems to be related to https://www.drupal.org/project/drupal/issues/2993688
When you add a (paragraphs) relation, the DISTINCT doesn't work anymore on nid because (in my case) the worktype.id is added as a column to the SELECT part:

SELECT DISTINCT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid, paragraphs_item_field_data_node__field_worktype.id AS paragraphs_item_field_data_node__field_worktype_id
FROM
node_field_data node_field_data
LEFT JOIN node__field_worktype node__field_worktype ON node_field_data.nid = node__field_worktype.entity_id AND node__field_worktype.deleted = '0'
LEFT JOIN paragraphs_item_field_data paragraphs_item_field_data_node__field_worktype ON node__field_worktype.field_worktype_target_revision_id = paragraphs_item_field_data_node__field_worktype.revision_id
WHERE (node_field_data.status = '1') AND (node_field_data.type IN ('location'))
ORDER BY node_field_data_created DESC
LIMIT 11 OFFSET 0

The above query results in duplicated NID's:
+-------------------------+------+----------------------------------------------------+
| node_field_data_created | nid | paragraphs_item_field_data_node__field_worktype_id |
+-------------------------+------+----------------------------------------------------+
| 1544454937 | 7 | 3 |
| 1544428064 | 5 | 29 |
| 1544195142 | 4 | 1 |
| 1544195142 | 4 | 2 |
+-------------------------+------+----------------------------------------------------+

To solve the duplicated nid's result I used hook_views_query_alter() to add a groupBy nid to the query with the Sql::addGroupBy method.

Resulting in the following query:

SELECT node_field_data.title AS node_field_data_title, MIN(node_field_data.nid) AS nid, MIN(paragraphs_item_field_data_node__field_worktype.id) AS paragraphs_item_field_data_node__field_worktype_id
FROM
node_field_data node_field_data
LEFT JOIN node__field_worktype node__field_worktype ON node_field_data.nid = node__field_worktype.entity_id AND node__field_worktype.deleted = '0'
LEFT JOIN paragraphs_item_field_data paragraphs_item_field_data_node__field_worktype ON node__field_worktype.field_worktype_target_revision_id = paragraphs_item_field_data_node__field_worktype.revision_id
WHERE (node_field_data.status = '1') AND (node_field_data.type IN ('location'))
GROUP BY node_field_data.nid, node_field_data_title
ORDER BY node_field_data_title ASC
LIMIT 11 OFFSET 0

It seems to work as expected by design, due the fact we are adding multiple references (target_id's) for one node to the distinct, which result to duplicated node results in views.

This issue is a duplicate and i didn't review your case in detail. The job of the other issue is to provide a working views integration. We (the maintainers) are not claiming its patch is ready at all.

What is more important though is that the expected use cases are provided as tests so we can make the Views integration work as expected. We only have a single test until now.

Thanks @tjerkr for the work around. It works for me too!

Yess, solution from #10 works.
Full example here.

/**
 * Implements hook_views_query_alter().
 */
function mymodule_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  switch($view->storage->id()){
    case 'my_view_name';

    $query->addField('entity_type', 'id', '', ['function' => 'groupby']);
    $query->addGroupBy('entity_type.id');
    break;
  }
}

Solution #10 would work for me if I had'nt an ORDER on a field of the related paragrap to do.
Any suggestion on how to do ?

Thanks

Thanks #10 and #13 for an easy to understand working example.

Thanks #10 and #13.
#13 is the best way to add groupBy(), this case works when you have a node with a multiple paragraph field.

#13 worked for me as well.

Just note that if you add a relationship with a field which has multiple values, this is bound to happen.

You can simply resolve this without any code from the UI as the following:
- Add a new ID field and exclude it from the display
- Activate aggregation
- Configure the ID field aggregation to "Group results together"
- set Group column to "Entity ID"

#18 works

#18 worked for me, except all I had to do was activate aggregation, it seemed to know which Entity ID or something.

It's not a duplicate.
This issue about NODE view with related paragraphs.
And "Views doesn't recognize relationship to host (2799479)" about PARAGRAPHS view with related nodes.

#13 works for me.
#18 does not.

FYI #18 does not work for JSON (REST) Views displays.

This is indeed a duplicate of #2993688: Views relationships with multi-valued entity reference fields invalidate Distinct query option, which is more general as the problem is not confined to Paragraphs. As such, there should not be an issue about it in this queue.

Please direct all efforts to the Drupal core issue. Thanks.

#18 works
if Group column "Entity ID" set on every views field

#18 works.
#24 Yes, it requires a consistent Group results by Entity ID on every views field (each one should have the SAME aggregate setting), otherwise blank error screen is produced.

hodba, that's a genius workaround!

I had the same problem on a views with a file relationship, and there was no translation on the relationship to filter by. Aggregating never occurred to me, but it works well until this can be resolved - thank you :)

#18 doesn't work for me, as generates an error: "Unknown column 'node__field_image.field_image_' in 'field list'..." (this bug is already registered, but still not fully fixed). Any other workarounds?

Inspired by #18 I tried something similar. The duplication, as others have noted, is being caused by a relationship on two fields. I turned on aggregation, but I didn't add a new ID field. I just set the aggregation settings on those two fields to "Group results together" by "Entity ID".

In my case, I wanted to use the First Name and Last Name field from a user's profile in a view, so I set up a relationship to get access to those fields. The trouble was that now I was getting triplicates of the nodes in the view: one for the original, another for the first name, a third for the last name. Aggregation on those two field reduced the number to one.

Hi to everyone, #18 and #24 worked great for me.
But i have a question, we are making a gallery site, so we created the colection as the taxonomy and the pices of art as nodes.
Thanks to #18 we could create a taxonomy view that shows the colections names and a image from the node (without #18 and #24 help, we had the colections repeated for every pice of art vinculated to the taxonomy).
My question is: can i make the image showed at my taxonomy to be random (every time from a diferent node vinculated to that taxonomy).

Thanks everyone for your help.

#18 works fine.

#28 You need to change image field aggregation settings group to target id

#18 works perfectly fine for me.

I had a page built with multiple paragraph items. It was showing duplicate nodes for each item. #18 worked perfectly for me.

#18 worked for me.

#18 didn't work for me, but what did work for me was changing the 'Rendering language' of the view (below Language at the bottom of the middle column) and setting it to 'Interface text language selected for page'.

@hodba #18 works perfectly, thank you!

and

@rroose #18 (considering #24) for me works in combination with #35.

I chose “Content language selected for page” for “Language” and “Content language selected for page” as “Filter criteria”.

I´m working with Drupal 8.9.18

At the moment no solution is working for me. You can see my configuration at the attached image.
I´ve created a relationship between nodes and media object over a vocabulary.

The media object has the field field_tags and the node has the field field_mt_post_tags both are referenced to the same vocabulary.

Now i´ve got the problem of duplicates and that are all images shown if only one tag is used together, but i want to have every image once and i want that the media is referenced to all tags of the referenced tags of the node.

If i´m using the code of #10/#13:

use Drupal\views\ViewExecutable;
 use Drupal\views\Plugin\views\query\QueryPluginBase;
  
 /*
 * Implements hook_views_query_alter().
 */
function custom_view_query_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  $id_view = $view->storage->id();
  drupal_set_message($id_view);
  switch($view->storage->id()) {
    case 'duplicate_of_photo_gallery_test_';
	
	/*
    $query->addField('entity_type', 'id', '', ['function' => 'groupby']);
    $query->addGroupBy('entity_type.id');
	*/
    break;
  }
}

I got the following error, so i have added the "media: ID", than i get the error in the view-display (second image).
Has someone an idea to solve that problem?

#13 worked for me with the following tweak.

entity_type and id:

    $query->addField('entity_type', 'id', '', ['function' => 'groupby']);
    $query->addGroupBy('entity_type.id');

became:

    $query->addField('node_field_data', 'nid', '', ['function' => 'groupby']);
    $query->addGroupBy('node_field_data.nid');

Also (prolly doesn't matter) I have the hook_views_query_alter() implementation in a separate file: your_module.views_execution.inc.

Hint: check the query, chunked out at the top of the view preview area, for tables in use by the view:

@perfectcu.be can you tell me where to put the code, please?

My solution was similar to #35.

In my case, some of the results in the view had files associated with them, and other results use a URL instead.

I was getting duplicates on the results that use files because of a media relationship and the site has 2 languages. My solution didn't use aggregation at all. What I needed to do was add additional filter criteria and logic using a filter group option.

I added 2 additional filters, in my case, for field_file:media: Media, which was the field that required the relationship to Media.

One filter sets the translation language to the Interface text language and the other filter looks for this field to be empty. Then, if you click the drop-down arrow next to add in the filter criteria section and select "And/Or Rearrange", a modal window opens and you should see "Create new filter group" as an option. I created a new group, dragged both of the new filters for the same field into the new group, and changed the operator from AND to OR.

Here's a screenshot: https://drive.google.com/file/d/1s6mWNAQ4emWy81-wOY6_BhQ-nWYCyFXW/view?usp=sharing

This way the results without a file can be displayed and the results that have a file only show up once because of the filter looking at the interface text language.

@filipetakanap,

You'll have to create a new module or tack the code into a module you're already working on. You can skip a lot of boilerplate pain by installing Drupal Console and having it plop out a shiny new module for you.

Let’s assume that the module you're hacking away on is called "your_module". All your module code is in a folder called "your_module" and this folder is located where Drupal's autodiscovery mechanism will pick it up; one of the modules folders.

In my case I already had a hook_views_query_alter() implementation in your_module/your_module.views_execution.inc

File: sites/default/modules/custom/your_module/your_module.views_execution.inc

/**
 * Implements hook_views_query_alter().
 */
function your_module_views_query_alter(ViewExecutable $View, $Query) {

   $view_id = $View->id();
   $display_id = $View->getDisplay()->display['id']; // You can use $View->display also? ... maybe?
   $view_hook = "$view_id::$display_id";

   switch($view_hook){
      case 'your_view::your_views_display';
         $Query->addField('node_field_data', 'nid', '', ['function' => 'groupby']);
         $Query->addGroupBy('node_field_data.nid');
      break;
  }
}

A couple of notes:

1. I needed a bit more granular filtering than what lamp5 was doing in his example. I only needed the filtering on a single display; therefore, $view_hook. You can get your hands on the specific view_id/display_id you're catching in your hook_views_query_alter() implementation here:
2. I'm not 100% sure that you have to put your hook_views_query_alter() inside a separate file; your_module/your_module.views_execution.inc. I remember reading somewhere that you did, but I've lost the thread* on that one, and I haven’t bothered to confirm that hook_views_query_alter() works from your main your_module/your_module.moudle file; it might.
3. I'm not type hinting $Query like lamp5 is, because doing so throws an exception if you're using search_api, as the $Query is sometimes and instance of Drupal\search_api\Plugin\views\query\SearchApiQuery and sometimes an instance of Drupal\views\Plugin\views\query\Sql. I implemented my hook_views_query_alter() call before googleing into lamp5's gem (thanks man!) and I think I was type hinting to Drupal\views\Plugin\views\query\Sql not QueryPluginBase? Sounds like it's time for a TODO!

-Dan

*PS found the thread ... maybe it's different in 8.9.x+? I've got it working so I'm lackadaisical about digging into the deets on that one.

#13 worked for me. Thanks

18 works for me. But i need to choose "Standard derivation" for field of entity from the relationship

#38 works correctly for me.

#43 Works perfectly for me, thank you!

Sometimes the duplication happens because of the language for the related entity from the relationship and can be solved by adding a language filter for the new related entity under the FILTER CRITERIA area.

Just a note that #13 fails PHPStan as an outdated method. I'm running Drupal 9.3.16, PHP8:

 ------ --------------------------------------------------------------------------------------------
  Line   paragraph_views/paragraph_views.module
 ------ --------------------------------------------------------------------------------------------
  18     Call to an undefined method Drupal\views\Plugin\views\query\QueryPluginBase::addField().
  19     Call to an undefined method Drupal\views\Plugin\views\query\QueryPluginBase::addGroupBy().
 ------ --------------------------------------------------------------------------------------------

To avoid PHPstan problem, method_exists function can be used

/**
 * Implements hook_views_query_alter().
 */
function your_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->storage->id() == 'my_view'
    && method_exists($query, 'addField')
    && method_exists($query, 'addGroupBy')) {
    $query->addField('node_field_data', 'nid', '', ['function' => 'groupby']);
    $query->addGroupBy('node_field_data.nid');
  }
}

Alternatively instanceof can also be used which is better in terms of predictability, for method signature etc, Also follows OOPS pattern and a bit faster compared to method_exists

use Drupal\views\Plugin\views\query\Sql;
/**
 * Implements hook_views_query_alter().
 */
function your_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->storage->id() == 'my_view' && $query instanceof Sql) {
    $query->addField('node_field_data', 'nid', '', ['function' => 'groupby']);
    $query->addGroupBy('node_field_data.nid');
  }
}

Thanks @guptahemant ... your #48 works for me

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 to this field with setting of 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 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.

In my case, I had an entity 'contact' with a reference to multiple 'contact_group' entities. I wanted to show all the contact groups that a contact was in as a column so I had a relationship to the contact group referenced by contact entities. This caused an extra row per contact group for each contact entity as a duplicate. However, if I wanted to filter for contacts in Contact Group 24, the delta for this entry may have been > 0 in which case filtering would cause these rows to disappear. So I ended up conditionally setting this filter via code in a hook_views_query_alter() using an ->addWhere() method. Note this ->addWhere() in a sql query is slightly different from the other ->where() method in an entity query.

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