How to order find results on value from either of 2 columns? (which ones depends) (Rails)

Question

Hi,

Total noob question. Would greatly appreciate any pointers.

I have a rails model that includes 2 possible values for a concept, e.g., distance (a value from a GPS file and an edited value). It's possible to have a value in either column (just file or just manual), both (manual overwrites file in this case), or neither:

distance-file     distance-edited
9
10                11
                  12

In several places in my app, I want to do a find and sort the results by distance, where file vs. edited matters only in that I want to use distance-edited when present.

Because I do this sort in many places, ideally I'd like to centralize the logic for picking which distance value to use somewhere so in each find I can simply sort on distance.

What's the right way to do this?

Thank you!

Olivia

Answer 1

I think that you should be able to simply pass :order => 'distance-edited, distance-file' as a condition to your find.

Answer 2

So just to restate, you want to use distance-edited as the primary measure when it is present, and distance-file when it is not? You'll need something like:

ORDER BY COALESCE(distance-edited, distance-file)

Coalesce means "use the first value in this list that isn't NULL", and is a MS SQL-specific function. For other databases, you'll need to use the equivalent statement supported by that system.

What if both are NULL? Do you want it at the end of the list, or the beginning? You could us a "default" value by adding a third value in the coalesce list:

ORDER BY COALESCE(distance-edited, distance-file, 100)