以下の内容はhttps://thinkami.hatenablog.com/entry/2023/03/05/214427より取得しました。

RailsのActiveRecordで、joinsメソッドやwhereメソッドで、ハッシュで条件を指定する

Ruby Rails

RailsのActiveRecordにて、SQLのJOIN句やWHERE句の条件を記載する場合、

が用意されています。

それらのメソッドでは、条件を指定する場合に

  • 文字列
  • ハッシュ

が使えます。

　
そんな中、各条件をハッシュで書きたいと思ったときに毎回調べているため、メモとして残しておきます。

　
目次

　

環境

　

データ構造

今回は

  • 複数のテーブルを結合する
  • 複数のテーブルに対して条件を追加する

を試すため、以下のようなデータ構造を用意しました。

　
画像では見わけづらいかもしれないため、文字でも表現しておきます。

[Maker] 1 --- n [Plant] 1 --- n [Employee]
[Maker] 1 --- n [ReservedProduct] 1 --- n [Sale]
                                  n --- 1 [Shop]
                                  1 --- n [SaleByCustomer] n --- 1 [Customer] n --- 1 [Country]
[Maker] n --- 1 [Country]

　
また、Makerモデルからたどれるよう、各モデルには以下のようなRailsの関連がついているものとします。

# MakerからPlant方向
class Maker < ApplicationRecord
  belongs_to :country, optional: true
  has_many :plants
  has_many :reserved_products
end

class Plant < ApplicationRecord
  has_many :employees
end

class Employee < ApplicationRecord
end


# MakerからReservedProduct方向
class ReservedProduct < ApplicationRecord
  has_many :sales
  has_many :sale_by_customers
  belongs_to :maker, optional: true
  belongs_to :shop, optional: true
end

class Sale < ApplicationRecord
  belongs_to :reserved_product
end

class Shop < ApplicationRecord
end

class SaleByCustomer < ApplicationRecord
  belongs_to :reserved_product
  belongs_to :customer
end

class Customer < ApplicationRecord
  belongs_to :country
end

class Country < ApplicationRecord
end


# MakerからCountry方向は、上記のCountryモデルを使うので省略

　

準備

RSpecのテストで実行されたSQLを標準出力へ出す設定を追加

デフォルトの設定の場合、実行されたSQLが出力されません。

今回は joins や where の条件がどのようなSQLになるのかを確認するため、rails_helper.rb に、以下を追加します。
ruby on rails - How do I turn on SQL debug logging for ActiveRecord in RSpec tests? - Stack Overflow

ActiveRecord::Base.logger = Logger.new($stdout) # SQLを出力

　

joinsメソッドにてハッシュによる条件を書く

JOIN句の条件を指定するため、今回は joins メソッドを使います。なお、 eager_load などでも書き方は同じになります。

Railsガイドを参考にしながら、複数のテーブルの結合を試してみます。
Active Record クエリインターフェイス - Railsガイド

　

隣のモデル (Country) を結合

ER図で

[Maker] n --- 1 [Country]

と表記している、MakerとCountryを結合したいとします。

　
この場合は joins に結合先の関連名をシンボルで渡します。

Maker.joins(:country)

　
発行されるSQLはこちら。

SELECT
  "makers".*
FROM
  "makers"
  INNER JOIN "countries" ON "countries"."id" = "makers"."country_id"

　
なお、joins に指定するシンボルは、モデルに

class Maker < ApplicationRecord
  belongs_to :country, optional: true
end

と定義した関連名を設定します。

　
もし関連を定義していない場合、実行時に

ActiveRecord::ConfigurationError: Can't join 'Maker' to association named 'country'; perhaps you misspelled it?

というエラーになります。

　

隣とその隣のモデル (Plant & Employee) を結合

ER図で

[Maker] 1 --- n [Plant] 1 --- n [Employee]

と表記している、MakerとCountryを結合したいとします。

　
この場合は、

  • 隣のモデルをハッシュのキー
  • 隣の隣のモデルを、配列の要素

にします。
13.1.3.1 ネストした関連付けを結合する（単一レベル） | Active Record クエリインターフェイス - Railsガイド

Maker.joins(plants: [:employees])

　
なお、今回は隣の隣は1つなので、 [] がなくても動作します。

Maker.joins(plants: :employees)

　
発行されるSQLはこちら。

SELECT
  "makers".*
FROM
  "makers"
  INNER JOIN "plants" ON "plants"."maker_id" = "makers"."id"
    INNER JOIN "employees" ON "employees"."plant_id" = "plants"."id"

　

さらに先のモデル (ReservedProduct方向) を結合

ER図で

[Maker] 1 --- n [ReservedProduct] 1 --- n [Sale]
                                  n --- 1 [Shop]
                                  1 --- n [SaleByCustomer] n --- 1 [Customer] n --- 1 [Country]

