distinct does't work.

it is:

it should be:

#5 duplicates.txt8.85 KBcouturier
#1 Master display query options.png101.22 KBAnonymous (not verified)
#1 Query for misbehaving view.png213.65 KBAnonymous (not verified)
Members fund testing for the Drupal project. Drupal Association Learn more


Anonymous’s picture

Priority: Normal » Major
213.65 KB
101.22 KB

I can confirm this, and I believe it amounts to a major (IMO critical) bug.

When expecting a view result of 559 rows (the site's number of user accounts), I instead am getting 9222 rows!!!

Is this a regression? Wasn't it addressed in an earlier release?

FYI, my query settings and the query itself are attached. Also, I get the same result regardless of the query settings -- none, Distinct, Distinct+PureDistinct all produce the same result of 9222 rows for 559 distinct items.



Anonymous’s picture

Title: distinct doesn't work » Duplicate rows are in the view's result set (was: distinct doesn't work)
Component: Miscellaneous » Views Data
Anonymous’s picture

I can confirm the issue.

Found this thread that seems related - http://drupal.org/node/863478
Maybe something changed in the latest version that broke this?

couturier’s picture

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.

couturier’s picture

8.85 KB

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.

couturier’s picture

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.

kreatIL’s picture

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

couturier’s picture

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.

boinkster’s picture

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?

couturier’s picture

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.

Anonymous’s picture

After much searching high and low a few months ago, I found one or several posts (which I wish I could point you to now) that suggested the key to resolving this issue is often to edit the view's "Query settings" as follows:

a) Enable "Disable SQL rewriting"; and

b) Disable "Distinct".

This is what has worked for me, across a number of views.

pthurmond’s picture

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.


The source reference is in the comments on the page.

mxh’s picture

Version: 7.x-3.5 » 7.x-3.x-dev

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.

Anonymous’s picture

Have you disabled SQL rewriting?

mxh’s picture

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.

Anonymous’s picture

Thanks so much for your kind comments, Maximillian.

ts145nera’s picture

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?

mxh’s picture

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.

ts145nera’s picture

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

joewired’s picture

I have the same problem using the location module with multiple values, getting duplicates which can't be removed.

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

g76’s picture

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.

couturier’s picture

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.

hgneng’s picture

#11 works for me.

hgneng’s picture

#11 works for me

DarkteK’s picture

Issue summary: View changes

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

DrupalNovice’s picture

#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:

shaisamuel’s picture

#11 solve the issue without any patch !

Andrew211’s picture

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;
cirrus3d’s picture

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