はじめに
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メソッドの実装方法を考えたい。
おことわり
SQLのパフォーマンスを正しく議論するためにはSQLの実行計画を確認すべきですが、執筆時間の都合上、僕の経験ベースで話を進めます。
SQLは20年近く書いているので、そこまで的外れな内容ではないはずです（たぶん）。
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 = Child
.where("LOWER(children.name) LIKE ?", "%#{str.downcase}%")
.select(:parent_id)
where(id: ids)
end
参考：発行される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回発行される。
# 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
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を使ったときも同じ議論になるはず）
サンプルコード
本記事のサンプルコードはこちら。
おまけ：子どもが一人もいない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 = Child.select(:parent_id)
where.not(id: ids)
end
参考：発行される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 ）
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