Rails - order on column's values (priority column)

Question

I've an array priority = ['HIGH', 'MEDIUM', 'LOW'] which is used to set an 'urgency' database column. I'd like to retrieve the data sorted by priority, though applying Task.order(:urgency) returns the results alphabetically (i.e. HIGH, LOW, MEDIUM).

I'm using PostgreSQL for the database.

I'd (obviously) like these to return from high to low priority. Is there a simple way to implement this, perhaps using the values' positions in the array?

Answer 1

A simple CASE WHEN can do the trick (postgreSQL syntax used here):

scope :urgency_ordered {
  order(<<-SQL)
    CASE tasks.urgency 
    WHEN 'HIGH' THEN 'a' 
    WHEN 'MEDIUM' THEN 'b' 
    WHEN 'LOW' THEN 'c' 
    ELSE 'z' 
    END ASC, 
    id ASC
  SQL
}

Call it this way:

Task.urgency_ordered

Answer 2

I am late to the party, but I am surprised none has come up with this answer yet: If you are using MySQL (untested but should work for Postgres, too), there is a shorter, safer and more generic way than any of the previous answers. It works with any field and is protected against sql injection, so you can pass any list of values from the user.

Add the following scope to your model or ApplicationRecord:

class Task < ActiveRecord::Base
  scope :order_by_field, ->(field, values) {
    order(sanitize_sql_for_order(["field(#{field}, ?)", values]))
  }
end

You can now call the scope directly on your relation:

tasks.ordered_by_field(:priority, ["high", "medium", "low"])

Answer 3

If using Rails 4.1+, consider using Active Record enums (Release Notes and Documentation) for a DB agnostic solution:

class Task < ActiveRecord::Base
  enum priority: [ :high, :medium, :low ] 
  # Or enum priority: { high: 0, medium: 1, low: 2 }

  scope :priority_order, ->{ order(:priority) }
  scope :reverse_priority_order, ->{ order(priority: :desc) }
end

This will require a new integer column for the priority, and copying/moving the text data that you have to the new integer column, which could be done in a migration. then calling Task.priority_order should do the trick.

Answer 4

It is late but here my 2cents. (Rails 4.2.1)

If you use the solution directly like this:

class Task < ActiveRecord::Base
    enum priority: { high: 0, medium: 1, low: 2 }
    scope :priority_order, order(:priority)
end

You will get this error: The scope body needs to be callable.

You need to call the scope like this.

scope :priority_order, -> {
    order(:priority)
}

define it like Proc or Lambda.

Why, you may ask :)

It makes sure what you have in the block is used each time the scope is triggered.