distinct does't work.

example
it is:
asd
asd
qwe
asd
qwe

it should be:
asd
qwe

Comments

Priority:Normal» Major
StatusFileSize
new213.65 KB
new101.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.

Thanks,

Bob

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

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?

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.

StatusFileSize
new8.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.

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.

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.

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.

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.

Have you disabled SQL rewriting?

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.

Thanks so much for your kind comments, Maximillian.

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

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.

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