Rails Active query order by multiple values in spe

问题:

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

回答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.