以前、RailsのActiveRecordにて、 eager_load を使って

子側が存在しない場合は結果に含めずに抽出する
子側が存在しなくても、結果に含めて抽出する

をしました。
Railsで、eager_loadのLEFT OUTER JOINにて、WHERE句とON句のそれぞれで絞り込みしてみた - メモ的な思考的な

　
そんな中、「ある条件の関連先レコードがない関連元データだけを取得する」方法が気になったため、Railsガイドを見ながら試したことをメモとして残します。
Active Record クエリインターフェイス - Railsガイド

なお、今回は「適切にデータが取得できること」を目的とします。そのため、システムの仕様やRDBMSに関係する部分(用途やパフォーマンス)での良し悪しは考慮しません。

環境
関連先を絞らずに、関連先レコードが存在しない関連元データを取得する
ある条件の関連先レコードがない場合にその関連元データを取得する
ソースコード

環境

Rails 7.0.4.2

関連先を絞らずに、関連先レコードが存在しない関連元データを取得する

本題に入る前段として、まずは「関連先を絞らずに、関連先レコードが存在しない関連元データを取得する」を試してみます。

データについて

テーブル構造

今回は、限定商品を管理するということで、限定商品(reserved_products) : 販売先(sales) = 1 : n となるようなテーブルを用意します。

models/reserved_product.rb

# == Schema Information
#
# Table name: reserved_products
#
#  id         :integer          not null, primary key
#  name       :string
#  created_at :datetime         not null
#  updated_at :datetime         not null
#
class ReservedProduct < ApplicationRecord
  has_many :sales
end

　
models/sales.rb

# == Schema Information
#
# Table name: sales
#
#  id                  :integer          not null, primary key
#  memo                :string
#  created_at          :datetime         not null
#  updated_at          :datetime         not null
#  reserved_product_id :integer          not null
#
# Indexes
#
#  index_sales_on_reserved_product_id  (reserved_product_id)
#
# Foreign Keys
#
#  reserved_product_id  (reserved_product_id => reserved_products.id)
#
class Sale < ApplicationRecord
  belongs_to :reserved_product
end

　
Rails mermaid ERDによるER図はこちら。
https://github.com/koedame/rails-mermaid_erd

各テーブルデータ

各テーブルには以下のようなデータが含まれています。

reserved_products

id	name
1	りんご
2	バナナ
3	ぶどう

　
sales

id	memo	reserved_product_id
1	fooさん - りんご - 1つめ	1
2	fooさん - りんご - 2つめ	1
3	barさん - りんご	1
4	bazさん - ぶどう	3

取得結果の期待値

今回は、関連先データとして sales がない reserved_products のレコード (ここではバナナ) を取得したいとします。

