Duplicate rows are in the view's result set (was: distinct doesn't work)

I've been doing hours of reading trying to figure this out. Yes, it seems something with the 7.x-3.5 upgrade has affected this, though I have so many Views with hundreds of nodes, that it could be I just didn't notice it before. I've found that Distinct does not work, but it does work if I remove any type of Sort critera and use just a single filter. For me, somehow adding a Sort criteria is producing duplicate nodes in a Views table.

After looking through dozens of other bug reports, I do not see any that address this issue, and I think it is an important one. I'm attaching an export of my View that is based on filtering by one taxonomy term and returns duplicate nodes when sort criteria is added.

This is a really interesting related comment: http://drupal.org/node/1239278#comment-4858846

Notice that someone else two comments later was getting duplicates. And, comment #17 gives advice for reducing duplicates.

Same problem here. Getting duplicates when sorting by a field with multiple values. The workaround using a relationship as described in the referenced issue (#6) doesn't work for me. In my case I'm not sorting by taxonomy terms but by a date field having multiple values.

This worked for me: http://drupal.org/project/views_arguments_extras
I replaced the standard “views core“ filter argument by applying a "Multi-item Argument Order“.

Edit: views arguments extras doesn't seem to work properly

I just spoke to a programmer at my local Drupal User's Group meeting (staris) who said "Distinct" was introduced in Views 7.x-3.3 and it didn't work then and doesn't work now in 3.5. He said that if "Distinct" did work, it would solve the problem of these duplicates but that we will probably have to wait until Drupal 8 to see it fixed. The other developers at the meeting agreed with this assessment. Remember, Views made it into Drupal 8 core so it will get a lot of attention as far as bugs from Drupal 8 and forward. Setting only one filter criteria and removing any sort criteria has made my duplicates problem go away for now.

I've got a view with multi values on a date field.

On my local dev box, it works:
Mysql 5.5.28-0ubuntu0.12.04.3-log
PHP 5.3.10-1ubuntu3.4

On the production server, it doesn't:
Mysql 5.1.66-0ubuntu0.10.04.3
PHP 5.1.66-0ubuntu0.10.04.3

Could there be something in here that would matter?

If it's the same Views module, seems that it should work the same both places. What we have in this issue is duplicate values being returned in Views that cannot be fixed except through use of the "Distinct" option which is broken. Some of us have found success by simplifying our View, that is, removing as many filters and sort criteria as possible. Some people have found problems with the "Published" or "Published or Admin" filter (published might work, while published or admin doesn't). Other people are seeing duplicates when sort criteria is added. My own duplicates problem was resolved by removing the published filter and any sort criteria. At present, I have one filter sorting by taxonomy term, and it works with no duplicates. Not an elegant solution, but we may have to wait for Drupal 8 for improvement.

So I found a really useful workaround that allowed me to fix this. I have posted the code snippet to DropBucket. Basically you implement a Drupal 7 query alter hook that checks all views queries for query comments that specify a groupby clause and field. Then it adds that groupby to the SQL query.

http://dropbucket.org/node/153

The source reference is in the comments on the page.

I have the same problem using a date field with multiple values, getting duplicates which can't be removed.

I confirm that distinct and "Pure Distinct" don't work.

This problem also occurs on the current dev.

I can't remove the date field filter because I need to filter out several date values, otherwise the view makes no sense.

As said in documentation, SQL rewriting is for disabling node_access checks, that means enabling this setting without caution you cause security problems because there is no native node_access check going on with the views result set. SQL rewriting has nothing to do with this issue. It doesn't help on this problem.

Hello,
I've an idea, but it's a workaround.
My idea is based on use view PHP and it work fine.
I set $static as an associative array where the key is field value that I would unique.
If $static[] is set I return "TRUE" to remove the row.

What do you think?

Hi ts145nera
working with Views PHP module is an alternative when Views come to its limits (like here at the moment). But be aware that it's pretty difficult to use because you have to handle all cases like access control (think of entity references).
My experience with Views PHP is that it can be a swiss knife for Views, but also can break down performance a lot.
I will also use this module to set up a selfmade PHP filter for a temporary workaround until this problem is fixed. I think your proposed solution will be also ok for a temporary workaround.

Hi hauptm,
thank you for your answer.
You're right, Views PHP is a swiss knife, but you pay attention to don't cut yourself :)
Maybe the problem is in views_plugin_query_default.inc:query()
I see that there's always "nid" field in $this->fields, so distinct can't work fine

same issue when trying to display media images from a multi-value field. i also do not want to merge them into a single row as that would defeat the point. All I need to do is to display a thumbnail of the images with a link to a colorbox. I may be missing something or not setting it up correctly as well. Any assistance would be so much appreciated.

You can use Rules sometimes to create views with thumbnails of certain images. It is a replacement for "sticky" or "promote to front," in a way, and might help you get around the fact that Distinct is still not working properly in Drupal 7. You would set up a rule to flag certain types of content and then create a view with the flagged content. Read the Tiny Book of Rules for a headstart if you are interested. You can create the image to be a clickable image through coding and linking to an uploaded image.

