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.

Caraway

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.

Nihility

Answer 2

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
  )
)

Isidore

Answer 3

The ActiveRecord Query method in_order_of has been introduced in Rails 7.

See https://edgeapi.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-in_order_of

Allows to specify an order by a specific set of values.

User.in_order_of(:id, [1, 5, 3])
# SELECT "users".* FROM "users"
#   WHERE "users"."id" IN (1, 5, 3)
#   ORDER BY CASE
#     WHEN "users"."id" = 1 THEN 1
#     WHEN "users"."id" = 5 THEN 2
#     WHEN "users"."id" = 3 THEN 3
#   END ASC

Ellissa