と表記している、ReservedProduct方向で結合したいとします。

　
隣の隣のモデルとの結合と同じように考えれば良いため、

  • reserved_productsをキーに、値の要素として、各結合先を指定
    • sales
    • sale_by_customers
    • shop
  • sale_by_customersは更に結合するので、同じような考え方で定義

とします。

Maker.joins(reserved_products: [
  :sale,
  :shop,
  { sale_by_customers: { customer: :country }},
])

　
発行されるSQLです。

SELECT
  "makers".*
FROM
  "makers"
  INNER JOIN "reserved_products" ON "reserved_products"."maker_id" = "makers"."id"
    INNER JOIN "sales" ON "sales"."reserved_product_id" = "reserved_products"."id"
    INNER JOIN "shops" ON "shops"."id" = "reserved_products"."shop_id"
    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"
        INNER JOIN "countries" ON "countries"."id" = "customers"."country_id"

　

ここまでの3パターンを一度に書く

Makerモデルから3方向に伸びるJOINなため、 joins メソッドにそれぞれの方向のJOIN定義を書きます。

Maker.joins(
  :country,
  plants: :employees,
  reserved_products: [:sales, :shop, { sale_by_customers: { customer: :country }}]
)

　
発行されるSQLです。

SELECT
  "makers".*
FROM
  "makers"
  INNER JOIN "countries" ON "countries"."id" = "makers"."country_id"
  INNER JOIN "plants" ON "plants"."maker_id" = "makers"."id"
    INNER JOIN "employees" ON "employees"."plant_id" = "plants"."id"
  INNER JOIN "reserved_products" ON "reserved_products"."maker_id" = "makers"."id"
    INNER JOIN "sales" ON "sales"."reserved_product_id" = "reserved_products"."id"
    INNER JOIN "shops" ON "shops"."id" = "reserved_products"."shop_id"
    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"
        INNER JOIN "countries" "countries_customers" ON "countries_customers"."id" = "customers"."country_id"

　

whereメソッドでハッシュによる条件を書く

where メソッドでも、ハッシュを使って条件を書くことができます。
13.1.3 複数の関連付けを結合する | 3.3 条件でハッシュを使う | Active Record クエリインターフェイス - Railsガイド

　

条件のうちハッシュで書けるもの

Railsガイドにある通り、 where メソッドにてハッシュで書けるのは以下の通りです。

  • 等号
  • 不等号 ( > など)
  • BETWEEN
  • IN
  • IS NULL
  • 外部キーのID
  • 関連名

　
それぞれ見ていきます。

　

等号

where のキーに属性を、値に取得したいものを指定します。

Maker.where(id: 1)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" = ? [["id", 1]]

　

不等号(大なり小なり)

Railsガイドに

Rubyの終端/始端を持たない範囲オブジェクト（beginless/endless range）がサポートされており、以下のように「〜より大きい」「〜より小さい」条件の構築で利用できます。

とあるため、試してみます。

　

大なり (<) は ...n

...1 のように、最後の値を含まない形式で書きます。

Maker.where(id: ...1)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" < ? [["id", 1]]

　

大なりイコール (<=) は ..n

..1 のように、最後の値を含む形式で書きます。

Maker.where(id: ..1)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" <= ? [["id", 1]]

　
　

小なり (>) はArelを使うしかない

今のところ、Arelを使うしかなさそうです。
activerecord - Rails: Using greater than/less than with a where statement - Stack Overflow

Maker.where(Maker.arel_table[:id].gt(1))

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" > 1

　

小なりイコール (>=) は ..n か ...n

1.. もしくは 1... で書きます。

# .. を使う
Maker.joins.where(id: 1..)

# ... を使う
Maker.joins.where(id: 1...)

　
両方とも同じSQLが発行されます。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" >= ? [["id", 1]]

　

BETWEEN は m..n

両端を含む範囲オブジェクト(..)で書きます。

Maker.where(id: 1..2)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" BETWEEN ?
  AND ? [["id", 1], ["id", 2]]

　
ちなみに、終端を含まない範囲オブジェクト(...)で書いた場合は、BETWEENにはなりません。

例えば

Maker.where(id: 1...2)

の時に発行されるSQLは不等号になっています。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" >= ?
  AND "makers"."id" < ? [["id", 1], ["id", 2]]

　

IN

ハッシュの値に配列を指定することで、INへと変換されます。

Maker.where(id: [1, 2])

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" IN (?, ?) [["id", 1], ["id", 2]]

　

IS NULL

ハッシュの値に nil を指定することで、IS NULLへと変換されます。

Maker.where(country: nil)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."country_id" IS NULL

　

外部キーのIDを指定

外部キーの列名(***_id)を指定

Maker.where(country_id: 2)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."country_id" = ? [["country_id", 2]]

　

