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.