ActiveRecordのfrom句でサブクエリを書きたい | Dev Driven 開発・デザインチーム ActiveRecordのfrom句でサブクエリを書きたい | 働くひとと組織の健康を創る iCARE

BLOG

ActiveRecordのfrom句でサブクエリを書きたい

shogofukuden
2021/07/03
+1

こんにちは、サーバーサイドエンジニアのでんでんです。

Ruby on Railsと知り合ってもう4年経つのですが、いまだに全くわかりません...
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.size

overtimesを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_overtime

SQLを文字列としているので、行数は少し長くなりますが、
ロジックをDB側に寄せることができるので、mapなどで回す必要がなくなりました。
またインスタンス化するものも少ないのでメモリ節約にもなります。

最後に

こんな記事を書いておいてあれですが、
このfromメソッド、どうしても使わないといけないとき以外は使わないほうが良いです。

ドナルド・クヌース先生も「早すぎる最適化は諸悪の根源である」と言っています。
ActiveRecordで済むところを、生SQLで書いて読みやすくなることは多分、少ないです。
「可読性を犠牲に、少し処理能力を上げる」程度ならしない方がマシです。
適切なタイミングで使うのが良いメソッドだなと思っています。

+1