Railsで複雑なSQL処理をする時のTips:squeel編

こんにちは。
MFクラウド給与の開発を担当しております中村です。

なでしこジャパン惜しかったですね。アメリカの序盤の勢いにやられてしまったという感じがしましたが、いい試合だったなぁ、と感動しました。

さて本題に入ります。

Webサービスの開発をしていると複数のテーブルから同時にデータを取得したいケースがあると思います。
railsの場合joinメソッドを使ったりしてできますが、関連するテーブルが3つ以上、更に関連先のテーブルのあるカラムの合計値をという場合、ActiveRecordだけと1回で取得するのが厳しい場合があります。

こういう場合に対応方法は幾つかあると思いますが、今回は『 squeel 』というgemを使う方法を採用しました。
 

インストール

こちら普通にgem install squeelをするか、Gemfileに

gem 'squeel'

と書いてbundle installをするだけOKです。
 

どうやって使ったか

簡単な使い方はgithubのページに書かれているのでここでは割愛し、ここでは今回このgemをどのように使ったのか記述例を示したいと思います。

4つのテーブルを参照して条件に合致したユーザのID一覧を返す

テーブル名など実際とは違いますが、以下のようなSQLに相当する処理を書きたいとします。

SELECT `users`.`user_id` FROM `users` LEFT OUTER JOIN `user_balances` ON `user_balances`.`period_id` = `users`.`period_id` AND `user_balances`.`employee_id` = `users`.`user_id` LEFT OUTER JOIN `user_insurances` ON `user_insurances`.`period_id` = `users`.`period_id` AND `user_insurances`.`employee_id` = `users`.`user_id` WHERE `users`.`hoge_type` != 'A' AND (user_balances.amount > 0) AND (user_insurances.user_type='B')

その場合次のように書きます。

class User

  def hogehoge_user_ids
    user_table = User.arel_table
    user_balance_table = UserBalance.arel_table
    user_insurance_table = UserInsurance.arel_table

    join_conditions = user_table
      .join(user_balance_table, Arel::Nodes::OuterJoin)
      .on(
        Arel::Nodes::And.new([user_balance_table[:period_id].eq(user_table[:period_id]), user_balance_table[:user_id].eq(user_table[:user_id])
          ])
      )
      .join(user_insurance_table, Arel::Nodes::OuterJoin)
      .on(
        Arel::Nodes::And.new([
          user_insurance_table[:period_id].eq(user_table[:period_id]),
 user_insurance_table[:user_id].eq(user_table[:user_id])
        ])
      )
      .join_sources

    joins(join_conditions)
      .where.not(hoge_type: 'A')
      .where('user_balances.amount > 0')
      .where('user_insurances.user_type=?', 'B').pluck(:user_id)
  end
end

各テーブルのjoinする条件をonメソッドを使って定義して行きます。最終的に条件に合致したUsersテーブルのデータからuser_idカラムの値だけ抽出するのでpluckを使っています。
 

4つのテーブルを参照して条件に合致したユーザが持っているあるカラム(例としてamountとします)の合計値を返す

今回は合計値なので、上と若干違います。

SELECT SUM(`user_balances`.`amount`) AS amount_sum FROM `users` LEFT OUTER JOIN `user_balances` ON `user_balances`.`period_id` = `users`.`period_id` AND `user_balances`.`user_id` = `users`.`user_id` LEFT OUTER JOIN `user_insurances` ON `user_insurances`.`period_id` = `users`.`period_id` AND `user_insurances`.`user_id` = `users`.`user_id` WHERE `users`.`hoge_type` != 'A' AND (user_balances.amount > 0) AND (user_insurances.user_type='B')

合計するデータはuser_balancesテーブルのamountカラムの値になります。
これを実現するためにはrubyコードでは以下のように書きます。

class User

  def hogehoge_user_ids
    user_table = User.arel_table
    user_balance_table = UserBalance.arel_table
    user_insurance_table = UserInsurance.arel_table

    join_conditions = user_table
      .join(user_balance_table, Arel::Nodes::OuterJoin)
      .on(
        Arel::Nodes::And.new([user_balance_table[:period_id].eq(user_table[:period_id]), user_balance_table[:user_id].eq(user_table[:user_id])
          ])
      )
      .join(user_insurance_table, Arel::Nodes::OuterJoin)
      .on(
        Arel::Nodes::And.new([
          user_insurance_table[:period_id].eq(user_table[:period_id]),
 user_insurance_table[:user_id].eq(user_table[:user_id])
        ])
      )
      .join_sources

    joins(join_conditions)
      .select (UserBalance.arel_table[:amount].sum.as('amount_sum'))
      .first
      .amount_sum
  end

大体一緒ですが、最後の合計値を取得するところでselectメソッドでカラムを指定し、sumで合計しamount_sumという名前で取得したUserモデルの算出結果を取得しています。
 

まとめ

以上のようにActiveRecordを使っていても複数テーブルが絡む複雑なSQL処理の実装は可能です。

とは言え、こういうのはあくまでも選択肢の一つで可能なら処理を分割したりした方が全体の流れが分かりやすくて良いと思います。

今回もできればそうしたかったのですが、「SQL発行回数がどうしても多くなってパフォーマンスがそこまで出せない、1回で取るようにした方が良い」と判断し上記の方法を選択しました。

このブログを読まれた方の参考に少しでもなれば幸いです。
 

最後に

マネーフォワードでは、Railsエンジニアを募集しています。
お互いに改善案を出し合いながら、サービスと共に一緒に成長出来る仲間をお待ちしています!

【採用サイト】
『マネーフォワード採用サイト』 https://recruit.moneyforward.com/
『Wantedly』 https://www.wantedly.com/companies/moneyforward

【公開カレンダー】
マネーフォワード公開カレンダー

【プロダクト一覧】
家計簿アプリ・クラウド家計簿ソフト『マネーフォワード』 https://moneyforward.com/
家計簿アプリ・クラウド家計簿ソフト『マネーフォワード』 iPhone,iPad
家計簿アプリ・クラウド家計簿ソフト『マネーフォワード』 Android
クラウド型会計ソフト『MFクラウド会計』 https://biz.moneyforward.com/
クラウド型請求書管理ソフト『MFクラウド請求書』 https://invoice.moneyforward.com/
クラウド型給与計算ソフト『MFクラウド給与』 https://payroll.moneyforward.com/

Pocket