ruby on rails 根据特定字段对查询结果做自定义排序

前几天在工作中遇到要根据多个国家的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)
}

最后,可能有些特殊情况没有考虑进去,欢迎讨论。
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 206,839评论 6 482
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 88,543评论 2 382
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 153,116评论 0 344
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 55,371评论 1 279
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 64,384评论 5 374
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,111评论 1 285
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,416评论 3 400
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,053评论 0 259
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 43,558评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,007评论 2 325
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,117评论 1 334
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,756评论 4 324
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,324评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,315评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,539评论 1 262
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,578评论 2 355
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,877评论 2 345

推荐阅读更多精彩内容

  • Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具(例如配置管理,服务发现,断路器,智...
    卡卡罗2017阅读 134,600评论 18 139
  • 1. Java基础部分 基础部分的顺序:基本语法,类相关的语法,内部类的语法,继承相关的语法,异常的语法,线程的语...
    子非鱼_t_阅读 31,587评论 18 399
  • 一. Java基础部分.................................................
    wy_sure阅读 3,793评论 0 11
  • 原文:https://my.oschina.net/liuyuantao/blog/751438 查询集API 参...
    阳光小镇少爷阅读 3,813评论 0 8
  • 落叶惹秋风 秋雨吹脸庞 九月秋夜凉 晨曦在他乡
    晨曦山鸡阅读 191评论 2 0