Rails: Custom ordering of records

Question

Does ruby or rails provide a method to order strings in a specified order? Say that I have the following priorities "Severe, High, Medium, Low".

These priorities are not going to change frequently (if at all). I have a Task model with a priority column:

tasks
  - id (integer)
  - name (string)
  - priority (string)

I'd like to get an array of all the tasks ordered by priority. Since the logical order does not follow alphabetical order, it's not possible to simply order by the priority column:

Task.all(:order => :priority)

What I've done is create a Priority model and defined the associations: Task belongs_to Priority. In the priorities table, I then assigned each priority name a value and ordered by that value. Is there a better way to do this? I'd rather not have a priorities table at all and declare a PRIORITY constant (as a hash), or simply specify the priority as a string in the tasks table.

Answer 1

You could use a case statement in your where clause. It's a little ugly but should work:

class Task
  PRIORITIES_ORDERED = ['high', 'medium', 'low']

  # Returns a case statement for ordering by a particular set of strings
  # Note that the SQL is built by hand and therefore injection is possible,
  # however since we're declaring the priorities in a constant above it's
  # safe.
  def self.order_by_case
    ret = "CASE"
    PRIORITIES_ORDERED.each_with_index do |p, i|
      ret << " WHEN priority = '#{p}' THEN #{i}"
    end
    ret << " END"
  end

  named_scope :by_priority, :order => order_by_case

end

And then when you want them ordered by priority, you can do something like this:

Task.all.by_priority

Of course as other people have mentioned, it's cleaner to make a foreign key to a Priority table instead of a text string column. In the Priorty table, you could add a position column as an integer that you could sort by, and plug-ins exist to make this easier.

Answer 2

Here's up updated version that works for rails 4.1 and a little customized:

  PRIORITIES_ORDERED = ['Unknown', 'Critical', 'Warning', 'Ok']
  def self.order_by_case
    ret = "CASE"
    PRIORITIES_ORDERED.each_with_index do |p, i|
      ret << " WHEN status = '#{p}' THEN #{i}"
    end
    ret << " END"
  end

  scope :order_by_priority, -> { order(order_by_case) }