Rails使用model.find_by_sql手写sql查询无法使用分页gem的解决方法

Rails提供了数据库查询方法以供我们快捷的去查询数据，以至于让我们不需要太懂sql的查询语句就可以通过Rails提供的方法查询出我们所需要的数据。
比如说：

Order.joins(:user).where(users: { id: 1 })

但是，实际工作中使用到的sql可能并不是那么简单，所以有些时候我们可能还需要使用更复杂的sql语句去进行查询。

比如说以下查询语句

SELECT
  *
From
(
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2018-3-1' 
AND o.completed_at <= '2018-3-31' 
GROUP BY
   ss.site_id
Union All
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2017-3-1' 
AND o.completed_at <= '2017-3-31' 
GROUP BY
   ss.site_id
) as part_data

这个其实算是简单的，最开始想到的或许是用Rails提供的model.find_by_sql来查


sql = <<-SQL
SELECT
  *
From
(
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2018-3-1' 
AND o.completed_at <= '2018-3-31' 
GROUP BY
   ss.site_id
Union All
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2017-3-1' 
AND o.completed_at <= '2017-3-31' 
GROUP BY
   ss.site_id
) as part_data
SQL

orders_data = Order.find_by_sql(sql)

得出如下


image.png

然后我们查看一下数据

=> [#<Order:0x00007f9e58ed9560 id: nil>,
 #<Order:0x00007f9e58ed8f20 id: nil>,
 #<Order:0x00007f9e58ed88e0 id: nil>,
 #<Order:0x00007f9e58ed8480 id: nil>,
 #<Order:0x00007f9e58ed3d40 id: nil>,
 #<Order:0x00007f9e58ed3890 id: nil>]
[7] pry(main)> orders_data.first.name
=> "123Ink"
[8] pry(main)>

是能查出数据的
现在现在查出来的数据对象比较少，可以直接用拿到前台页面直接each使用。但是，如果查出的数据对象太多了，一次查询出来可能会很慢，放到前台有可能还会撑爆页面，会产生浏览障碍，这个时候可能需要进行分页显示。那么拿我们平时很常用的gem 'kaminari'来做分页吧。

[8] pry(main)> orders_data = Order.find_by_sql(sql).page(1).per(2)
  Order Load (5720.8ms)  SELECT
  *
From
(
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2018-3-1'
AND o.completed_at <= '2018-3-31'
GROUP BY
   ss.site_id
Union All
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2017-3-1'
AND o.completed_at <= '2017-3-31'
GROUP BY
   ss.site_id
) as part_data

NoMethodError: undefined method `page' for #<Array:0x00007f9e58e4e7f8>
from (pry):59:in `<main>'
[9] pry(main)>

啊~~~ 报错了
为什么会这样呢？？？

在rails中kaminari的源码中
类似.page .per方法是放在Kaminari::ActiveRecordExtension模块里面

 ActiveSupport.on_load(:active_record) do
  require 'kaminari/models/active_record_extension'
  ::ActiveRecord::Base.send :include, Kaminari::ActiveRecordExtension
end

那么我们看看orders_data是什么？

[9] pry(main)> orders_data.class
=> Array
[10] pry(main)>

Array 数组？？？
原来model.find_by_sql生成的数据返回值是一个数组，而不是集成ActiveRecord::Base的对象数组。这下就明白是什么原因导致无法使用kaminari来帮助我们分页了。
现在到了这里，如果要进行分页，还得通过写方法来分页，实在是令人觉得麻烦。那么，有办法既能在Rails提供的方法里进行如此复杂的查询，还能使用kaminari来帮助我们分页吗？
答案是有的，而且也非常简单。只要把sql往里塞就好。
还是拿上面的sql做例子吧
只需要这样

orders_data = Order.select("*").from("(
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2018-3-1'
AND o.completed_at <= '2018-3-31'
GROUP BY
   ss.site_id
Union All
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2017-3-1'
AND o.completed_at <= '2017-3-31'
GROUP BY
   ss.site_id
) as part_data")

我们看下结果

[14] pry(main)> orders_data
=> [#<Order:0x00007f9e61e5a900 id: nil>,
 #<Order:0x00007f9e61e5a798 id: nil>,
 #<Order:0x00007f9e61e5a630 id: nil>,
 #<Order:0x00007f9e61e5a4c8 id: nil>,
 #<Order:0x00007f9e61e5a360 id: nil>,
 #<Order:0x00007f9e61e5a1f8 id: nil>]

我们会发现，数据出来了
然后我们看一下它的类

[15] pry(main)> orders_data.class
=> Order::ActiveRecord_Relation
[16] pry(main)>

是Order::ActiveRecord_Relation，既然如此，那么我们是不是就可以使用kaminari来帮助我们分页了呢？

[16] pry(main)> orders_data.page(1).per(1)
  Order Load (5680.1ms)  SELECT  * FROM (
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2018-3-1'
AND o.completed_at <= '2018-3-31'
GROUP BY
   ss.site_id
Union All
SELECT
  ss.name AS name,
  count(DISTINCT o.id) as order_quantity,
  sum(
    i.quantity * i.unit_price - i.discount_amount
  ) AS revenu
FROM
  orders o,
  order_items i,
  products p,
  standard_products sp,
  channels ss
WHERE
  o.id = i.order_id
AND sp.main_display_in_site = ss.site_id
AND i.product_id = p.id
AND p.standard_product_id = sp.id
AND o.checkout_step = 8
AND o.completed_at >= '2017-3-1'
AND o.completed_at <= '2017-3-31'
GROUP BY
   ss.site_id
) as part_data LIMIT 1 OFFSET 0
=> [#<Order:0x00007f9e62d279b0 id: nil>]
[17] pry(main)>

结果发现，完全符合我们的预想，能分页啦。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 215,539评论 6赞 497
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,911评论 3赞 391
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 161,337评论 0赞 351
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,723评论 1赞 290
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,795评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,762评论 1赞 294
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,742评论 3赞 416
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,508评论 0赞 271
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,954评论 1赞 308
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,247评论 2赞 331
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,404评论 1赞 345
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,104评论 5赞 340
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,736评论 3赞 324
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,352评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,557评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,371评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,292评论 2赞 352

推荐阅读更多精彩内容