Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Duplicates in results due to Removing DISTINCT from query #104

Closed
beatlevic opened this Issue · 9 comments

5 participants

@beatlevic

Kaminari removes the DISTINCT from my query (select("DISTINCT table.*")) and then does a SELECT DISTINCT TOP over the query.

Because I'm joining with other tables the distinct is required in the sub-query for the correct result, and without it results in duplicates.

Could this issue be resolved?

Thanks,
Coen

@amatsuda
Owner

Thanks for reporting this issue!
This problem was fixed in this commit 69a5935 which will be included in the next release (0.12.4).

@amatsuda amatsuda closed this
@beatlevic beatlevic reopened this
@beatlevic

Unfortunately this fix doesn't fix my DISTINCT problem. I'm still missing my DISTINCT in the sub-query (DISTINCT blocks.*):

SELECT DISTINCT TOP (7) [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY blocks.title ASC) AS [__rn], blocks.* FROM [blocks]

@beatlevic beatlevic closed this
@beatlevic beatlevic reopened this
@beatlevic

Any updates or questions on this front?

Thanks.

@amatsuda
Owner

I'm sorry but I haven't met that case.
Can you please provide a failing test case or example app?
And, I guess you're using SQLServer, but does the same error happen on other RDBMSs like Postgres, MySQL or SQLite3?

@beatlevic

For example 'Book' has many 'Chapters'. Inner join on Chapters, and return all books where Chapter.title LIKE "today". I then need a distinct on the result set (as more than one chapter per book could satisfy the LIKE criteria).

We indeed use SQLServer :( which indeed could be the source of our problem. I just wanted to run this by you, to see if you know if this occurs with other DBs as well.

@beatlevic

If this is not the case with other DBs, do you have any suggestions?

@jpzwarte

The problem is that ActiveRecord ignores the DISTINCT query when there is more than 1 column name specified. See ActiveRecord::Calculations.select_for_count. The trick is to add a GROUP BY statement containing the DISTINCT column names. See http://stackoverflow.com/questions/5092745/rails-3-difference-between-relation-count-and-relation-all-count

@zzak
Collaborator

Closing this until someone can provide a failing test or reproducible app

@zzak zzak closed this
@top4ek

@zzak,
I got same issue, I beleive.
Here example app.

In default index action we have all pages in paginator
In filtered action we still have all pages, but when click on 6+ we got empty page.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.