Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
15
Help us understand the problem. What is going on with this article?

More than 5 years have passed since last update.

@Oakbow

ActiveRecord の IN 演算子でサブクエリを扱う

自ブログにも書いた内容ですが、こちらにも再掲します。

要約すると、配列を条件のパラメータとして与えることで IN 演算子を使うことは Rails3 の頃からできていましたが、同じ書式でサブクエリも扱えるよ、というお話です。

IN 演算子は SQLでは以下の二通りの書き方が可能ですが、ActiveRecord においても、下記のクエリがどちらも使えます。

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

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

後者については Arel を使うか Squeel を使うしかないと思い込んでいたので、新鮮な驚きでした。

こんな感じで使えます

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(grate: '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)

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 で書けると良いのですが、そこまではさすがにできないのでこんな書き方をしています。

最後に

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

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

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

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

15
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
15
Help us understand the problem. What is going on with this article?