14

I need to get the previous and next active record objects with Rails. I did it, but don't know if it's the right way to do that.

What I've got:

Controller:

@product = Product.friendly.find(params[:id])

order_list = Product.select(:id).all.map(&:id)

current_position = order_list.index(@product.id)

@previous_product = @collection.products.find(order_list[current_position - 1]) if order_list[current_position - 1]
@next_product = @collection.products.find(order_list[current_position + 1]) if order_list[current_position + 1]

@previous_product ||= Product.last
@next_product ||= Product.first

product_model.rb

default_scope -> {order(:product_sub_group_id => :asc, :id => :asc)}

So, the problem here is that I need to go to my database and get all this ids to know who is the previous and the next.

Tried to use the gem order_query, but it did not work for me and I noted that it goes to the database and fetch all the records in that order, so, that's why I did the same but getting only the ids.

All the solutions that I found was with simple order querys. Order by id or something like a priority field.

William Weckl
  • 2,435
  • 4
  • 26
  • 43

4 Answers4

38

Write these methods in your Product model:

class Product

  def next
    self.class.where("id > ?", id).first
  end

  def previous
    self.class.where("id < ?", id).last
  end

end

Now you can do in your controller:

@product = Product.friendly.find(params[:id])

@previous_product = @product.next
@next_product = @product.previous

Please try it, but its not tested. Thanks

Rails Guy
  • 3,836
  • 1
  • 20
  • 18
  • 2
    The problem is that I'm not ordering by ID. – William Weckl Sep 04 '14 at 13:35
  • 2
    It's still a good example on where to put these methods. Can be put to use. – D-side Sep 04 '14 at 14:10
  • I agree. How do you call it in the view? –  Mar 06 '16 at 13:25
  • 4
    I up voted Rails Guy's answer, but wanted to address the questions about ordering by something other than id, e.g. created_at. Per requests from @Mogsdad and others, I changed my answer to a comment. To order by created_at, change the implementation of the suggested `next` method to include: `self.class.where("created_at > ?", created_at).order(created_at: :asc).first` and your `previous` method to include `self.class.where("created_at < ?", created_at).order(created_at: :asc).last` – kevinsapp May 14 '16 at 17:12
  • Your solution helped me. So elegant. – chickensmitten Jul 22 '16 at 06:48
  • thanks for the ordering solution, just what I needed. – Asan Sep 24 '16 at 21:22
  • Works, but you will have to add `.order('anything ASC')` after the .where query in case you order by something else than ID. – SEJU May 31 '22 at 14:07
4

I think it would be faster to do it with only two SQL requests, that only select two rows (and not the entire table). Considering that your default order is sorted by id (otherwise, force the sorting by id) :

@previous_product = Product.where('id < ?', params[:id]).last
@next_product = Product.where('id > ?', params[:id]).first

If the product is the last, then @next_product will be nil, and if it is the first, then, @previous_product will be nil.

