複雑なSQL処理をやってみた

忘れそうなので、一応メモです。

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

///////2015/7/27 以下のsqueelの記述は不要。今回この機能使ってなかったです。
こういう場合に対応方法は幾つかあると思いますが、今回はsqueelというgemを使う方法を採用しました。

インストール

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

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' ) 

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

user.rb

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回で取るようにした方が良い」と判断し上記の方法を選択しました。