前幾天在工作中遇到要根據(jù)多個(gè)國(guó)家的code查詢出對(duì)應(yīng)的國(guó)家并將結(jié)果按code的順序來(lái)排序的需求，但我們?cè)趓ails中做查詢時(shí)通常得到的結(jié)果都是有順序的(下面都會(huì)選擇id來(lái)做自定義排序)。
Role.where(id: [2, 1, 5]).map(&:id) #=> [1, 2, 5]
這里就和我上面所講的需求不一致了，我們希望查詢結(jié)果的順序是[2, 1, 5]。想了一下沒(méi)找到什么優(yōu)雅的解決方式就在dash和某中文網(wǎng)站(可能是這個(gè)需求用中文不太好描述。。)搜索沒(méi)結(jié)果后，轉(zhuǎn)而在google上搜了下，挑了靠前的五到六個(gè)網(wǎng)頁(yè)看了下后，發(fā)現(xiàn)方法基本都是那幾種，這里就做一個(gè)小整合。
一.case...when相關(guān)方法或同類原理
這是看到的最多的方法，有很多變種。這里先做一個(gè)記錄。
- case...when的思路原型是這樣:
case :b
when :a then 1
when :b then 2
when :c then 3
end
#=> 2
這里第一種是應(yīng)用sql語(yǔ)句的一種寫法和order by連用，在查詢完后做不規(guī)則排序。sql語(yǔ)句原型是
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語(yǔ)句用ruby表示出來(lái)
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]
- 第二種是同樣的思想，先查詢?nèi)缓髮⒉樵兘Y(jié)果的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(這個(gè)也有多種方法，下面寫較簡(jiǎn)單的兩種)，再進(jìn)行比較。
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有個(gè)特性是可以按字段排序
ORDER BY FIELD
具體語(yǔ)法是:
SELECT id FROM roles WHERE id IN (1, 2, 5)
ORDER BY FIELD(id, 2, 1, 5);
#=> 2, 1, 5
用rails轉(zhuǎn)化后就是
Role.where(id: ids).order("FIELD(id, #{ids.join(',')})").map(&:id)
#=> [2, 1, 5]
三.postgresql的特殊方法
- 那在pg中就無(wú)法使用mysql的field特性了，但是pg也有自己的方式來(lái)自定義排序。
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: 通過(guò)這樣的方式做自定義排序也存在問(wèn)題，如果ids = [12, 2, 1, 5], 那么結(jié)果就會(huì)出現(xiàn)
User.where(id: ids).order("position(id::text in '#{ids.join(',')}')").map(&:id)
#=> [1, 12, 2, 5]
- 在postgresql 9.4版本開(kāi)始，我們可以利用WITH ORDINALITY來(lái)設(shè)置返回值。那這里我們配合unnest(將一個(gè)數(shù)組擴(kuò)展為多行記錄)和JOIN使用，可以通過(guò)先新建行記錄確定排序順序然后通過(guò)表連接查詢出對(duì)應(yīng)順序的記錄
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
- 通過(guò)order_as_specified也可以根據(jù)字段自定義排序
github地址
簡(jiǎn)單介紹：在要做查詢的model中添加extend OrderAsSpecified,
Role.where(id: [2, 1, 5]).order_as_specified(id: [2, 1, 5]).map(&:id) #=> [2, 1, 5]
也可以在此基礎(chǔ)上嵌套排序，具體可以直接看該gem包。
五.如果你沒(méi)很多數(shù)據(jù)要查那最直接的方法。。
[2, 1, 5].map{|id| Role.find(id)}.map(&:id) #=> [2, 1, 5]
***
### 總結(jié)
從以上幾種方法里可以看出，當(dāng)你想要根據(jù)特定順序查詢數(shù)據(jù)時(shí)，除了通過(guò)ruby的方法來(lái)進(jìn)行排序外，還可以通過(guò)各個(gè)數(shù)據(jù)庫(kù)的特性來(lái)完成排序，當(dāng)然還需要根據(jù)實(shí)際情況再做決定，我這里因?yàn)槔^承關(guān)系只能在pg的方法的基礎(chǔ)上再做修改了。。
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)
}
最后，可能有些特殊情況沒(méi)有考慮進(jìn)去，歡迎討論。