行動すれば次の現実

テック中心の個人ブログ

外部キー(null許可)を複数持っているテーブルで検索する時のSQLクエリのサンプル

レガシーシステムの改修案件で以下のような正規化されていない条件で検索することがありましたので、記事にまとめました。

テーブル設計

  • orders (注文)

    • id
    • supplier_id
    • customer_id
  • suppliers (取引先)

    • id
    • is_active (boolean)
  • customers (顧客)

    • id
    • is_active (boolean)

例えば注文テーブルがあり、「取引先の注文」と「顧客の注文」のいずれかを一つのテーブルで管理しているとします。どちらも設定されることはシステムとしてありえないと仮定します。

この注文テーブルから、取引先がアクティブ(is_active=TRUE)である、または顧客がアクティブ(is_active=TRUE)であるテータを抽出したい場合のクエリを紹介します。

SQLのサンプル

SELECT
    orders.*
FROM
    orders
    LEFT OUTER JOIN
        suppliers
    ON  suppliers.id = orders.supplier_id
    LEFT OUTER JOIN
        customers
    ON  customers.id = orders.customer_id
WHERE
    (suppliers.is_active = TRUE OR suppliers.id IS NULL)
    AND
    (customers.is_active = TRUE OR customers.id IS NULL)

Activerecordで実装する場合

Order.left_outer_joins(:supplier, :customer)
  .merge(where(suppliers: { is_active: true })
    .or(where(suppliers: { id: nil })))
  .merge(where(customers: { is_active: true })
    .or(where(customers: { id: nil })))