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.

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(...)