子レコードの条件で親レコードを絞り込みたいときはEXISTS句を活用しよう

## はじめに
Railsで次のような親子関連を持ったモデルがあったとする。

```ruby
class Parent < ApplicationRecord
  has_many :children
end

class Child < ApplicationRecord
  belongs_to :parent
end
```

そして、DBのデータが次のように登録されていたとする。

- parent = Namihei
  - child = Sazae
  - child = Katsuo
  - child = Wakame
- parent = Misae
  - child = Shinnosuke
  - child = Himawari

この状況で以下のようなメソッドを作りたい。

```ruby 
# 引数で与えられた文字列を子どもの名前に含む親（Parent）を返す
# （子どもの名前の大文字・小文字は無視する）
Parent.children_name_with(str)
```

この要件を満たすためのテストコードは次のようになる。

```ruby
require "test_helper"

class ParentTest < ActiveSupport::TestCase
  test "zで検索" do
    # Sazaeが該当するのでNamiheiが返る
    parents = Parent.children_name_with('z').order(:name)
    assert_equal [parents(:namihei)], parents
  end

  test "kで検索" do
    # ShinnosukeとKatsuoとWakameが該当するのでMisaeとNamiheiが返る
    parents = Parent.children_name_with('k').order(:name)
    assert_equal [parents(:misae), parents(:namihei)], parents
  end

  test "rで検索" do
    # Himawariが該当するのでMisaeが返る
    parents = Parent.children_name_with('r').order(:name)
    assert_equal [parents(:misae)], parents
  end
end
```

この要件を満たす`children_name_with`メソッドの実装方法を考えたい。

### おことわり
SQLのパフォーマンスを正しく議論するためにはSQLの実行計画を確認すべきですが、執筆時間の都合上、僕の経験ベースで話を進めます。
SQLは20年近く書いているので、そこまで的外れな内容ではないはずです（たぶん）。

## JOIN + DISTINCTを使う（あまり推奨しない）
この要件を満たすコードとして、以下のような実装をよく見かける。

（注：`"%#{str.downcase}%"`よりも`"%#{sanitize_sql_like(str.downcase)}%"`とした方がより望ましいが、記述を短くするためここではあえて前者を使う）

```ruby
scope :children_name_with, -> (str) do
  joins(:children)
    .where("LOWER(children.name) LIKE ?", "%#{str.downcase}%")
    .distinct
end
```

参考：発行されるSQL

```sql 
SELECT DISTINCT "parents".* 
FROM "parents" 
INNER JOIN "children" 
  ON "children"."parent_id" = "parents"."id" 
WHERE (LOWER(children.name) LIKE '%k%') 
ORDER BY "parents"."name" ASC
```

たしかにこの実装でも要件は満たせる。
しかし、childrenテーブルをJOINすると大量のparentsレコードの重複行が発生してしまう恐れがある。
そのために`distinct`を呼んで重複行を排除する必要があるが、一般にこの処理はRDBMSにとってハイコストなものでであるため、パフォーマンスが悪化する恐れがある。

### 参考：DISTINCTを付けなかった場合

DISTINCTなしで子どもの名前に"k"が含まれる親を検索するSQLを実行すると以下のような結果になる。

| id | name | created_at | updated_at |
|:---|:---|:---|:---|
| 2 | Misae | 2021-03-03 22:33:05 | 2021-03-03 22:33:05 |
| 1 | Namihei | 2021-03-03 22:33:05 | 2021-03-03 22:33:05 |
| 1 | Namihei | 2021-03-03 22:33:05 | 2021-03-03 22:33:05 |

Namiheiのレコードが重複する理由は、JOIN先のchildrenテーブルでKatsuoとWakameの2件が該当したためである。

## EXISTS句を使う（個人的におすすめ）

上のようなコードは以下のようにEXISTS句を使ったクエリが発行されるように書き直すと、RDBMS上の処理効率が良くなる。

```ruby
scope :children_name_with, -> (str) do
  sql = <<~SQL
    EXISTS (
      SELECT *
      FROM children c
      WHERE c.parent_id = parents.id
      AND LOWER(c.name) LIKE ?
    )
  SQL
  where(sql, "%#{str.downcase}%")
end
```

参考：発行されるSQL

```sql
SELECT "parents".*
FROM "parents"
WHERE (EXISTS (
  SELECT *
  FROM children c
  WHERE c.parent_id = parents.id
  AND LOWER(c.name) LIKE '%k%'
))
ORDER BY "parents"."name" ASC
```

このSQLにするとparentsレコードの重複行が発生しなくなり、DISTINCTの処理も不要になる。

この記事で示した程度の少量のレコード数であれば体感できる速度差はないが、何万、何十万という重複行が発生するような状況では無視できない違いが出てくる。

## もしくはIN + サブクエリを使う
次のように書く方法もある。

```ruby
scope :children_name_with, -> (str) do
  ids = Child
    .where("LOWER(children.name) LIKE ?", "%#{str.downcase}%")
    .select(:parent_id)
  where(id: ids)
end
```

参考：発行されるSQL

```sql
SELECT "parents".*
FROM "parents"
WHERE "parents"."id" IN (
  SELECT "children"."parent_id"
  FROM "children"
  WHERE (LOWER(children.name) LIKE '%k%')
)
ORDER BY "parents"."name" ASC
```

