Finding the next ActiveRecord model object in the database using given object

Question

I have an ActiveRecord model object @gallery which represents a row in the Galleries MYSQL table. Is there a way for me to ask @gallery to give me the id to the next gallery object in the table?

The obvious way for me to do this is to :

@galleries = Gallery.find(:all)
index = @galleries.index(@gallery)

@nextgallery = @galleries[index+1]

but then I have to nilsafe this and I'm unnecessarily making another DB call to fetch all records. Any other way out?

Answer 1

I've been in similar situation and I end up with couple solutions using Rails 3.1.3, with either class methods or scope (named_scope in Rails 2.x). This approach will work even with not sequential ids.

Class methods example using a Post model:

def next
  Post.where("posts.id > ?", self.id).order("posts.id ASC").limit(1)
end

def previous
  Post.where("posts.id < ?", self.id).order("posts.id DESC").limit(1)
end

This could be used like:

post = Post.find(5)

next_post = post.next
 => [#<Post id: 6, ...]

previous_post = post.previous
 => [#<Post id: 4, ...]

And for scopes, using lambda should be something like:

  scope :next, lambda { |i| {:conditions => ["#{self.table_name}.id > ?", i.id], :order => "#{self.table_name}.id ASC", :limit => 1} }
  scope :previous, lambda { |i| {:conditions => ["#{self.table_name}.id < ?", i.id], :order => "#{self.table_name}.id DESC", :limit => 1} }

Answer 2

Are you really sure that you're making a second db call? If so, does it really matter? Is this deep in a 4x nested loop where every call matters?

If not, pre-optimizing may be your downfall. AR:B does a lot of caching and iirc find(:all) returns an array or Hash so index+1 should be okay.