Getting doublets of records while using an offset

[Max Grønlund]

Consider this schenario where I'm having pagination on a page

@recordings =  Recording.order(:title).page(params[:page]).per(4)
 puts params[:page]
@recordings.each do |recording|
      puts recording.id 

end 

page ----------------3------------------------
  Recording Load (4.0ms)  SELECT  "recordings".* FROM "recordings"   ORDER BY "recordings"."title" ASC LIMIT 4 OFFSET 8
1032
952
1063
1166 <<<<<<<<<<<<<<<<<<<<<<<<<< notice 

page ----------------4------------------------

Recording Load (4.3ms)  SELECT  "recordings".* FROM "recordings"   ORDER BY "recordings"."title" ASC LIMIT 4 OFFSET 12
1166  <<<<<<<<<<<<<<<<<<<<<<<<<< notice 
1168
657
756

So record 1166 is on both pages

--------------------
how I solved it

class Uniqifyer
  def self.uniqify not_uniq_field
    uniq_string     =   not_uniq_field.to_s
    # secure ordering e.g [13 , 120, 140 ] not [120, 13, 140]
    prepend_str =  '00000000000000'.byteslice(0...-uniq_string.length)
    prepend_str + uniq_string + '_uuid_' + UUIDTools::UUID.timestamp_create().to_s
  end
end

#in model:
before_commit :uniqify
def uniqify
 self.MY_UNIQ_STRING = Uniqifyer.unigify self.MY_NOT_UNIQ_FIELD
end

# in controller
def index
 @ordered_records = MY_MODEL.order(MY_UNIQ_STRING).page(params[:page]).per(24)
end

# Notice! Rube is so cool you can directly convert a uniqifyed number like this
number = MY_UNIQ_STRING.to_i

not so elegant
any suggestions are welcome

[Frederick Cheung]

On Tuesday, October 28, 2014 1:14:56 PM UTC, Max Grønlund wrote:

Consider this schenario where I'm having pagination on a page

@recordings =  Recording.order(:title).page(params[:page]).per(4)

Is the issue that title is not in fact unique?

If so you could order on two columns (rather than creating an extra unique column), for example

Recording.order(:title, :id).page(params[:page]).per(4)

Fred

[Matt Jones]

Are you on Postgresql? It takes a very literal interpretation of the SQL standard when row order isn't uniquely specified:

http://dba.stackexchange.com/questions/48989/postgresql-indeterminate-ordering-of-results

Sorting on something that's reliably unique (even just `id` along with `title`) should sort this out.

--Matt Jones