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

Go To StackoverFlow.com

1

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

2009-06-16 20:06
by NoName


1

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)
2009-06-16 20:29
by BradC
Ahh, interesting. I had read a little about dropping SQL functions into Rails but did not know about coalesce. Will try this out. Thanks - NoName 2009-06-19 19:09


0

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

2009-06-16 20:28
by John Topley
This will order the entries without distance-edited after all those with distance-edited, which is wron - Tor Erik Linnerud 2009-06-16 23:30
OK, I didn't have chance to try it out - John Topley 2009-06-17 06:22
I should have mentioned that I did try that and, as Tor suggested, it did not intermingle the results for an accurate sort - NoName 2009-06-19 19:08
Ads