Sorting on one of two columns in database

rubyonrails-talk
1

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?

2

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

3

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

4

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!