Rails ActiveRecord – Order query by matching number

I have a model called Location. This table has 2 fields I want to order by: rating (integer) and name (string).

I want to order alphabetically by name, but if the rating is 5, I want that rating to come first. If the rating is anything else except for 5, I want them to unbiasedly show up in alphabetical order after the 5’s.

Is this possible to do with SQL? I know I can obviously do this with Ruby’s Array#sort, but I want to do this as a query.

Answer

Yes, you can do this with a bit of SQL. You’ll want to include a case expression in your ORDER BY:

case rating when 5 then 0 else 1 end

That’s roughly equivalent to rating == 5 ? 0 : 1 in Ruby so it will put all the rating 5 locations first and the other locations after. Then sort by name:

Location.order(Arel.sql('case rating when 5 then 0 else 1 end, name'))

Of course, you can hide that in a scope to make things easier to read and understand.