SQL Rails：获取下一个/上一个记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7394088/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails: Get next / previous record
提问by Andrew
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 idorder or the next/previous photo in created_atorder.
在 photo#show 视图中，我想显示“来自该用户的更多信息”，并显示来自该用户的下一张和上一张照片。我会同意这些是按id顺序的下一张/上一张照片或按顺序的下一张/上一张照片created_at。
How would you write that kind of query for one next / previous photo, or for multiple next / previous photos?
您将如何为下一张/上一张照片或多张下一张/上一张照片编写这种查询？
回答by Harish Shetty
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
回答by Cremz
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.nextin my views afterwards.
这样它就会循环，从最后一个项目到第一个项目，反之亦然。我只是@item.next事后提出我的意见。
回答by jwilcox09
Not sure if this is a change in Rails 3.2+, but instead of:
不确定这是否是 Rails 3.2+ 中的更改，而是：
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.
好像是“order by”错了，所以先给你数据库中的第一条记录，因为如果你有比当前低的3项，[1,3,4]，那么“第一条”就是1，但是最后一个就是你要找的那个。您也可以在 where 之后应用排序，但这是一个额外的步骤。
回答by jinstrider2000
This should work, and I think it's more efficient than the other solutions in that it doesn't retrieve every recordabove 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 :)
这是我在 StackOverflow 上发布的第一个答案，这个问题已经很老了……我希望有人看到它:)
回答by Zernel
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
回答by Jordan Schwartz
You can pass some options into the where method:
您可以将一些选项传递给 where 方法：
For the next photo:
下一张照片：
Photo.where(:user_id => current_user.id, :created_at > current_photo.created_at).order("created_at").first
Previous photo
上一张照片
Photo.where(:user_id => current_user.id, :created_at < current_photo.created_at).order("created_at").last
I may have the first/last mixed up.
我可能把第一个/最后一个搞混了。
回答by NealJMD
class Photo < ActiveRecord::Base
belongs_to :user
default_scope { order('published_at DESC, id DESC') }
def next
current = nil
user.photos.where('published_at >= ?', published_at).each do |p|
if p.id == id then break else current = p end
end
return current
end
def previous
current = nil
user.photos.where('published_at <= ?', published_at).reverse.each do |p|
if p.id == id then break else current = p end
end
return current
end
end
I found that the answers already here did not serve my case. Imagine that you want a previous or next based on the date published, but some photos are published on the same date. This version will loop through the photos in the order they are rendered on the page, and take the ones before and after the current one in the collection.
我发现这里的答案并不适用于我的情况。想象一下，您想要基于发布日期的上一张或下一张照片，但有些照片是在同一日期发布的。此版本将按照照片在页面上呈现的顺序循环遍历照片，并取集合中当前照片之前和之后的照片。
回答by charlysisto
回答by Oldwolf Pang
Modify your app/models/application_record.rb to the following code:
将您的 app/models/application_record.rb 修改为以下代码：
class ApplicationRecord < ActiveRecord::Base
self.abstract_class = true
def next
self.class.where("id > ?", id).order("id ASC").first || self.class.first
end
def previous
self.class.where("id < ?", id).order("id DESC").first || self.class.last
end
end
Then you can use next() and previous() in all your models.
然后您可以在所有模型中使用 next() 和 previous() 。
