Rails Active 查询按特定顺序按多个值排序? [英] Rails Active query order by multiple values in specific order?
问题描述
我有一张桌子
Roles(id int,,name vachar(50),role enum(CAD,CA,CM,CV ))
我想选择按特定顺序按特定值排序的数据.我的活动模型查询:role.order('role asc') 然后结果:
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
但我想要这样的结果:
1 name1 CAD
2 name2 CM
3 name2 CA
谁能帮帮我?提前致谢
推荐答案
一个可移植的解决方案是将 CASE 语句用作 ORDER BY 中的内联映射:
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
))
请记住,您可以按您想要的任何表达式进行 ORDER BY,并且 CASE 当然是 SQL 中的一个表达式.
Keep in mind that you can ORDER BY any expression you want and a CASE certainly is an expression in SQL.
较新版本的 Rails 会希望您使用 Arel.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
)
)
)
如果列表是动态的,您可以构建一个 CASE 表达式:
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.
这篇关于Rails Active 查询按特定顺序按多个值排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!