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?

Salvage

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

Misapprehension

This is pretty brilliant. Looks ugly, but great solution IMO to an otherwise very sticky issue. – Fronniah

Answer 2

ActiveRecord::QueryMethods#in_order_of (Rails 7+)

---

Starting from Rails 7, there is a new method ActiveRecord::QueryMethods#in_order_of.

A quote right from the official Rails docs:

in_order_of(column, values)

Allows to specify an order by a specific set of values. Depending on your adapter this will either use a CASE statement or a built-in function.

User.in_order_of(:id, [1, 5, 3])
# SELECT "users".* FROM "users" ORDER BY FIELD("users"."id", 1, 5, 3)

---

It works with model enums as well:

class Book < ApplicationRecord
  enum status: [:proposed, :written, :published]
end

Book.in_order_of(:status, %w[written published proposed])

---

Sources:

• Official docs - ActiveRecord::QueryMethods#in_order_of.
• PR - Add ActiveRecord::QueryMethods#in_order_of.
• More examples.

Highway

This is great news! I always hoped Rails would incorporate a query method like this 👍 – Villosity

Answer 3

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

Negro

Answer 4

Thanks to this answer I came up with this:

order("role = 0 DESC, role = 1 DESC, role = 2 DESC")

Or, as a scope with optional arguments:

scope :order_by_roles, -> (first = :admin, second = :ordinary, third = :manager) { 
  order("role = #{User.roles[first]} DESC, role = #{User.roles[second]} DESC, role = #{User.roles[third]} DESC") 
}  

Salvage