Sorting on one of two columns in database

Rails
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

(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

3

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

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!