sql - Rails Active query order by multiple values in specific order?

Question

I have a table

Roles(id int,,name vachar(50),role enum(CAD,CA,CM,CV ))

I want select data that order by specific values in specific order . My active model query: role.order('role asc') then the result:

1 name1 CAD
2 name2 CA
3 name2 CM

But I want the result like:

1 name1 CAD
2 name2 CM
3 name2 CA

Can anyone help me? Thanks in advance

Answer 1

A portable solution would be to use a CASE statement as an inlined map in your ORDER BY:

query.order(%q(
  case role
  when 'CAD' then 1
  when 'CM'  then 2
  when 'CA'  then 3
  end
))

Keep in mind that you can ORDER BY any expression you want and a CASE certainly is an expression in SQL.

Newer versions of Rails will want you to use Arel.sql rather than a raw string:

query.order(
  Arel.sql(
    %q(
      case role
      when 'CAD' then 1
      when 'CM'  then 2
      when 'CA'  then 3
      end
    )
  )
)

And if the list is dynamic, you can build a CASE expression:

array = %w[CAD CM CA]
q     = connection.method(:quote) # Or ApplicationRecord.connection.method(:quote)
cases = array.each_with_index.map { |e, i| "when #{q[e]} then #{i}" }
query.order(Arel.sql("case role #{cases.join(' ')} end"))

All the string manipulation is a bit ugly but it is perfectly safe and you'd usually hide it in a scope.