USO800

Web開発者oakbowのブログです

Rails4のActiveRecordは、IN演算子のサブクエリを使える

f:id:oakbow:20150324011856p:plain

結論

SQLでは WHERE 句で IN 演算子というものを使えます。 この IN 演算子の中は、カンマ区切りの配列のような形で要素を列挙する形でも、別の SQL の出力結果(サブクエリ)を利用することもできます。

SELECT * FROM users WHERE id IN (1,5,7,10,50...)

SELECT * FROM users WHERE id IN (SELECT user_id FROM presences WHERE ...) 

こんな感じの二つの書式を使うことが出来ます。

Rails3 ではこのうち前者のタイプは普通に使えたんですが、後者は Squeel などの外部の力を借りないとできなかったと思います。

Rails4 ではどちらもできるようになっているよ、というお話です。

こんな感じで使えます

Student(学生)モデルとReport(レポート)モデル、Credit(単位)モデルがあるとします。

Student:Report は1対多、Student:Credit は1対1の関係にあります(話を簡単にするため、教科の概念をなくしています)。

ここで、最終レポートを提出した学生全員に単位を与える処理を考えてみましょう。

アプリケーションサイドから素直に考えた場合、こんなコードになると思います。

 student_ids = Student.joins(:reports).where('reports.type= ?', :final).pluck(:id)

 Credit.where('student_id IN ? ', student_ids).update_all(grade: 'good')

SQL はこんな感じになります。

SELECT students.id 
FROM students 
INNER JOIN reports ON students.id = reports.student_id 
WHERE reports.type = 'final'

UPDATE credits SET grade = 'good' WHERE student_id IN (1, 5, 12, 33, 156, ...)

でもこれ、こう書けるんですね。

 student_ids = Student.joins(:reports).where('reports.type= ?', :final).select(:id)

 Credit.where(student_id: student_ids).update_all(grade: 'good')

SQL はこうなります。↑と違って一回だけ発行されます。

UPDATE credits 
SET grade = 'good' 
WHERE student_id IN (
  SELECT students.id 
  FROM students 
  INNER JOIN reports ON students.id = reports.student_id 
  WHERE reports.type = 'final')

そう。 同じ書式のままで、配列でも ActiveRecord::Relation でも受け取ってくれるんです。SQL の IN 演算子と似ていて、非常に直観的ですね。これはすごい。 Rails3 で Squeel が入った状態から始めた私はいまいち素の AR にできることできないことをちゃんと把握できていないんですが、これ、Arel 使わないとできないと思ってました。。

サブクエリのすすめ

前者は SQL を二回発行して、まずレポートを提出した学生の ID 一覧を取得し、その後学生の ID 一覧に合致する単位データを一括で「良」に更新しています。 後者は SQL の発行回数は一回。レポート提出者の学生の ID の集合を取得し、その集合に合致する単位データを更新しています。

データ量が少なければ微々たる差しかありませんが、どの RDBMS でもほぼ確実に後者の方が速いです。

SQL というのは集合を扱うことに関しては非常に優れていて、集合の状態のまま条件としてうまく渡して一度で処理できると、特にデータ量が増えてきたときにものすごい差になって来るんです。 なので可能な限り後者の形で発行してあげる必要があります。 集合の扱いに関しては通常の言語が及ぶことはまずないので、配列やハッシュでいったん受け取って加工して…などということは基本的に考えない方がいいです。

サブクエリは遅いこともある。。。という話をSQL初級講座的なものを読んで避けようとする方もいると思いますが、それはサブクエリじゃない通常のクエリとの対比の話であって、ネイティブコードで実行される、集合処理に特化した命令(SQL)の代わりに、Ruby で配列処理やっても余計に遅くなるだけなので、素直にサブクエリ使いましょう。

もちろん例外はありますが、基本的には SQL の発行回数が少なければ少ないほど速いです。

実際に遭遇したコードに近いもの

今回の話はコードレビューで知りました。

本物のコードは上の例とは違って、「最終レポートを提出していない学生をすべて不可にする」という感じの処理をやっています。

同じではありませんが、こんなコードになっています。

Report モデルは、レポートの提出がないとレコードが生成されません。

 student_query =
      Student
      .joins(
        sanitize_sql_array([<<-SQL, :final])
          LEFT OUTER JOIN reports
            ON students.id = reports.student_id
              AND reports.type = ?
        SQL
      )
      .where("reports.student_id IS NULL").select(:id)

  Credit
  .where(student_id: student_query)
  .update_all(grade: 'not_good')

こんな感じ。 sanitize_sql_arrayプレースホルダのために利用しています。

結合条件を AR4 で書けると良いのですが、そこまではさすがにできないのでこんな書き方をしています。

最後に

ここに書いたコードやクラスは説明のために頭で考えたミニマムコード(例として十分な最小限のコード)です。

実際に動かしている訳ではないので不備がある可能性があります。

記事の内容やコードに間違いがありましたらご連絡ください。

可能な限り補足修正したいと思います。

はー、はじめて技術ブログみたいな記事書いたわ。