How increment through records when row number and id is out of sync?

When you have deleted some previous records in the DB, the auto-incremented record ID and the row number of the later records gets out of sync.

So, how do start from a given record and efficiently increment to the next record?

If you use the record ID, you might hit an ID that has been deleted.

If you use something like User.first(:offset => user_id) then you might miss some records. Since on a given record the user_id might be higher than the row number (as a result of previous 开发者_StackOverflow社区record(s) having been deleted).

The only solution I've found is to increment through all records each time, but that seems very inefficient:

user_id = params[:id].to_i
users = User.all
next_user = nil
next_cond = false
users.each do |u|
  if u.id == user_id
    next_cond = true
  elsif next_cond
    next_user = a
    break
  end
end

So, how do you efficiently get the next record? Is there a way to get the row number of a given record, for instance?

NB: I am using SQLite locally, and MySQL on the server.

User.where("id > ?", user_id).order("id ASC").first

It sounds like you want a pagination solution.

will_paginate is the best solution I'm familiar with, but it's kind of old, and I haven't checked in a while if a better one has come along.

http://railscasts.com/episodes/51-will-paginate