Rails – 複雑なSQL文でモデルインスタンスを抽出する

やりたいこと

モデルインスタンスの形式を保持したまま、複雑なSQL文でデータを抽出してくる(抽出後、ransackなどのメソッドを適用したいため)。

できないやりかた

find_by_sql

モデル.find_by_sql(SQL文)で直書きできるが、返り値がハッシュとなるためモデルインスタンスのメソッドが使えなくなる。

ActiveRecord::Base.connection.select_all

以下のように、select_all(SQL文)で直書きできるが、find_by_sqlと同様、返り値がハッシュであるため、今回は使えない。


query = <<-SQL 
  SELECT * 
  FROM payment_details
  INNER JOIN projects 
          ON projects.id = payment_details.project_id
  ORDER BY payment_details.created_at DESC
SQL

result = ActiveRecord::Base.connection.select_all(query)

Arel

ArelはActive Recordの内部で使用されるSQL生成ライブラリであるが、Railsの公式ライブラリではないためバージョンアップ時などにエラーの原因となる恐れがあるとのこと。なので、できる限り使わない方が良さそう。

できるやりかた

joinsやselectメソッドの中に生SQLを書き、それらをつなぎ合わせることでモデルインスタンスの形式を保持させる。

実装例

今回はモデルのscopeとして書いていく。SUM(CASE WHEN)とか、複数キーによるINNER JOINなどなど。インデックスをちゃんと貼れば、それなりの速度で動くようになったのでひとまずはこれで良しとする。

Model


class Item < ActiveRecord::Base
      # 省略
      scope :load_for_sales_per_product, lambda { |view_currency, store_ids|
        includes(:unit)
        .select(<<-SQL 
          items.*,
              SUM(items.quantity) AS total_quantity,
              SUM(CASE WHEN rate_histories.main_currency_id = #{view_currency.id} THEN items.price
                  ELSE items.price * rate_histories.rate END) AS total_price"
          SQL
        )
        .joins(:order)
        .joins(<<-SQL
          INNER JOIN exchange_histories
            ON  rate_histories.group_id = orders.rate_history_group_id
            AND rate_histories.main_currency_id = items.price_currency_id
            AND rate_histories.settlement_currency_id = #{view_currency.id}
          SQL
         )
        .where.not(orders: {status: 99})
        .where(orders: {store_id: store_ids})
        .group(:unit_id)
      }
   # 省略
end

備忘メモ

  • joins("SQL文")やselect("SQL文")のように書くことができる。
  • SQL文の中に #{} でRubyのコードが書ける。
  • アソエーションの形がbelongs_toでjoins(:単数形)としていても、whereで書くときは where(複数形: {キー: 値}) となる(テーブル名は複数形なので)。

Sources

兵庫県西宮市生まれのフリーランスRailsエンジニア。海外を拠点にデジタルノマド生活中。/ 前職・資格:公認会計士 / プログラミング言語:Ruby, JavaScript, HTML, CSS / 日本語・英語
コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です