Hi, all. I''ve got a list of jobs I''m trying to sort based on
the
''location'' column. The location column is actually filled in
by a
method in the location model, and it''s "name" if the
locations.name
column is not null, otherwise it''s locations.address.
So, the long and short is that I''d like to figure out how to do this
in my job_controller.rb:
sort_jobs_by = ''locations.name if locations.name is not null,
otherwise use locations.address''
@jobs = Job.find(:all, :order => sort_jobs_by, :include => [:location])
Of course, "locations.name if locations.name is not null, otherwise
use locations.address" is not exactly valid SQL, and so that won''t
work. I need to know what to set sort_jobs_by that would accomplish
this.
I know that I can do this:
@jobs.sort! {|a,b| a.location_name <=> b.location_name}
(where location_name is an instance method that retrieves from the
job.location either its name or its address).
But that''s slower, I think, and anyway doesn''t seem to be
quite cricket.
Any suggestions?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Try sorting by both. Perhaps the NULL''s will show up on top, and the only "real" sorting will be done on the address value for those records. Bart --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
> Hi, all. I''ve got a list of jobs I''m trying to sort based on the > ''location'' column. The location column is actually filled in by a > method in the location model, and it''s "name" if the locations.name > column is not null, otherwise it''s locations.address. > > So, the long and short is that I''d like to figure out how to do this > in my job_controller.rb: > > sort_jobs_by = ''locations.name if locations.name is not null, > otherwise use locations.address'' > > @jobs = Job.find(:all, :order => sort_jobs_by, :include => [:location]) > > Of course, "locations.name if locations.name is not null, otherwise > use locations.address" is not exactly valid SQL, and so that won''t > work. I need to know what to set sort_jobs_by that would accomplish > this. > > I know that I can do this: > > @jobs.sort! {|a,b| a.location_name <=> b.location_name} > > (where location_name is an instance method that retrieves from the > job.location either its name or its address). > > But that''s slower, I think, and anyway doesn''t seem to be quite cricket. > > Any suggestions?If mysql... :order => "IFNULL(locations.name, locations.address)" If postgresql, :order => "COALESCE(locations.name, locations.address)" -philip --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Sorry for the delay, thanks for the info! That was just what I was after. Sadly, I then found out that not all of the fields are NULL; some are just empty strings. But I''m down the road a ways with it, and now I''ve learned about the IFNULL(), so mucho thanks! On 11/15/06, Philip Hallstrom <rails-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org> wrote:> > > Hi, all. I''ve got a list of jobs I''m trying to sort based on the > > ''location'' column. The location column is actually filled in by a > > method in the location model, and it''s "name" if the locations.name > > column is not null, otherwise it''s locations.address. > > > > So, the long and short is that I''d like to figure out how to do this > > in my job_controller.rb: > > > > sort_jobs_by = ''locations.name if locations.name is not null, > > otherwise use locations.address'' > > > > @jobs = Job.find(:all, :order => sort_jobs_by, :include => [:location]) > > > > Of course, "locations.name if locations.name is not null, otherwise > > use locations.address" is not exactly valid SQL, and so that won''t > > work. I need to know what to set sort_jobs_by that would accomplish > > this. > > > > I know that I can do this: > > > > @jobs.sort! {|a,b| a.location_name <=> b.location_name} > > > > (where location_name is an instance method that retrieves from the > > job.location either its name or its address). > > > > But that''s slower, I think, and anyway doesn''t seem to be quite cricket. > > > > Any suggestions? > > If mysql... :order => "IFNULL(locations.name, locations.address)" > > If postgresql, :order => "COALESCE(locations.name, locations.address)" > > -philip > > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---