activerecord get next element

rubyonrails-talk
1

Hi, How would you get an SQL element, the next one and the previous one, but the definition of a next/previous element is not dependant only of his id but also of a status, exemple :

id status 1 true 2 false 3 true

Here the next element of 1 is 3

any idea ? Thanks

2

If I understand correctly you can specify an order in the find call that provides the records in whatever order you want.

Note also that if you do not specify an order then there is no guarantee in what order they will appear. They will not necessarily be in id order.

Colin

3

yes but that doesnt resolve my problem (I think), I don't want to select all the database, otherwise I just need to filter by status an order by id, I just want this 3 results :-/

4

yes but that doesnt resolve my problem (I think), I don't want to select all the database, otherwise I just need to filter by status an order by id, I just want this 3 results :-/

Do you mean that you know the id of a record, and you have a defined sort order, and you want to fetch just three records starting with the one before the known id, for the defined sort order?

Sorry, no idea. Anyone?

You could do two finds, fetch two records starting with your central one and the given order, then do another find of two records with the order reversed.

Colin

5

This might help. These are some class methods to do something similar.

   def self.prior_to id      find(:first, :conditions => ["#{primary_key} < ?", id], :order => "#{primary_key} DESC") ||        find(:first, :conditions => ["#{primary_key} >= ?", id], :order => "#{primary_key}")    end

   def self.next_after id      find(:first, :conditions => ["#{primary_key} > ?", id], :order => "#{primary_key}") ||        find(:first, :conditions => ["#{primary_key} <= ?", id], :order => "#{primary_key} DESC")    end

You'd want to adjust the conditions to also look at status and probably take a model instance rather than an id. Maybe even a list of the attributes that must be matched (just :status in your example).

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

6

thanks colin and rob, that's approximately what I have so far, I was hoping a solution to do it in one request :-/

7

Mike Mickey wrote:

thanks colin and rob, that's approximately what I have so far, I was hoping a solution to do it in one request :-/

The simplest way would be to use UNION on the two queries. You could also use subquery syntax to munge the criteria.

Best,

8

Better yet. Take these conditions and apply each as a named scope. Then, by chaining the calls you’ll get everything in one nice little query. So for example (assuming a default order of: “id ASC”):

In your model (lets call it Foo):

named_scope :for_status, lambda { |status|

{ :conditions => { :status => status } }

}

named_scope :find_previous, lambda { |id|

{ :conditions => [ “id < :id”, { :id => id } ] }

}

named_scope :find_subsequent, lambda { |id|

{ :conditions => [ “id > :id”, { :id => id } ] }

}

Then to fetch the desired record you can do this:

previous = Foo.for_status(true).find_previous(3).first

next = Foo.for_status(true).find_subsequent(1).last

Cheers,

Tim

9

Actually the calls should look like this:

previous = Foo.for_status(true).find_previous(3).last

next = Foo.for_status(true).find_subsequent(1).first

So, the .last and .first calls were transposed.

Cheers,

Tim

10

nice one ! Thanks