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 Michael <pada51-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > 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 3If 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 :-/ On Sep 23, 2:38 pm, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> 2009/9/23 Bensoussan Michael <pad...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: > > > > > 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 > > 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
2009/9/23 mickeyben <pada51-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > 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> On Sep 23, 2:38 pm, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >> 2009/9/23 Bensoussan Michael <pad...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: >> >> >> >> > 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 >> >> 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 > > >
On Sep 23, 2009, at 7:49 AM, mickeyben wrote:> On Sep 23, 2:38 pm, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >> 2009/9/23 Bensoussan Michael <pad...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: >>> 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, >>> example : >> >>> id status >>> 1 true >>> 2 false >>> 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 :-/ >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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org
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 Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote:> On Sep 23, 2009, at 7:49 AM, mickeyben wrote: > > > > > > > On Sep 23, 2:38 pm, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: > >> 2009/9/23 Bensoussan Michael <pad...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: > >>> 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, > >>> example : > > >>> id status > >>> 1 true > >>> 2 false > >>> 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 :-/ > > 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 > R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org
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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
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 <
rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
>
> 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
> marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--
Tim Lowrimore
Coroutine LLC
516 Tennessee St., Suite 215
Memphis, TN 38103
office: 901.312.8818
mobile: 901.490.5325
http://www.coroutine.com
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 Lowrimore <tlowrimore-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>wrote:> 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 < > rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: > >> >> 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 >> marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org >> -- >> Posted via http://www.ruby-forum.com/. >> >> >> >> > > > -- > Tim Lowrimore > Coroutine LLC > 516 Tennessee St., Suite 215 > Memphis, TN 38103 > office: 901.312.8818 > mobile: 901.490.5325 > http://www.coroutine.com >-- Tim Lowrimore Coroutine LLC 516 Tennessee St., Suite 215 Memphis, TN 38103 office: 901.312.8818 mobile: 901.490.5325 http://www.coroutine.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
nice one ! Thanks On Sep 23, 6:26 pm, Tim Lowrimore <tlowrim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 Lowrimore <tlowrim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>wrote: > > > > > > > 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 < > > rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: > > >> 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 > >> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > >> -- > >> Posted viahttp://www.ruby-forum.com/. > > > -- > > Tim Lowrimore > > Coroutine LLC > > 516 Tennessee St., Suite 215 > > Memphis, TN 38103 > > office: 901.312.8818 > > mobile: 901.490.5325 > >http://www.coroutine.com > > -- > Tim Lowrimore > Coroutine LLC > 516 Tennessee St., Suite 215 > Memphis, TN 38103 > office: 901.312.8818 > mobile: 901.490.5325http://www.coroutine.com