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

2009/9/23 Bensoussan M. [email protected]:

3 true

Here the next element of 1 is 3

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

2009/9/23 mickeyben [email protected]:

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

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

On Sep 23, 3:08 pm, Rob B. [email protected]

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

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

On Wed, Sep 23, 2009 at 7:35 AM, Marnen Laibow-Koser <
[email protected]> wrote:


Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Posted via http://www.ruby-forum.com/.


Tim L.
Coroutine LLC
516 Tennessee St., Suite 215
Memphis, TN 38103
office: 901.312.8818
mobile: 901.490.5325
http://www.coroutine.com

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

On Wed, Sep 23, 2009 at 10:05 AM, Tim L.
[email protected]wrote:

                      { :conditions => [ "id < :id", { :id => id } ] }

Posted via http://www.ruby-forum.com/.
office: 901.312.8818
mobile: 901.490.5325
http://www.coroutine.com


Tim L.
Coroutine LLC
516 Tennessee St., Suite 215
Memphis, TN 38103
office: 901.312.8818
mobile: 901.490.5325
http://www.coroutine.com

On Sep 23, 2009, at 7:49 AM, mickeyben wrote:

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

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 B. http://agileconsultingllc.com
[email protected]

nice one !
Thanks