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?
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
scope :urgency_ordered, ->{order("CASE tasks.urgency WHEN 'HIGH' THEN 'a' WHEN 'MEDIUM' THEN 'b' WHEN 'LOW' THEN 'c' ELSE 'z' END ASC, id ASC")} (in case anyone else needs to use it the same way), but it does the job perfectly!
Date.today or a user argument (because the result of the scope depends on a variable). Here, in your case, the order clause does not depend on a variable, it is always the same way that the Task will be ordered.The scope body needs to be callable. error without it, and a little googling told me the lambda would fix it. It did in this case - though your rationale makes a lot of sense to me. For my own curiosity's sake, do you know why this might be? (Not to worry if not, my problem is solved after all!)
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"])
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.
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.
