rails paginate duplicates entries

Question

I'm getting duplicate entries in my paginate call.

The code is as follows:

@reviews = @user.reviews.paginate(:page => params[:page], :per_page => 5)

When I run this in the terminal, it returns duplicate entries. Here is an example:

PAGE 1:

1.9.3p327 :040 > me.reviews.paginate(:page => 1, :per_page => 5)
  Review Load (2.1ms)  SELECT "reviews".* FROM "reviews" WHERE "reviews"."user_id" = 43 ORDER BY rating DESC LIMIT 5 OFFSET 0
   (1.0ms)  SELECT COUNT(*) FROM "reviews" WHERE "reviews"."user_id" = 43
 => [#<Review id: 319>, #<Review id: 322>, #<Review id: 318>, #<Review id: 323>, #<Review id: 313>] 

PAGE 2:

1.9.3p327 :045 > me.reviews.paginate(:page => 2, :per_page => 5)
  Review Load (1.3ms)  SELECT "reviews".* FROM "reviews" WHERE "reviews"."user_id" = 43 ORDER BY rating DESC LIMIT 5 OFFSET 5
   (0.7ms)  SELECT COUNT(*) FROM "reviews" WHERE "reviews"."user_id" = 43
 => [#<Review id: 313>, #<Review id: 324>, #<Review id: 315>, #<Review id: 310>, #<Review id: 312>] 

Note that review #313 occur both times.

However when I run me.reviews each id occurs only once.

I have been working on this all day so can respond quickly to update with any information you may need. I'm positively confused.

Enlace

Answer 1

You have duplicate ratings in your reviews (for example: 4,4,4,4,3,3,3,3, etc). Results are not guaranteed to be ordered the same for 2 different queries in the case of ties. You can resolve this by adding a secondary sort on id. That will guarantee the sort order for the entire query, and you will not have duplicates on different pages:

me.reviews.order(:rating, :id).paginate(...)

Orography

you changed my life. Thank you. I looked into it and my review model is ordered 'rating DESC'. I added 'id' to that and everything now flows. Still, one follow-up: I don't understand why there were duplicates. I understand why there would be UNORDERED entries given some are equal by rating, but why duplicate? – Enlace

This may depend on the type of database you're using. I've seen this with Postgres with LIMIT & OFFSET, which is what will_paginate does. You are doing 2 separate queries. The database does not guarantee that ties are ordered the same in both of the queries. So in my example above, a review with rating 3 might be in position 5 (page 1) the first time you run the query & in position 6 (page 2) the 2nd time. – Orography

thank you. I've been going on with this for 12 hours now. It's always a simple fix, but getting there takes some experience and you clearly have it. Thanks. – Enlace

Happy to help. 12 hours - sounds like it's time for break! – Orography

Thanks! I was stuck on a similar issue for a while. – Kuster