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

はじめに

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

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

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

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

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

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メソッドの実装方法を考えたい。

JOIN + DISTINCTを使う（あまり推奨しない）

この要件を満たすコードとして、以下のような実装をよく見かける。

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

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

参考：発行される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上の処理効率が良くなる。

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

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 + サブクエリを使う

次のように書く方法もある。

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

参考：発行されるSQL

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

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

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

まとめ

子レコード（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メソッドの実装を考える。
テストコードを書くと次のようになる。

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

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

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

参考：発行される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句を使う

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

参考：発行される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 + サブクエリを使う

scope :without_children, -> do
  ids = Parent.joins(:children).select(:id)
  where.not(id: ids)
end

参考：発行されるSQL

SELECT "parents".*
FROM "parents"
WHERE "parents"."id" NOT IN (
  SELECT "parents"."id"
  FROM "parents"
  INNER JOIN "children"
    ON "children"."parent_id" = "parents"."id"
)
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 ）

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