このSQLでも重複行は発生しないのでDISTINCTは不要。
EXISTS句を使ったときとどちらが実行効率が良いのかは未検証。

ただ、個人的にはEXISTS句を使った方が「子レコードの存在有無（EXISTS OR NOT）で絞り込みたい」という意図が明確になるので、EXISTS句を使う方が好み。

### なぜpluckではなくselectなのか？

上のコードを`select`ではなく`pluck`に置き換えると、サブクエリではなく`parent_id`を取得するクエリとParentを取得するクエリが2回発行される。

```diff
 # pluckを使う場合
 scope :children_name_with, -> (str) do
   ids = Child
     .where("LOWER(children.name) LIKE ?", "%#{str.downcase}%")
-    .select(:parent_id)
+    .pluck(:parent_id)
   where(id: ids)
 end
```

```sql
SELECT "children"."parent_id"
FROM "children"
WHERE (LOWER(children.name) LIKE '%k%')

SELECT "parents".*
FROM "parents"
WHERE "parents"."id" IN (1, 1, 2)
ORDER BY "parents"."name" ASC
```

`pluck`でも得られる結果は同じだが、クエリが1回で済む`select`の方が実行効率が良い。

## まとめ
子レコード（has_manyで関連する関連先のレコード）の条件で親レコードを絞り込みたいときは、JOIN + DISTINCTよりもEXISTSを使って絞り込む。

### 実行環境
- Ruby on Rails 6.1.3
- SQLite3 （PostgreSQLやMySQLを使ったときも同じ議論になるはず）

### サンプルコード
本記事のサンプルコードはこちら。

- https://github.com/JunichiIto/rails-exists-sandbox

## おまけ：子どもが一人もいないParentを検索する

次のように子どもが一人もいないParentレコードがあったとする。

- parent = Namihei
  - child = Sazae
  - child = Katsuo
  - child = Wakame
- parent = Misae
  - child = Shinnosuke
  - child = Himawari
- parent = Golgo13
  - (no children)

子どもが一人もいないParentを探す`without_children`メソッドの実装を考える。
テストコードを書くと次のようになる。

```ruby
test ".without_children" do
  parents = Parent.without_children.order(:name)
  assert_equal [parents(:golgo13)], parents
end
```

### 方法1：LEFT OUTER JOIN + id IS NULLを使う

```ruby
scope :without_children, -> do
  left_outer_joins(:children).where(children: { id: nil })
end
```

参考：発行されるSQL

```sql
SELECT "parents".*
FROM "parents"
LEFT OUTER JOIN "children"
  ON "children"."parent_id" = "parents"."id"
WHERE "children"."id" IS NULL
ORDER BY "parents"."name" ASC
```

### 方法2：NOT EXISTS句を使う

```ruby
scope :without_children, -> do
  sql = <<~SQL
    NOT EXISTS (
      SELECT *
      FROM children c
      WHERE c.parent_id = parents.id
    )
  SQL
  where(sql)
end
```

参考：発行されるSQL

```sql
SELECT "parents".*
FROM "parents"
WHERE (NOT EXISTS (
  SELECT *
  FROM children c
  WHERE c.parent_id = parents.id
))
ORDER BY "parents"."name" ASC
```

### 方法3：NOT IN + サブクエリを使う

```ruby
scope :without_children, -> do
  ids = Child.select(:parent_id)
  where.not(id: ids)
end
```

参考：発行されるSQL

```sql
SELECT "parents".*
FROM "parents"
WHERE "parents"."id" NOT IN (
  SELECT "children"."parent_id"
  FROM "children"
)
ORDER BY "parents"."name" ASC
```

### どれがいいか？

- 方法1
  - メリット：SQLを書かずにActiveRecordの機能だけで済む
  - デメリット：RDBMSによっては内部的に大量に子レコードをJOINしてからidがNULLの行を絞り込む、というような処理が走りそう（未検証）

- 方法2
  - メリット：書き手の意図が明確になる。RDBMSのクエリオプティマイザが効きやすそう（未検証）
  - デメリット：生SQLを書かなければいけない。状況によってはscopeの再利用性が下がる

- 方法3
  - メリット：SQLを書かずにActiveRecordの機能だけで済む
  - デメリット：NOT INだとテーブルの全件走査が走るかも？（未検証。クエリオプティマイザが賢ければうまくindexが使われるかも？）

クエリオプティマイザの性能次第のところはあるが、個人的にはクエリの意図が明確で実行速度も速そうな方法2を使いたい。


## 応用：EXISTSを使いつつ、なるべく生SQLの量を減らす

EXISTSやNOT EXISTSを使うクエリは以下のように書くこともできる。（Thanks to @shunichi ）

```ruby
scope :children_name_with, -> (str) do
  where(
    'EXISTS (:children)',
    children: Child.where(
      "children.parent_id = parents.id AND LOWER(children.name) LIKE ?",
      "%#{str.downcase}%"
    )
  )
end

scope :without_children, -> do
  where(
    'NOT EXISTS (:children)',
    children: Child.where("children.parent_id = parents.id")
  )
end
```

参考 https://pganalyze.com/blog/active-record-subqueries-rails#where-not-exists