#11 works for me.

#11 works for me

If you still have doubts with distinct let me know, I can help you !

#11 did not work for me.

In my case, the duplicates occur when sorting by field with multiple values, as #7.

A workaround is found here: https://www.drupal.org/node/1223448#comment-4757898

Hopefully this will be fixed soon.

Related issues:
https://www.drupal.org/node/2361749
https://www.drupal.org/node/1223448

#11 solve the issue without any patch !

If all else fails, you can remove duplicates using something like this.

function my_module_views_pre_render(&$view) {
  if ($view->storage->id() == 'my_view') {
    $matchedGroupIds = array();
    foreach ($view->result as $row) {
      $id = $row->_entity->id();
      if(!in_array($id, $matchedGroupIds)){
        array_push($matchedGroupIds, $id);
        $rows[] = $row;
      }
    }
    $view->result = $rows;
  }
}

Has anybody tried to turn on aggregation for the view? Distinct then works for me.

For date field with multiple values this worked for me:

In Filter Criteria, I added "Content: Date:delta (= 0)"

.

Edit: Sorry. It did not for this reason:
In particular, if you are filtering by upcoming dates and the field's first date (delta 0) is in the past but subsequent dates are in the future, no row will be returned.

https://drupal.stackexchange.com/questions/12498/views-relationship-outp...

Author: hachesilva

I've tried every recomendation I've found and nothing seems to do the trick perfecly. The most precise result I can get is filtering the views results before rendering the view, like this:

function YOURTHEMENAME_views_pre_render(&$view) {
  $used_nids = array();
  $new_view_result = array();

  if ($view->name == 'YOURVIEWNAME') {
    foreach ($view->result as $row) {
        if (!in_array($row->nid, $used_nids)) {
          $new_view_result[] = $row;
          $used_nids[] = $row->nid;
        }
    }

    $view->result = $new_view_result;
  }
}

Put that snippet in your template.php and replace YOURTHEMENAME and YOURVIEWNAME with your values, you should have no duplicates. HOWEVER, I'm finding that this creates pages with less rows than expected, for example, if you want 10 results per page and your view has 8 duplicates in the first page, the first page would have just 2 rows. So, take my code as a partial result and only if you don't care having less results per page or if you are displaying all results at once.

I realise this in an old issue, and for Drupal 7, but just a quick note to thank cirrus3d for the comment in #30.
I'm using Drupal 8, with a relationship to a paragraph that causes duplicates rows to appear.
Enabling aggregation on the view suppressed the duplicates.

I have a dirty hack for D8: https://gist.github.com/luksak/0907196a41315b5876f9d630a554f41c

It won't work with pagination an might cause unexpected issues, but in my case it solves the issue.

The suggested solution by #31 seemed to work for me.
I have a view showing customers with a relationship to their orders ( can be a few orders for each customer) which showed me duplicate customers in the results.
Adding a filter order delta = 0 removed the duplicates.

Oh wow, that makes so much sense. This would also solve the issue for entity references in my case...

All the examples on this page are for Views displaying nodes. However I have a View of Media that displays both Image and Video media types. Video Media integration is provided by the Video Embed Field module. In my view, the media are grouped by taxon and then, within that, by media type.

I am trying to adapt #29 to my case, and so far I have this:

function MYVIEWMACHINENAME_views_pre_render(&$view) {
    if ($view->storage->id() == 'MYVIEWMACHINENAME') {
        if ($view->current_display == 'MYDISPLAYMACHINENAME') {
            $matchedGroupIds = array();
            foreach ($view->result as $row) {
                $id = $row->_entity->id();
                if(!in_array($id, $matchedGroupIds)){
                    array_push($matchedGroupIds, $id);
                    $rows[] = $row;
                }
            }
            $view->result = $rows;
        }
    }
}

It runs without displayed errors, and the images are displaying correctly, however the displayed videos are either the incorrect videos (for the taxon), or missing. How can I adapt this function to cater for both image and video media types within the same View?

For my case, instead of the custom function (#37), I managed to remove duplicates by adding new Filter Criteria with delta=0 for the fields that I had added relationships for:

Credit to this comment and this comment for leading me to that solution, which has rescued my project.

Before that, I found that Advanced>Other>Query settings>Distinct didn't work, and Advanced>Other>Use aggregation>Aggregate caused errors.

#32 love!

But still have problems using LOAD MORE as pager :(

In my case, I was able to resolve this issue by using a different formatter. I used Rendered Entity instead. I needed to get a field of the entity referenced and when I used relationship to display the field, I was getting duplicates.

Then used Formatter: Rendered Entity and create a view mode.

For those who still haven't found a way to address this issue, I used the views distinct module (https://www.drupal.org/project/views_distinct) and applied the 'Filter Repeats' setting on the concerned field. In my case, the duplicates were caused by including a sort criteria on a field with multiple values. Take note though of the known issues listed in the module's page before using. Cheers!

@nick-hope thanks.
#38 work for me.

#38 worked for me: It's the easiest solution assuming you're sorting on only one index/delta of a multi-value field.

#32 works for me