ActiveRecordのfrom句でサブクエリを書きたい
こんにちは、サーバーサイドエンジニアのでんでんです。
Ruby on Railsと知り合ってもう４年経つのですが、いまだに全くわかりません...
AcitveRecordなんかは典型で、少し難しいことをしようとすると、生SQLに走ろうとしてしまいます。
特にサブクエリを書きたいような時は特に...
が、先日fromメソッドを知り、少しActiveRecordを使える用になったので、紹介します。
fromメソッド
このメソッドです
今CarelyのRailsは5.2なのでそのバージョンのドキュメントを載せています
使い所
例えばですが、
自社システムのCarelyでは、残業時間の管理ができます。
従業員(Customer)の残業時間(Overtime)を毎月取り込むことができるとします。
class Customer(従業員)
has_many :overtimes
end
# year_month(年月)、hour(残業時間)のカラムを持つとします
class Overtime(時間)
belongs_to :customer
endこの状態で、
- 45時間以上残業している従業員が最も多い月の、対象の従業員人数
- 平均月間残業時間の平均
がほしいとします
SQLなら以下のような感じで書けると思います。
SELECT
MAX(summarized_overtimes.count_overtime) AS max_count_overtime
AVG(summarized_overtimes.average_overtime) AS average_average_overtime
FROM (
SELECT
COUNT(CASE WHEN overtimes.hour > 45 THEN true ELSE NULL END) AS count_overtime,
AVG(overtimes.hour) AS average_overtime
FROM
overtimes
GROUP BY
overtimes.year_month
) AS summarized_overtimes上記をActiveRecordに変換します。
FROM句の中は簡単です。
こんな感じで書けます
from_select_query = <<-SQL
COUNT(CASE WHEN overtimes.hour > 45 THEN true ELSE NULL END) AS count_overtime,
AVG(overtimes.hour) AS average_overtime
SQL
from_overtimes = Overtime.group(:year_month).select(from_select_query)ここからさらに、maxとavgするのが少し大変です。
fromメソッドを使わない場合
# maxの値
from_overtimes.map(&:count_overtime).max
# avgの値
average_overtimes = from_overtimes.map(&:average_overtime)
average_overtimes.sum / average_overtimes.sizeovertimesをmapで回して頑張るしか多分ありません。
レコード数が少なければ良いですが、何回もmapをするので計算量も増えますし、
集計元のデータをインスタンス化する必要があるので、メモリにも負荷がかかります。
fromメソッドを使う場合
以下のような感じになります
select_query = <<-SQL
MAX(overtimes.count_overtime) AS max_count_overtime,
AVG(overtimes.average_overtime) AS average_average_overtime
SQL
overtime_data = Overtime.from(from_overtimes, :overtimes).select(select_query).to_a.first
# maxの値
overtime_data.max_count_overtime
# avgの値
overtime_data.average_average_overtimeSQLを文字列としているので、行数は少し長くなりますが、
ロジックをDB側に寄せることができるので、mapなどで回す必要がなくなりました。
またインスタンス化するものも少ないのでメモリ節約にもなります。
最後に
こんな記事を書いておいてあれですが、
このfromメソッド、どうしても使わないといけないとき以外は使わないほうが良いです。
ドナルド・クヌース先生も「早すぎる最適化は諸悪の根源である」と言っています。
ActiveRecordで済むところを、生SQLで書いて読みやすくなることは多分、少ないです。
「可読性を犠牲に、少し処理能力を上げる」程度ならしない方がマシです。
適切なタイミングで使うのが良いメソッドだなと思っています。
