activerecord get next element

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

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

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 :-/

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

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

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

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,

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

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

nice one !
Thanks