Custom order based on Enum values

Question

I have this defined Enum for roles:

enum role: {ordinary: 0, manager: 1, admin: 2}

I would liked to order a collection of objects in the following order:

admin (first all admins)
ordinary (then all ordinaries)
manager (and lastly all managers)

Is this possible at all?

Answer 1

A solution for this:

class YourModel < ActiveRecord::Base
  ROLE_ORDERS = [2, 0, 1]

  scope :order_by_role, -> {
    order_by = ['CASE']
    ROLE_ORDERS.each_with_index do |role, index|
      order_by << "WHEN role=#{role} THEN #{index}"
    end
    order_by << 'END'
    order(order_by.join(' '))
  }
end

Then your query will be simple like this:

YourModel.order_by_role

The generated query is:

SELECT * from your_models
ORDER BY ( CASE
           WHEN role=2 THEN 0
           WHEN role=0 THEN 1
           WHEN role=1 then 2
           END
         )

Good reference from this

Answer 2

Starting Rails 6.0, @hieu-pham's solution will throw this deprecation warning: "Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s) [..] Non-attribute arguments will be disallowed in Rails 6.1. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql()."

So, building up on both his answer and @fellow-stranger's, I'd suggest this:

class YourModel < ActiveRecord::Base
  ROLE_ORDERS = [2, 0, 1]
  scope :order_by_role, -> { order(Arel.sql(ROLE_ORDERS.map{ |role| "role=#{role} DESC" }.join(', '))) }
end

Then this is used in your code just as in @hieu-pham's solution...

YourModel.order_by_role

... which generates this query:

SELECT * from your_models
ORDER BY role = 2 DESC, role = 0 DESC, role = 1 DESC