[#<ReservedProduct:0x00007f0f59c91d68 id: 59467727, name: "バナナ", ...]

実装パターン

LEFT OUTER JOIN + IS NULLで取得する

想定しているSQLはこちらです。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
  LEFT OUTER JOIN "sales" ON "sales"."reserved_product_id" = "reserved_products"."id"
WHERE
  "sales"."id" IS NULL

　
これをActiveRecordで発行する場合の実装パターンはいくつかあるため、それぞれ見ていきます。

left_outer_joins + where を使う

このパターンで使う各メソッドは以下の通りです。

left_outer_joins メソッドについて
- https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-left_outer_joins
  - left_joins のエイリアスでもある
where メソッドについて
- https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-where

　
実装です。

ReservedProduct.left_outer_joins(:sales).where(sales: { id: nil })

　
発行されるSQLはこちら。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
  LEFT OUTER JOIN "sales" ON "sales"."reserved_product_id" = "reserved_products"."id"
WHERE
  "sales"."id" IS NULL

Rails6.1から追加された missing を使う

Rails6.1にて WhereChainの missing が追加されましたので、こちらも試してみます。

　
実装はこんな感じで、 left_outer_joins に比べるとシンプルです。

ReservedProduct.where.missing(:sales)

　　
発行されるSQLも left_outer_joins と同じです。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
  LEFT OUTER JOIN "sales" ON "sales"."reserved_product_id" = "reserved_products"."id"
WHERE
  "sales"."id" IS NULL

eager_load を使う

今回は関連先のデータは使用しないのですが、 eager_load も使えます。

eager_load について
- https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-eager_load

　
実装は left_outer_joins と似たような感じです。

ReservedProduct.eager_load(:sales).where(sales: { id: nil })

　
発行されるSQLです。SELECT句が増えていますが、今回欲しいデータは取得できています。

SELECT
  "reserved_products"."id" AS t0_r0,
  "reserved_products"."name" AS t0_r1,
  "reserved_products"."created_at" AS t0_r2,
  "reserved_products"."updated_at" AS t0_r3,
  "sales"."id" AS t1_r0,
  "sales"."memo" AS t1_r1,
  "sales"."reserved_product_id" AS t1_r2,
  "sales"."created_at" AS t1_r3,
  "sales"."updated_at" AS t1_r4
FROM
  "reserved_products"
  LEFT OUTER JOIN "sales" ON "sales"."reserved_product_id" = "reserved_products"."id"
WHERE
  "sales"."id" IS NULL

NOT IN で取得する

想定しているSQLはこちらです。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
WHERE
  "reserved_products"."id" NOT IN (
    SELECT
      "reserved_products"."id"
    FROM
      "reserved_products"
      INNER JOIN "sales" ON "sales"."reserved_product_id" = "reserved_products"."id"
  )

　
ActiveRecordではこうなります。

ReservedProduct.where.not(id: ReservedProduct.joins(:sales).select(:id))

NOT EXISTSで取得する

想定しているSQLはこちらです。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
WHERE
  (
    NOT EXISTS (
      SELECT
        "sales".*
      FROM
        "sales"
      WHERE
        (reserved_products.id = sales.reserved_product_id)
    )
  )

　
ただ、ActiveRecordでは EXISTS述語を発行できるメソッドはなさそうです(もしあったら教えていただけるとありがたいです)。

　
そのため、一部で生SQLを書く感じになります。

サブクエリをto_sqlし、whereに生SQLを渡す

EXISTS述語を含む部分をすべて生SQLで書くこともできます。

ただ、今回は to_sql を使ってEXISTS述語のサブクエリ部分のSQLをActiveRecordに任せることとします。

　
ActiveRecordではこうなります。

sql = Sale.where('reserved_products.id = sales.reserved_product_id').to_sql
ReservedProduct.where("NOT EXISTS (#{sql})")

　
発行されるSQLはこちら。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
WHERE
  (
    NOT EXISTS (
      SELECT
        "sales".*
      FROM
        "sales"
      WHERE
        (reserved_products.id = sales.reserved_product_id)
    )
  )

whereにarrayを渡す

先ほどは to_sql を使いました。

ただ、以下の記事よると to_sql しなくても where だけで実現できそうです。

Because Rails follows standard naming conventions when querying (the downcased plural form of our model), we can add the above condition into our subquery without too much difficulty.

Advanced Active Record: Using Subqueries in Rails

また、ActiveRecordの where の array の説明にも渡せそうな記述があります。
https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-where

　
試してみると、たしかに問題なく動作しました。

ReservedProduct.where('NOT EXISTS (:sales)', sales: Sale.where('reserved_products.id = sales.reserved_product_id'))

　
発行されるSQLも同じです。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
WHERE
  (
    NOT EXISTS (
      SELECT
        "sales".*
      FROM
        "sales"
      WHERE
        (reserved_products.id = sales.reserved_product_id)
    )
  )

　
ちなみに、 Advanced Active Record: Using Subqueries in Rails ではサブクエリで select('1') しています。

ただ、書籍「達人に学ぶSQL徹底指南書第2版」(ミック著、翔泳社) によると、

EXISTS内のサブクエリのSELECT句のリストには、次の3通りの書き方があります。

ワイルドカード： SELECT *

定数： SELECT 'ここは何でもいいんだよ'

列名： SELECT col

しかし、この3つの書式は、いずれも結果に違いがないのです。

達人に学ぶSQL徹底指南書第2版第1部 1-5 EXISTS述語の使い方

とのことなので、今回はサブクエリでは select('1') を書きませんでした。

Arelのexistsを使う

EXISTS述語を生SQLで書きたくない場合は Arel の exists も使えそうです。

　
Arelを使って書くとこんな感じになります。

ReservedProduct.where(Sale.where('reserved_products.id = sales.reserved_product_id').arel.exists.not)

　
発行されるSQLはこちら。カッコが増えているくらいのようです。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
WHERE
  NOT (
    EXISTS (
      SELECT
        "sales".*
      FROM
        "sales"
      WHERE
        (reserved_products.id = sales.reserved_product_id)
    )
  )

　
なお、Arelで書くのは避けたほうが良さそうです。ただ、今回の実装であればそこまで複雑なものにはならなそうです。
Arelでクエリを書くのはやめた方が良い5つの理由 - Qiita

RSpecで動作確認

念のため、上記の各実装の結果が同じになることをRSpecで確認してみると、問題なくパスしました。

require 'rails_helper'

RSpec.describe ReservedProduct, type: :model do
  let!(:apple) { create(:reserved_product, name: 'りんご') }
  let!(:banana) { create(:reserved_product, name: 'ばなな') }
  let!(:grape) { create(:reserved_product, name: 'ぶどう') }

  describe '関連先がない' do
    before do
      create(:sale, reserved_product: apple, memo: 'fooさん - りんご - 1つめ')
      create(:sale, reserved_product: apple, memo: 'fooさん - りんご - 2つめ')

      create(:sale, reserved_product: apple, memo: 'barさん - りんご')

      create(:sale, reserved_product: grape, memo: 'bazさん - ぶどう')
    end

    it 'いずれの結果も同じになること' do
      actual_by_left_join = ReservedProduct.left_outer_joins(:sales).where(sales: { id: nil }).to_a
      actual_by_missing = ReservedProduct.where.missing(:sales).to_a
      actual_eager_load = ReservedProduct.eager_load(:sales).where(sales: { id: nil }).to_a
      actual_not_in = ReservedProduct.where.not(id: ReservedProduct.joins(:sales).select(:id)).to_a
      actual_not_exists = ReservedProduct.where('NOT EXISTS (:sales)',
                                                sales: Sale.where('reserved_products.id = sales.reserved_product_id'))
                                         .to_a
      actual_not_exists_with_arel = ReservedProduct.where(Sale.where('reserved_products.id = sales.reserved_product_id').arel.exists.not).to_a

      expect(actual_by_left_join).to eq(actual_by_missing)
                                       .and eq(actual_eager_load)
                                              .and eq(actual_not_in)
                                                     .and eq(actual_not_exists)
                                                            .and eq(actual_not_exists_with_arel)
    end
  end
end

ある条件の関連先レコードがない場合にその関連元データを取得する

続いて本題です。

データについて

テーブル構造

題材は同じですが、得意先ごとに限定商品を1つだけ買えるような構造とします。

限定商品(reserved_products) : 限定商品の得意先(sale_by_customers) = 1 : n
限定商品の得意先(sale_by_customers): 得意先 (customers) = n : 1

モデル + annotateはこんな感じです。

reserved_products

# == Schema Information
#
# Table name: reserved_products
#
#  id         :integer          not null, primary key
#  name       :string
#  created_at :datetime         not null
#  updated_at :datetime         not null
#
class ReservedProduct < ApplicationRecord
  has_many :sale_by_customers
end

　
sale_by_customers

# == Schema Information
#
# Table name: sale_by_customers
#
#  id                  :integer          not null, primary key
#  memo                :string
#  created_at          :datetime         not null
#  updated_at          :datetime         not null
#  customer_id         :integer          not null
#  reserved_product_id :integer          not null
#
# Indexes
#
#  index_sale_by_customers_on_customer_id          (customer_id)
#  index_sale_by_customers_on_reserved_product_id  (reserved_product_id)
#
# Foreign Keys
#
#  customer_id          (customer_id => customers.id)
#  reserved_product_id  (reserved_product_id => reserved_products.id)
#
class SaleByCustomer < ApplicationRecord
  belongs_to :reserved_product
  belongs_to :customer
end

　
customers

# == Schema Information
#
# Table name: customers
#
#  id         :integer          not null, primary key
#  name       :string
#  created_at :datetime         not null
#  updated_at :datetime         not null
#
class Customer < ApplicationRecord
end

　
Rails mermaid ERDによるER図はこちら。

各テーブルデータ

reserved_products

id	name
1	りんご
2	バナナ
3	ぶどう

　
sale_by_customers

id	memo	reserved_product_id	customer_id
1	fooさんのりんご	1	1
2	fooさんのバナナ	2	1
3	fooさんのぶどう	3	1
4	barさんのりんご	1	2
5	bazさんのバナナ	2	3
6	bazさんのぶどう	3	3

　
customers

id	name
1	foo
2	bar
3	baz

取得結果の期待値　

指定した得意先 (customer) に対して、reserved_productsがsale_by_customersに存在しないレコードをreserved_productsから取得したいとします。

# fooさん
=> []

# barさん
=> [#<ReservedProduct:0x00007f77b1348e58 id: 59467727, name: "バナナ", ...>, 
    #<ReservedProduct:0x00007f77b1330718 id: 938768738, name: "ぶどう", ...>]

# bazさん
=> [#<ReservedProduct:0x00007f77b1322dc0 id: 690933842, name: "りんご", ...>]

実装

今回は「指定した得意先」という動的な絞り込み条件が必要です。

そのため、関連先を絞らない場合で使っていたメソッドが使えないケースも出てきます。

LEFT OUTER JOIN + ON句での絞り込み + IS NULL で取得する

想定しているSQLはこちらです。

JOINする前に customer の絞り込みが必要になることから、ON句にて絞り込みを行っています。

仮にWHERE句で絞り込みを行おうとしても、 customers の各列がNULLになるため、customer.id での絞り込みはできません。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
  LEFT OUTER JOIN sale_by_customers ON (
    reserved_products.id = sale_by_customers.reserved_product_id
    AND sale_by_customers.customer_id = 【指定した得意先】
  )
WHERE
  "sale_by_customers"."id" IS NULL

　　
このパターンについては実装が大きく変更となっています。

モデルのscope + joinsで生SQL + where

以前の記事でも調べましたが、ActiveRecordでON句を指定してJOINするようなメソッドはなさそうでした。

　
そこで、 joins メソッドには結合条件の生SQLを渡すことができるのを利用します。

　
また、長めの生SQLを何度も書くことになるため、scopeを定義し引数として customer.id を受け取れるようにします。 15.1 引数を渡す | Active Record クエリインターフェイス - Railsガイド

　
なお、引数をそのまま埋め込むのではなく、ActiveRecordのサニタイズ系メソッドを使うようにします。 https://api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html

　
今回はON句でのサニタイズなので、 sanitize_sql_array を使います。

　
まとめると、こんな感じのscopeを ReservedProduct モデルに作ります。

class ReservedProduct < ApplicationRecord
  has_many :sale_by_customers

  scope :no_reservations_by, ->(customer_id) {
    sql = ApplicationRecord.sanitize_sql_array(
      ['LEFT OUTER JOIN sale_by_customers
            ON (reserved_products.id = sale_by_customers.reserved_product_id AND
                sale_by_customers.customer_id = :customer_id)',
       { customer_id: customer_id }])

    joins(sql).where(sale_by_customers: { id: nil })
  }
end

　
定義したscopeを使うことで、期待するSQLや結果が取得できました。

# 実行
ReservedProduct.no_reservations_by(1)

# 結果
#   ReservedProduct Load (0.2ms)  SELECT "reserved_products".* FROM "reserved_products" # LEFT OUTER JOIN sale_by_customers
#            ON (reserved_products.id = sale_by_customers.reserved_product_id AND
#                 sale_by_customers.customer_id = 1) WHERE "sale_by_customers"."id" IS NULL
#=> []

NOT IN で取得する

想定しているSQLはこちらです。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
WHERE
  "reserved_products"."id" NOT IN (
    SELECT
      "reserved_products"."id"
    FROM
      "reserved_products"
      INNER JOIN "sale_by_customers" ON "sale_by_customers"."reserved_product_id" = "reserved_products"."id"
      INNER JOIN "customers" ON "customers"."id" = "sale_by_customers"."customer_id"
    WHERE
      "sale_by_customers"."customer_id" = 【指定した得意先】
  )

　
ActiveRecordではこうなります。

ReservedProduct.where.not(id: ReservedProduct.joins(sale_by_customers: :customer).where(sale_by_customers: { customer_id: 1 }).select(:id))

NOT EXISTSで取得する

想定しているSQLはこちらです。

SELECT
  "reserved_products".*
FROM
  "reserved_products"
WHERE
  (
    NOT EXISTS (
      SELECT
        "sale_by_customers".*
      FROM
        "sale_by_customers"
      WHERE
        (
          reserved_products.id = sale_by_customers.reserved_product_id
        )
        AND "sale_by_customers"."customer_id" = 【指定した得意先】
    )
  )

　
Arelを使う/使わないで実装が若干異なるため、それぞれ見ていきます。

whereにarrayを渡す

ActiveRecordではこうなります。

ReservedProduct.where('NOT EXISTS (:sale_by_customers)', sale_by_customers: SaleByCustomer.where('reserved_products.id = sale_by_customers.reserved_product_id').where(customer_id: 2))

Arelのexistsを使う

Arelを使う場合はこうなります。

ReservedProduct.where(SaleByCustomer.where('reserved_products.id = sale_by_customers.reserved_product_id').where(customer_id: 2 ).arel.exists.not)

RSpecで動作確認

先程と同じくRSpecのテストコードがパスすることも確認します。

require 'rails_helper'

RSpec.describe ReservedProduct, type: :model do
  let!(:apple) { create(:reserved_product, name: 'りんご') }
  let!(:banana) { create(:reserved_product, name: 'ばなな') }
  let!(:grape) { create(:reserved_product, name: 'ぶどう') }

  describe '条件付きの関連先がない' do
    let!(:foo) { create(:customer, name: 'foo') }
    let!(:bar) { create(:customer, name: 'bar') }
    let!(:baz) { create(:customer, name: 'baz') }

    before do
      create(:sale_by_customer, reserved_product: apple, customer: foo)
      create(:sale_by_customer, reserved_product: banana, customer: foo)
      create(:sale_by_customer, reserved_product: grape, customer: foo)

      create(:sale_by_customer, reserved_product: apple, customer: bar)
      create(:sale_by_customer, reserved_product: banana, customer: bar)

      create(:sale_by_customer, reserved_product: grape, customer: baz)
    end

    shared_examples 'いずれの結果も同じになること' do
      it do
        actual_by_scope = ReservedProduct.no_reservations_by(customer_id).to_a

        actual_not_in = ReservedProduct.where.not(id: ReservedProduct.joins(sale_by_customers: :customer)
                                                                     .where(sale_by_customers: { customer_id: customer_id })
                                                                     .select(:id))
                                       .to_a

        actual_not_exists = ReservedProduct
                              .where('NOT EXISTS (:sale_by_customers)',
                                     sale_by_customers: SaleByCustomer
                                                          .where('reserved_products.id = sale_by_customers.reserved_product_id')
                                                          .where(customer_id: customer_id))
                              .to_a

        actual_not_exists_with_arel = ReservedProduct
                                        .where(SaleByCustomer.where('reserved_products.id = sale_by_customers.reserved_product_id')
                                                             .where(customer_id: customer_id ).arel.exists.not)
                                        .to_a

        expect(actual_by_scope).to eq(actual_not_in).and eq(actual_not_exists).and eq(actual_not_exists_with_arel)
      end
    end

    context 'fooさんの予約がない' do
      let(:customer_id) { foo.id }

      it_behaves_like 'いずれの結果も同じになること'
    end

    context 'barさんの予約がない' do
      let(:customer_id) { bar.id }

      it_behaves_like 'いずれの結果も同じになること'
    end

    context 'bazさんの予約がない' do
      let(:customer_id) { baz.id }

      it_behaves_like 'いずれの結果も同じになること'
    end
  end
end