Jean-Théo
  • 402
  • 3
  • 13
  • The problem is that my order is not by the id and I need to maintain that way. default_scope -> {order(:product_sub_group_id => :asc, :id => :asc)} – William Weckl Sep 04 '14 at 12:56
  • Turns out, `first` and `last` acually do `LIMIT 1`. So it **doesn't** load tons of objects and only take one. It actually retrieves only one object. *Just a note.* – D-side Sep 04 '14 at 13:06
  • @WilliamWeckl then replace `id` with the field you are comparing, as long as there are no equal ordering values: multiple criteria can be achieved via a construct like `(> first) or (=first, >second)`. – D-side Sep 04 '14 at 13:08
  • Otherwise, maybe you could try something with row_number : http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by – Jean-Théo Sep 04 '14 at 13:18
  • @D-side my field that I use to order is a group of products, in my list I get all products ordered by groups, when the product is from same group, than I order by ID. – William Weckl Sep 04 '14 at 13:26
  • @Jean-Daube can I do that in postgres and with active record methods? – William Weckl Sep 04 '14 at 13:27
  • 1
    @WilliamWeckl I've reflected that as well, in a bit unclear way: you could form conditions like `(group_id = ? AND id > ?) OR group_id > ?`. A certainly not so clean solution... – D-side Sep 04 '14 at 13:32
  • 1
    @WilliamWeckl : Yes, it seems that row_number exists for postGre : http://stackoverflow.com/questions/3397121/how-to-show-row-numbers-in-postgresql-query Unfortunately, I don't think you'll be able to handle it through clean active record methods. You will have to use Model.find_by_sql(...). – Jean-Théo Sep 04 '14 at 13:32
  • 1
    @D-side not so clean but still quite effective ! But when you have such nested conditions, shouldn't you start with the one that has the more chances to be true for the less efforts, so that you don't have to execute the second one ? (and therefore do group_id > ? OR (group_id = ? AND id > ?) ) ?? – Jean-Théo Sep 04 '14 at 13:36
  • @WilliamWeckl I doubt there's much room for optimization, since only one object will be fetched. I've ordered my conditions in the same order you might be ordering results in: same group first, next groups next. – D-side Sep 04 '14 at 13:41
  • @D-side I'm a little confused about what solution fits better. I understand the two solutions will work, but mine works too. Just working is not what I'm looking for. Your solution and Jean-Daube's solution looks like more performative than mine but, is there any rails way or other conventions to do something like that? – William Weckl Sep 04 '14 at 14:02
  • 1
    @WilliamWeckl I'm not aware of any standard way to achieve that. Since this can only (probably) be implemented manually, the Rails Way can only be obeyed in a sense of where to place that code. See another answer on how to pack these methods into the model class for easy referencing. You may even use `arel` to avoid plain SQL conditions if you want. – D-side Sep 04 '14 at 14:09
  • @D-side Did it. `.where('(products.product_sub_group_id = :product_sub_group_id and products.id > :id) OR products.product_sub_group_id > :product_sub_group_id', {:product_sub_group_id => self.product_sub_group_id, :id => self.id})` Haven't understand the advantages of using `AREL`. Wanna post it as answer to I can set as the solution? – William Weckl Sep 04 '14 at 17:39
  • 1
    @WilliamWeckl it's just Ruby way of writing SQL. 100% Ruby is a little easier to maintain. – D-side Sep 04 '14 at 20:32
2

There's no easy out-of-the-box solution.

A little dirty, but working way is carefully sorting out what conditions are there for finding next and previous items. With id it's quite easy, since all ids are different, and Rails Guy's answer describes just that: in next for a known id pick a first entry with a larger id (if results are ordered by id, as per defaults). More than that - his answer hints to place next and previous into the model class. Do so.

If there are multiple order criteria, things get complicated. Say, we have a set of rows sorted by group parameter first (which can possibly have equal values on different rows) and then by id (which id different everywhere, guaranteed). Results are ordered by group and then by id (both ascending), so we can possibly encounter two situations of getting the next element, it's the first from the list that has elements, that (so many that):

  • have the same group and a larger id
  • have a larger group

Same with previous element: you need the last one from the list

  • have the same group and a smaller id
  • have a smaller group

Those fetch all next and previous entries respectively. If you need only one, use Rails' first and last (as suggested by Rails Guy) or limit(1) (and be wary of the asc/desc ordering).

Community
  • 1
  • 1
D-side
  • 9,150
  • 3
  • 28
  • 44
  • @glebm The author explicitly stated that he tried to use it. Still, `order_query` is **not** an out-of-the-box solution, it's a third-party library. I understand your concern, as the library's maintainer, it would be better to clarify with the author, what exactly didn't work. – D-side Sep 07 '14 at 19:26
2

This is what order_query does. Please try the latest version, I can help if it doesn't work for you:

class Product < ActiveRecord::Base
  order_query :my_order,
    [:product_sub_group_id, :asc], 
    [:id, :asc]     
  default_scope -> { my_order } 
end

@product.my_order(@collection.products).next
@collection.products.my_order_at(@product).next

This runs one query loading only the next record. Read more on Github.

glebm
  • 20,282
  • 8
  • 51
  • 67