環境
- Ruby2.5.1
- Rails 5.2.2
- mysql 5.6.34
作りたいSQL
※若干もともと書いたコードから諸々変更しているため、そのまま実装すると問題があるかもしれません。
以下のSQLを書いたとしましょう
SELECT date,
union_reports.product_id,
SUM(stockings_num) AS stockings_num,
SUM(sales_num) AS sales_num,
FROM
(SELECT Date(stocked_at) AS date,
product_id,
SUM(num) AS stockings_num,
0 AS sales_num,
FROM `stockings`
WHERE stocked_at BETWEEN '20180101000000' AND '20181231235959'
AND product_id = 1
GROUP BY date(delivered_at),
UNION SELECT Date(saled_at) AS date,
product_id,
0 AS stockings_num,
SUM(num) AS sales_num,
FROM `sales`
WHERE saled_at BETWEEN '20180101000000' AND '20181231235959'
AND product_id = 1
GROUP BY date(saled_at)) union_reports
GROUP BY date
前提としてProductsテーブルに商品に関する情報が何かしらあり、StockingsテーブルとSalesテーブルにそれぞれ商品と売れた（仕入れた）数、そのイベントの起きた日付を記録しています。
SQLはproductsモデルにおいてidが１の2018年内の仕入れ数と売上個数を見たいというわけですね。
きちんとSQLを見ると、まず両モデルで2018年内+product_idが１のレコードを抽出、sumとgroup_byでまとめてunionしたものをunion_reportsとして定義、さらにそれをまとめている形です。
ActiveRecord化
分解してみていきましょう。
まず以下のSQL。
SELECT Date(stocked_at) AS date,
product_id,
SUM(num) AS stockings_num,
0 AS sales_num,
FROM `stockings`
WHERE stocked_at BETWEEN '20180101000000' AND '20181231235959'
AND product_id = 1
GROUP BY date(delivered_at)
こちらはActiveRecordだと以下で書けると思います。（stocked_atの部分は元のSQLだとjoinsの条件でSQLベタがきだったのをwhereに落としているので不都合があるかもしれません。）
def make_stock_statement
Stocking.select("Date(stocked_at) AS date", "product_id",
"SUM(num) AS stockings_num","0 AS sales_num").group("product_id")
.where(stocked_at: '20180101000000'..'20181231235959').where(product_id: 1)
end
同様に以下のSQLもActiveRecordにしましょう。
UNION SELECT Date(saled_at) AS date,
product_id,
0 AS stockings_num,
SUM(num) AS sales_num,
FROM `sales`
WHERE saled_at BETWEEN '20180101000000' AND '20181231235959'
AND product_id = 1
GROUP BY date(saled_at)
以下のようになると思います。
def make_sale_statement
Sale.select("Date(saled_at) AS date", "product_id",
"SUM(num) AS sales_num","0 AS stock_num").group("product_id")
.where(saled_at: '20180101000000'..'20181231235959').where(product_id: 1)
end
二つのサブクエリをUnionします。(詳細はこの記事参照1)
def make_union_reports
Arel::Nodes::Union.new(
make_delivery_statement.ast,
make_request_statement.ast
).to_sql
end
最後にさらにsumしますが、union_reportsを参照したいからといって
def make_statement
UnionReport.select("date",
"union_reports.product_id",
"SUM(stockings_num) AS stockings_num",
"SUM(sales_num) AS sales_num",)
.from("#{make_union_reports} union_reports").group("date")
end
としてはいけません。union_reportsはテーブルとしては存在しないのでエラーを出します。
じゃあどうすればいいか。
def make_statement
Sale.select("date",
"union_reports.product_id",
"SUM(stockings_num) AS stockings_num",
"SUM(sales_num) AS sales_num",)
.from("#{make_union_reports} union_reports").group("date")
end
単にActiveRecordを使いたいだけなので適当なモデルを最初に持って来ればいいんですね。
ActiveRecord.select(以下略)でもいけるかもしれません。（未検証）
追記
途中で一部Arelを使っているんですが、そろそろ来そうなRails6.0.0では途中で一瞬出てくる
make_delivery_statement.ast,
make_request_statement.ast
の部分は
make_delivery_statement.arel.ast,
make_request_statement.arel.ast
とastの前にarelを追加する必要があるみたいです。