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モデルの算出結果を取得しています。