How to sort activerecord query by specific prority

Question

I am using rails 3 and postrges.

I would like order by a specific priority.

Something like:

Assignment.order(priority: ['best', 'good', 'bad'])

and this will return all activerecords first with 'best', then 'good', then 'bad'

I cannot seem to find anything like this. I do not need an array, it has to be activerecords.

Answer 1

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.