I have a View on my D7 site sorted randomly. Views Infinite Scroll shows the correct number of records, but will show duplicates of some and leave others off.


sadashiv’s picture

Status: Active » Needs review

You can use distinct to remove dupe rows. It is present in Advanced-->Query settings: Settings

Click on Settings and check the Distinct checkbox option.

Note the change in the query build by views it applies distinct clause to the query.


725’s picture

I have tried this method but it does not work for me. There is no problem if no sorting method is used. But if any sorting is used duplicate records can be seen.

sadashiv’s picture

The distinct only works for one entity i.e. if you have created view of type "content" and are adding relationship or have added conditions which are merging tables like other users so distinct will only apply distinct for contents but the result will contain dupes of the other table.

725’s picture

Thanks for quick response. My view has "Flags: bookmarks (by any user)" relationship. If I don't use any sorting then there are no duplicates. But I must use sorting here. How can I avoid duplicate records in this case?

sadashiv’s picture

I think even without sorting their will be dupes only they will be spread so not noticeable, sorting bring them together. I am not sure but may be the aggregation on the dupe fields can reduce the dupes, can you check using that? You can find it in the "Advanced" part of views.

725’s picture

I cannot enable aggregation. I get the error:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS field_data_field_rating_, MIN(node.nid) AS nid_1, 'node' AS field_data_body_n' at line 1

quickly’s picture

+1 I am having a very similar issue.

greggles’s picture

Status: Needs review » Postponed

I think this is fundamentally bug in the random sort of views. I don't think this module is truly compatible with a randomly sorted view. There used to be sort plugin for views where the same random sort was somehow applied for a certain amount of time. This let random work with multi-page views. Until/unless something like that is again available I don't see how Views Infinite Scroll can work with a randomly sorted view.

quickly’s picture

I have explored this problem, the issue is not only with the Random sort. I can see duplicate records even with other sorting options. This means the problem is probably not in Views, most probably it is a bug in VIS.

greggles’s picture

Well, there are two problems. One is "Duplicate Records when Sorting by Random" which is this issue. In this case there is a fundamental problem that views infinite scroll is unlikely to solve, but this issue can be helpful to alert people to a workaround/solution when one is found.

For a more generic "duplicate records with any sort function" there is #1921130: Duplicate Records....

outlierdavid’s picture

I am having this exact issue that the OP is having. I am NOT using infinite scroll. (I did at one time, but have since removed AND uninstalled)

This isn't a bug with VIS, for sure, but a bug with how the view runs the "random sort" in conjunction with AJAX. It's my feeling that every query will pull X number of items from Query 1... but when the next query is requested, it re-runs query 1, randomizes AGAIN, and offsets the results by X (to display only "page 2" results).

This is fine if you aren't randomizing and the view retrieves the same results every time, but since the view randomizes the query with every ajax call, you get duplicate items and missing items as well.

I haven't a solution for this yet... but considering my idea about where the problem lies is probably spot on, we can begin trouble shooting from this point to see if a work-around or a fix can be made.

jcsnyder’s picture

I am experiencing a similar issue with infinite scroll. Basically, the user can vote nodes up and down, and when infinite scroll grabs the next batch of nodes in the view, it reruns the query. But by this time, the order of results has changed due to the votes. I am looking for a way for views to cache the query result... or something like that.

hardcoding’s picture

i have the same issue. first i had sorting by random. after duplicate entries i found out that vis dont like sort critaria. without sort criteria i dont have duplicate entries. does anybody else have this issue?

hardcoding’s picture

ok i found the problem. if your sort criteria cant give your entries an unique position, the bug occurs.
what i mean is: sort by random
- with every query the entries get a new position

sort by field with 2 values:
- imagine you have 50 entries with value "1" and 50 with value "2". so views first show the entries with value 1 but views dont know which entry to show cause all got the same position "1" and so you will get an random entry.
if no paging exists, views will correctly show the result with no duplicates. but if there is an paging, you will have duplicates.

views infinite scroll does the right job. views sort criterion "random" needs work. it has to give all entries an position independent of paging and with using paging views has to remember that position.

sorry for my bad english, but i hope it will help someone.

Alex Cioroianu’s picture

I have the same issue.

So, is there any fix for this? I can not figure it out.

davidwhthomas’s picture

#14 hardcoding

Thanks for the tips on resolving this issue!

In my case, I was sorting in views by a weight field from draggableviews.

I added a second sort criteria of "Post date: created date" descending.

This means that even if two nodes have the same weight (e.g default 0) then they still get a unique sort, assuming the node created timestamp varies.

It fixed the duplicate item issue I was having.

Thanks again.

gbelot2003’s picture

Issue summary: View changes

Found the problem, in my case, I used "Items per page" with 6 elements, and "Number of pager links visible" 6 too, so, I set "Items per page" with 0 that is infinite elements and the repetition problem just desapear...

Tritof’s picture

ok but #17 in that case you actually don't use infinite load, you just have one load for all?
#14 I agree it is random. and to confirm your demonstration, I have a case where I am sorting by User : Last access and the ones that never accessed because it is a test site, so almost all of them, are having the bug behavior, because with having the exact same value, views is trying to sort them by a mean which is random and it causes the bug.
Is it possible to combine random function with a datestamp so every random value has a unique value and that would fixe the problem?

kabarca’s picture

eolson’s picture

I solved this problem for my situation. This may not help all of you but it might help some.

I was getting the random results when I was sorting by Global: Random (asc). I was filtering the nodes by saying take anyone that Is not empty (not NULL). When I switched this to Is one of and selected all of the options I stopped getting the duplicate entries.

The only downside for me is that if I get any more options in that filter, I'll just need to remember to go back into the view and make sure it's selected in that list.

joseph.olstad’s picture

Thanks for the sort criteria tips from #14 and #16

Our solution is to use this patch, in addition to adding post date to our sort criteria in the view.

SRizo’s picture

Thanks joseph.olstad for your patch. But if we have more than one sort criteria it does not work. Without this patch you can set POST DATE sorting granularity to SECONDS and infinite scrolling will not duplicate. The problem if we have more than one sorting criteria. Any ideas?

joseph.olstad’s picture

Yes we've got POST DATE sorting granularity to seconds, that works for us. The other patch is for caching ajax requests.

501cGeek’s picture

I was having the same problem of duplicates appearing with Views with a lot of filters and a sort, although I was not sorting by random, just an integer field (representing a 0-5 star rating). I tried a bunch of things, but the only thing I found that worked (thankfully!) is the Views Random Seed module. Below my existing sort criteria I added the second sort criteria Global: Random Seed. Then, finally, the view would return only unique results, sorted in the order I wanted.

Even if you're not sorting randomly, try Views Random Seed first! It may just fix your duplicates problem with Views, and you can use it with other sort criteria besides random.