関連名を指定

モデルに関連がある場合、関連名を指定すると外部キーの列名へと変換されます。

Maker.where(country: 2)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."country_id" = ? [["country_id", 2]]

　

関連先のテーブルの列を指定

モデルの関連(belongs_to や has_many など)を使って、関連先のテーブルの列を指定します。

今回は、関連名が単数と複数、それぞれの挙動を見ていきます。

　

関連名が単数 (belongs_to)

関連が

class Maker < ApplicationRecord
  belongs_to :country, optional: true
end

と定義してある country の列で絞り込みたいとします。

　
この場合、joins で結合した上で、 where で絞り込みをします。

なお、 where のハッシュのキーには

  • 関連名
  • テーブル名

のいずれも使えるため、それぞれ見ていきます。

　

ハッシュのキーに関連名を使う

ハッシュのキーに関連名、値にもハッシュとして「キー：列名、値：絞り込みたい値」を指定します。

今回の場合は関連名が country になります。

Maker.joins(:country).where(country: { name: '日本' })

　
発行されるSQLです。

JOINするときに、テーブル名に別名として関連名が付与されています。

SELECT
  COUNT(*)
FROM
  "makers"
  INNER JOIN "countries" "country" ON "country"."id" = "makers"."country_id"
WHERE
  "country"."name" = ? [["name", "日本"]]

　

ハッシュのキーにテーブル名を使う

ハッシュのキーにテーブル名を使ってみます。

今回の場合は、テーブル名は countries です。

Maker.joins(:country).where(countries: { name: '日本' })

　
発行されるSQLです。別名は使われていません。

SELECT
  COUNT(*)
FROM
  "makers"
  INNER JOIN "countries" ON "countries"."id" = "makers"."country_id"
WHERE
  "countries"."name" = ? [["name", "日本"]]

　

関連名が複数 (has_many)

続いて関連名が複数の場合です。

class Maker < ApplicationRecord
  has_many :plants
end

また、Plantモデルにも belongs_to が設定してあるものとします。

class Plant < ApplicationRecord
  belongs_to :maker
end

　
この場合、デフォルトでは関連名・テーブル名とも複数形なため、ハッシュのキーは複数形を指定します。

Maker.joins(:plants).where(plants: { name: '北工場' })

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
  INNER JOIN "plants" ON "plants"."maker_id" = "makers"."id"
WHERE
  "plants"."name" = ? [["name", "北工場"]]

　

複数の条件をANDでつなぐ

where に複数のハッシュのキーを渡します。

Maker.where(id: 1, country: nil)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" = ?
  AND "makers"."country_id" IS NULL [["id", 1]]

　

否定形

where.not を使います。
3.4 NOT条件 | Active Record クエリインターフェイス - Railsガイド

ここでは

  • 否定
  • NOT IN
  • NOT NULL

を見ていきます。

　

否定 (!=)

Maker.where.not(id: 1)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" != ? [["id", 1]]

　

NOT IN

Maker.where.not(id: [1, 2])

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" NOT IN (?, ?) [["id", 1],
  ["id", 2]]

　

NOT NULL

Maker.where.not(country: nil)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."country_id" IS NOT NULL

　

複数の否定形

NOT IN かつ IS NOT NULL のような複数の否定形のSQLとしたい場合も、 where.not を使います。

ただ、Rails6.1から、1つ where.not で書くとNANDな否定形の形になっています。

where.notがNORではなくNANDを述部で生成するようになった

Ruby on Rails 6.1 リリースノート - Railsガイド

　
そこで、NANDな書き方とNORな書き方を見ていきます。

　

NANDな書き方: NOT(IN AND IS NULL)

1つの where.not に条件を入れ込みます。

Maker.where.not(id: 2, country: nil)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  NOT (
    "makers"."id" = ?
    AND "makers"."country_id" IS NULL
  ) [["id", 2]]

　

NORな書き方: NOT IN AND IS NOT NULL

別々の where.not にそれぞれの条件を記載します。

Maker.where.not(id: 2).where.not(country: nil)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  "makers"."id" != ?
  AND "makers"."country_id" IS NOT NULL [["id", 2]]

　

ここまで joins と where を組み合わせる

ここまで書いてきた joins と where を組み合わせて、1つのSQLを作ってみます。

Maker.joins(
  :country,
  plants: :employees,
  reserved_products: [{ sale_by_customers: { customer: :country }}, :shop]
).where(
  id: [1, 2, 3],
  plants: { id: 4..6 },
  employees: { id: ...7 },
  reserved_products: { id: 8.. },
  sale_by_customers: { customer_id: 9 },
).where.not(
  customers: { country: [10, 11, 12] },
  countries: { name: nil },
)

　
発行されるSQLです。

SELECT
  COUNT(*)
