Ordering by specific value in Activerecord

Question

In Ruby on Rails, I'm trying to order the matches of a player by whether the current user is the winner.

The sort order would be:

• Sort by whether the current user is the winner
• Then sort by created_at, etc.

I can't figure out how to do the equivalent of :

Match.all.order('winner_id == ?', @current_user.id)

I know this line is not syntactically correct but hopefully it expresses that the order must be:

1) The matches where the current user is the winner 2) the other matches

Answer 1

You can use a CASE expression in an SQL ORDER BY clause. However, AR doesn't believe in using placeholders in an ORDER BY so you have to do nasty things like this:

by_owner = Match.send(:sanitize_sql_array, [ 'case when winner_id = %d then 0 else 1 end', @current_user.id ])
Match.order(by_owner).order(:created_at)

That should work the same in any SQL database (assuming that your @current_user.id is an integer of course).

You can make it less unpleasant by using a class method as a scope:

class Match < ActiveRecord::Base
  def self.this_person_first(id)
    by_owner = sanitize_sql_array([ 'case when winner_id = %d then 0 else 1 end', id])
    order(by_owner)
  end
end

# and later...
Match.this_person_first(@current_user.id).order(:created_at)

to hide the nastiness.

Answer 2

If you want to do sorting on the ruby side of things (instead of the SQL side), then you can use the Array#sort_by method:

query.sort_by(|a| a.winner_id == @current_user.id)

If you're dealing with bigger queries, then you should probably stick to the SQL side of things.