How to sort activerecord query by specific prority

In newer versions of Rails you will get an ActiveRecord::UnknownAttributeReference (Query method called with non-attribute argument(s) error if you pass raw SQL to .order().

You need to wrap your SQL query with Arel.sql(). You can also use ruby's Here Doucment syntax, to write multi line SQL statements, and squish to make it more readable in the console.

So the whole thing becomes:

Assignment.order(
  Arel.sql(<<-SQL.squish
    CASE
      WHEN priority = 'best' THEN '1'
      WHEN priority = 'good' THEN '2'
      WHEN priority = 'bad' THEN '3'
    END
  SQL
  )
)

Order can be any SQL code. You can use a CASE statement to map your values to values that naturally sort in the correct order.

Assignment.order("
    CASE
      WHEN priority = 'best' THEN '1'
      WHEN priority = 'good' THEN '2'
      WHEN priority = 'bad' THEN '3'
    END")

Even better, you could move this logic to the model so that it's easier to call from controllers:

class Assignment < ActiveRecord::Base
  ...
  def self.priority_order
    order("
        CASE
          WHEN priority = 'best' THEN '1'
          WHEN priority = 'good' THEN '2'
          WHEN priority = 'bad' THEN '3'
        END")
  end
end

Then you can just call Assignment.priority_order to get your sorted records.

If this column is sortable in the view, add a parameter to the method for direction:

def self.priority_order(direction = "ASC")
  # Prevent injection by making sure the direction is either ASC or DESC
  direction = "ASC" unless direction.upcase.match(/\ADESC\Z/)
  order("
      CASE
        WHEN priority = 'best' THEN '1'
        WHEN priority = 'good' THEN '2'
        WHEN priority = 'bad' THEN '3'
      END #{direction}")
end

Then, you would call Assignment.priority_order(params[:direction]) to pass in the sorting from the controller.