FROM
  "makers"
  INNER JOIN "countries" ON "countries"."id" = "makers"."country_id"
  INNER JOIN "plants" ON "plants"."maker_id" = "makers"."id"
  INNER JOIN "employees" ON "employees"."plant_id" = "plants"."id"
  INNER JOIN "reserved_products" ON "reserved_products"."maker_id" = "makers"."id"
  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"
  INNER JOIN "countries" "countries_customers" ON "countries_customers"."id" = "customers"."country_id"
  INNER JOIN "shops" ON "shops"."id" = "reserved_products"."shop_id"
WHERE
  "makers"."id" IN (?, ?, ?)
  AND "plants"."id" BETWEEN ?
  AND ?
  AND "employees"."id" < ?
  AND "reserved_products"."id" >= ?
  AND "sale_by_customers"."customer_id" = ?
  AND NOT (
    "customers"."country_id" IN (?, ?, ?)
    AND "countries"."name" IS NULL
  ) 
  [
    ["id", 1],
    ["id", 2],
    ["id", 3],
    ["id", 4],
    ["id", 6],
    ["id", 7],
    ["id", 8],
    ["customer_id", 9],
    ["country_id", 10],
    ["country_id", 11],
    ["country_id", 12]
  ]

　

LIKEはハッシュではなく文字列で書くが、注意点あり

where メソッドのところで見たとおり、SQLのWHERE句でLIKE検索をしたい場合、ハッシュでは指定できません。

そのため、 where メソッドに文字列で条件を指定することになります。

　
ただ、LIKEの場合は適切な形でサニタイズが必要になるので注意が必要です。

　
ここではどのような結果になるかを見ていきます。

　

NG: 引数をサニタイズしない

whereに渡す引数をサニタイズしない場合、 % や _ がエスケープされないため、そのままワイルドカードとして使えてしまいます。

　
引数に % が使われる時のテストを書いてみると、テストがパスしました。

context '%という文字を渡す' do
  before do
    create(:maker, name: '東')
    create(:maker, name: '西')
    create(:maker, name: '南')
    create(:maker, name: '北')
  end

  let(:keyword) { '%' }

  context 'サニタイズしない' do
    it '全件取得できる' do
      actual = Maker.where('name LIKE ?', "%#{keyword}%")

      expect(actual.count).to eq(4)
    end
  end
end

　
発行されるSQLです。

ワイルドカード文字がエスケープされていません。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  (name LIKE '%%%')

　
続いて、引数に _ が使われる場合もテストがパスしています。

context '_という文字を渡す' do
  let(:keyword) { '_' }

  context 'サニタイズしない' do
    it '全件取得できる' do
      actual = Maker.where('name LIKE ?', "%#{keyword}%")

      expect(actual.count).to eq(4)
    end
  end
end

　
発行されるSQLです。

こちらもワイルドカード文字がエスケープされていません。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  (name LIKE '%_%')

　

NG: 引数にsanitize_sql_arrayを使う

生SQLを書くときにSQLインジェクションを防ぐために使うメソッドとして、 sanitize_sql_array があります。

　
sanitize_sql_array を使って、 % に対するテストコードを書いてみます。

しかし、こちらも % がサニタイズされることなく、4件取得できてしまいます。

context 'sanitize_sql_arrayを使う' do
  it '全件取得できる' do
    actual = Maker.where(Maker.sanitize_sql_array(['name LIKE ?', "%#{keyword}%"]))

    expect(actual.count).to eq(4)
  end
end

　
発行されるSQLを見ても、ワイルドカード文字がエスケープされていません。

SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  (name LIKE '%%%')

　

OK: 引数にsanitize_sql_likeを使う

LIKE中のワイルドカード文字をエスケープするには、別のメソッド sanitize_sql_like を使います。

　
同じくテストコードを書いてみると、先ほどとは異なり1件も取得できません。

context 'sanitize_sql_likeを使う' do
  it '1件も取得できない' do
    actual = Maker.where('name LIKE ?', "%#{Maker.sanitize_sql_like(keyword)}%")

    expect(actual.count).to eq(0)
  end
end

　
発行されるSQLを見ると、 % や _ がエスケープされています。

-- `%` の場合
SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  (name LIKE '%\%%')


-- `_` の場合
SELECT
  COUNT(*)
FROM
  "makers"
WHERE
  (name LIKE '%\_%')

　

ソースコード

Githubに上げました。
https://github.com/thinkAmi-sandbox/rails_association-sample

今回のプルリクはこちら
https://github.com/thinkAmi-sandbox/rails_association-sample/pull/5



以上の内容はhttps://thinkami.hatenablog.com/entry/2023/03/05/214427より取得しました。
このページはhttp://font.textar.tv/のウェブフォントを使用してます
不具合報告/要望等はこちらへお願いします。
モバイルやる夫Viewer Ver0.14