結論
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 で書けると良いのですが、そこまではさすがにできないのでこんな書き方をしています。
最後に
ここに書いたコードやクラスは説明のために頭で考えたミニマムコード(例として十分な最小限のコード)です。
実際に動かしている訳ではないので不備がある可能性があります。
記事の内容やコードに間違いがありましたらご連絡ください。
可能な限り補足修正したいと思います。
はー、はじめて技術ブログみたいな記事書いたわ。