前几天在工作中遇到要根据多个国家的code查询出对应的国家并将结果按code的顺序来排序的需求，但我们在rails中做查询时通常得到的结果都是有顺序的(下面都会选择id来做自定义排序)。
Role.where(id: [2, 1, 5]).map(&:id) #=> [1, 2, 5]
这里就和我上面所讲的需求不一致了，我们希望查询结果的顺序是[2, 1, 5]。想了一下没找到什么优雅的解决方式就在dash和某中文网站(可能是这个需求用中文不太好描述。。)搜索没结果后，转而在google上搜了下，挑了靠前的五到六个网页看了下后，发现方法基本都是那几种，这里就做一个小整合。
一.case...when相关方法或同类原理
这是看到的最多的方法，有很多变种。这里先做一个记录。
- case...when的思路原型是这样:
case :b
when :a then 1
when :b then 2
when :c then 3
end
#=> 2
这里第一种是应用sql语句的一种写法和order by连用，在查询完后做不规则排序。sql语句原型是
SELECT * FROM roles WHERE id IN (1, 2, 5)
ORDER BY CASE id
WHEN 2 THEN 0
WHEN 1 THEN 1
WHEN 5 THEN 2
ELSE 3 END;
#=> 2, 1, 5
下面的方法就是将该段sql语句用ruby表示出来
def find_ordered(ids)
order_clause = "CASE id "
ids.each_with_index do |id, index|
order_clause << sanitize_sql_array(["WHEN ? THEN ? ", id, index])
end
order_clause << sanitize_sql_array(["ELSE ? END", ids.length])
where(id: ids).order(order_clause)
end
Role.find_ordered([2, 1, 5]).map(&:id)
#=> [2, 1, 5]
- 第二种是同样的思想，先查询然后将查询结果的id与要求的id顺序做比较。
ids = [2, 1, 5]
records = Role.find(ids)
result = ids.collect {|id| records.detect {|x| x.id == id}}.map(&:id)
#=> [2, 1, 5]
也可以将id存为key，所在的那条记录存为value(这个也有多种方法，下面写较简单的两种)，再进行比较。
Role.find(ids).index_by(&:id).slice(*ids).values.map(&:id)
#=> [2, 1, 5]
ids = [2, 1, 5]
records = Role.find(ids).group_by(&:id)
result = ids.map {|id| records[id].first}.map(&:id)
#=> [2, 1, 5]
二.mysql的特殊方法
- mysql有个特性是可以按字段排序
ORDER BY FIELD
具体语法是:
SELECT id FROM roles WHERE id IN (1, 2, 5)
ORDER BY FIELD(id, 2, 1, 5);
#=> 2, 1, 5
用rails转化后就是
Role.where(id: ids).order("FIELD(id, #{ids.join(',')})").map(&:id)
#=> [2, 1, 5]
三.postgresql的特殊方法
- 那在pg中就无法使用mysql的field特性了，但是pg也有自己的方式来自定义排序。
position(substring in string)可以返回指定子字符串的位置
eg.position('om' in 'Thomas') #=> 3
ids = [2, 1, 5]
Role.where(id: ids).order("position(id::text in '#{ids.join(',')}')").map(&:id)
#=> [2, 1, 5]
ps: 通过这样的方式做自定义排序也存在问题，如果ids = [12, 2, 1, 5], 那么结果就会出现
User.where(id: ids).order("position(id::text in '#{ids.join(',')}')").map(&:id)
#=> [1, 12, 2, 5]
- 在postgresql 9.4版本开始，我们可以利用WITH ORDINALITY来设置返回值。那这里我们配合unnest(将一个数组扩展为多行记录)和JOIN使用，可以通过先新建行记录确定排序顺序然后通过表连接查询出对应顺序的记录
ids = [12, 2, 1, 5]
User.joins("JOIN unnest(array#{ids}) WITH ORDINALITY t(id, ord) USING (id) ORDER BY t.ord").map(&:id)
#=> [12, 2, 1, 5]
四.gem包:order_as_specified
- 通过order_as_specified也可以根据字段自定义排序
github地址
简单介绍：在要做查询的model中添加extend OrderAsSpecified,
Role.where(id: [2, 1, 5]).order_as_specified(id: [2, 1, 5]).map(&:id) #=> [2, 1, 5]
也可以在此基础上嵌套排序，具体可以直接看该gem包。
五.如果你没很多数据要查那最直接的方法。。
[2, 1, 5].map{|id| Role.find(id)}.map(&:id) #=> [2, 1, 5]
***
### 总结
从以上几种方法里可以看出，当你想要根据特定顺序查询数据时，除了通过ruby的方法来进行排序外，还可以通过各个数据库的特性来完成排序，当然还需要根据实际情况再做决定，我这里因为继承关系只能在pg的方法的基础上再做修改了。。
scope :get_and_order_supplier_cal_popular_country, -> (codes) {
sql = sanitize_sql_array(
["JOIN unnest(array[?]) WITH ORDINALITY t(code, ord) USING (code)
WHERE type = 'Country' ORDER BY t.ord", codes] )
joins(sql)
}
最后，可能有些特殊情况没有考虑进去，欢迎讨论。