Rails: Get next / previous record
Solution 1
Try this:
class User
has_many :photos
end
class Photo
belongs_to :user
def next
user.photos.where("id > ?", id).first
end
def prev
user.photos.where("id < ?", id).last
end
end
Now you can:
photo.next
photo.prev
Solution 2
It lead me to a solution for my problem as well. I was trying to make a next/prev for an item, no associations involved. ended up doing something like this in my model:
def next
Item.where("id > ?", id).order("id ASC").first || Item.first
end
def previous
Item.where("id < ?", id).order("id DESC").first || Item.last
end
This way it loops around, from last item it goes to the first one and the other way around.
I just call @item.next
in my views afterwards.
Solution 3
Not sure if this is a change in Rails 3.2+, but instead of:
model.where("id < ?", id).first
for the previous. You have to do
.where("id > ?", id).last
It seems that the "order by" is wrong, so first give you the first record in the DB, because if you have 3 items lower than the current, [1,3,4], then the "first" is 1, but that last is the one you ware looking for. You could also apply a sort to after the where, but thats an extra step.
Solution 4
class Photo < ActiveRecord::Base
belongs_to :user
scope :next, lambda {|id| where("id > ?",id).order("id ASC") } # this is the default ordering for AR
scope :previous, lambda {|id| where("id < ?",id).order("id DESC") }
def next
user.photos.next(self.id).first
end
def previous
user.photos.previous(self.id).first
end
end
Then you can:
photo.previous
photo.next
Solution 5
This should work, and I think it's more efficient than the other solutions in that it doesn't retrieve every record above or below the current one just to get to the next or previous one:
def next
# remember default order is ascending, so I left it out here
Photo.offset(self.id).limit(1).first
end
def prev
# I set limit to 2 because if you specify descending order with an
# offset/limit query, the result includes the offset record as the first
Photo.offset(self.id).limit(2).order(id: :desc).last
end
This is my first answer ever posted on StackOverflow, and this question is pretty old...I hope somebody sees it :)
Comments
-
Andrew almost 2 years
My app has Photos that belong to Users.
In a photo#show view I'd like to show "More from this user", and show a next and previous photo from that user. I would be fine with these being the next/previous photo in
id
order or the next/previous photo increated_at
order.How would you write that kind of query for one next / previous photo, or for multiple next / previous photos?
-
igrek over 11 yearsprev does not work properly this way, should be user.photos.where("id < ?", id).last, well, thank's anyway
-
Harish Shetty over 11 years@igrek I have fixed the answer. Generally, I avoid using
last
call as it has performance implications if you have a big data set(stackoverflow.com/questions/4481388/…). I use theORDER BY id DESC
clause to get to the last rows. -
Dmitry Polushkin about 10 yearsI've created gem with the same behaviour, but the other approach: github.com/dmitry/proximal_records
-
Kourindou Hime over 9 yearshow about using limit(1) ?
-
Harish Shetty over 9 years@KourindouHime, the
first
method limits the result set toLIMIT 1
. Thefirst
method here is different than the first method on an array. -
Harish Shetty over 9 years@igrek, I have changed the solution to use
first
andlast
as I noticed that in latest version of railsfirst
andlast
adds proper sorting. -
Arslan Ali almost 9 yearsAwesome for cycle iteration.
-
Greg Blass almost 7 yearsNice, was looking for a cycle as well!
-
Avishai about 4 yearsWorks great. You might want to implement this on your model class instead, that way you can scope the query in a way that's consistent with your business logic (next/previous published article, for example).
-
Sathish almost 4 yearsThis solution does not work if the underlying Database IDs are not regular which is a common use case. for example if my record IDs are
[3, 10, 11, 13, 14]
and if I want to find the next element toid => 3
then the yielded result isoffset(3) => [13,14]
. This behaviour is explained clearly in docsoffset => Specifies the number of rows to skip before returning rows.