Zenn
🤠

任意検索条件に対応するSQLの作り方

2024/07/25に公開
SQL
ETL
tech

顧客や商品を検索する際、名称、日付、分類など、分かる条件だけを指定して該当データを絞り込むことがよくあります。このような機能を開発する場合、以下のような問題が発生しやすいです。

  • SQLが複雑化しやすい
  • メンテナンスが困難になる
  • パフォーマンスが低下しやすい

これらの問題をSQLビルダー（Carbunql）を使って回避する方法を照会します。

前提条件

C#のライブラリ Carbunql を使用します。

例題：顧客検索

ER図

顧客、売上、店舗のテーブルが以下のように関連していると仮定します。

出力要件

  • customersテーブルの内容を表示する
  • ページングを行う
  • 1ページあたりの表示件数は20件

検索要件

以下のいずれかの項目で検索できるものとします。

  • 顧客ID（customer_id）
  • 姓（last_name）
  • 名（first_name）
  • 生年月日（birthday）
  • 住所（city）
  • 売上ID（sale_id）
  • 店舗名（store_name）

前振り:一般的な実装方法

まず、SQLビルダーを使わない場合の実装方法について簡単に説明します。

はじめに出力要件に基づいてSQLを作成します。

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.birthday,
    c.city
FROM
    customers AS c
ORDER BY
    c.first_name,
    c.last_name,
    c.birthday,
    c.city,
    c.customer_id
LIMIT 20
OFFSET :page_index

ユーザーの入力に応じて検索条件を動的に追加する必要があるので以下のように実装していきます。

  • SQLを3つの部分に分ける：WHERE句の前、WHERE句、WHERE句の後
  • 動的にWHERE句を組み立てる
  • これらの部分を結合して最終的なSQLを生成する

以下はC#での実装例です。

var beforeWhere = "SELECT ... FROM customers AS c";
var where = " WHERE 1=1";
var afterWhere = " ORDER BY ...LIMIT 20 OFFSET :page_index";

// WHERE句を動的に組み立てる
if (!string.IsNullOrEmpty(first_name)) where += " AND c.first_name = :first_name";
if (!string.IsNullOrEmpty(last_name)) where += " AND c.last_name = :last_name";
// 追加の条件...

// SQL文の組み立て
var sql = beforeWhere + where + afterWhere;

問題点

検索条件が customers の列である場合

単純にWHERE条件を組み立てるだけなので、ここに問題はありません。

検索条件が customers 以外の列である場合

検索条件が外部テーブル（売上、店舗）を参照する場合、複雑になってきます。

前段同様、WHERE 文に EXISTS 文を AND 条件で追加することは可能なのですが、単純に実装してしまうと以下のようになります。

if (sale_id.HasValue)
{
    where += " AND EXISTS (SELECT * FROM sales AS s WHERE s.sale_id = :sale_id AND s.customer_id = c.customer_id)";
}
if (!string.IsNullOrEmpty(store_name))
{
    where += " AND EXISTS (SELECT * FROM sales AS s INNER JOIN stores AS st ON  s.store_id = st.store_id WHERE st.store_name = :store_name)";
}

この実装では、sale_idとstore_nameの両方が検索条件として指定された場合、salesテーブルを2回スキャンする必要があり、パフォーマンス上の懸念があります。

保守性と性能の両立が難しい

前段のような外部テーブルを参照する検索条件のパフォーマンスを良くしようと思った場合、CTEを使用したり、INNER JOIN を使用する必要が出てきますが、クエリを文字列で表しているので挿入箇所が増えれば増えるほど保守がしづらくなっていきます。

また、検索条件も一律末尾に足せば良いわけでもない、という問題が実装をさらに複雑にさせていきます。

SQLビルダーCarbunqlを使った実装

Carbunqlは選択クエリをモデルとして扱うことができますので、モデル化するところから始めます。

var query = new SelectQuery("SELECT c.customer_id, c.first_name, c.last_name, c.birthday, c.city FROM customers AS c ORDER BY c.first_name, c.last_name, c.birthday, c.city, c.customer_id LIMIT 20 OFFSET :page_index");

検索条件が customers テーブルの列である場合

検索条件"first_name"が指定されている場合、従来はこのように書いていました。

where += " AND c.first_name = :first_name";

Carbunqlでは以下のように記述します。

query.AddWhere("first_name", x => $"{x.Alias}.first_name = :first_name");

上記コードは以下の意味を持ちます。

  • "first_name"列を所持するクエリソース（テーブル、サブクエリ、CTE）を検索する。
  • クエリソースが見つかった場合、第2引数に指定したラムダが実行される。
  • ラムダは引数としてクエリソースが取得できる。
  • 検索条件文字列を返却するとWHERE文として組み込みがされる。

今回の例では、列first_nameを所有するクエリソースxはcustomers AS cで、同Aliasは"c"です。

これらの情報を利用して、c.first_name = :first_nameという文字列を生成し、返却すればWHERE句に検索条件として追加されます。

検索条件が customers 以外の列である場合

前段と同様にAddWhereメソッドで記述したいところですが、列sale_idを所有するクエリソースはSQL上に存在しませんので、エラーが出てしまいます。

// 列sale_idを所有するクエリソースが見つからないので、エラー！
query.AddWhere("sale_id", x => $"{x.Alias}.sale_id = :sale_id");

今回のように元のSQLにクエリソースが見つからない場合は、CTEを使って足してあげましょう。

query.AddCTEQuery("select s.sale_id, s.customer_id from sales s", "target_sales")
    .AddExists(["customer_id"], "target_sales");

AddCTEQueryメソッドを使用したコードは以下の意味を持ちます。

  • SQLselect s.sale_id, ...をtarget_salesという名称でCTEに追加する

AddExistsメソッドを使用したコードは以下の意味を持ちます。

  • "customer_id"列を所持するクエリソースを検索する
  • クエリソースが見つかった場合、第2引数に指定したクエリソースと結合される

これで列sale_idを所有するクエリソースsales sが認識されるようになりました。この時点でのSQLは以下のようになります。

WITH
  target_sales AS (
    SELECT
      s.sale_id,
      s.customer_id
    FROM
      sales AS s
  )
SELECT
...
FROM
  customers AS c
WHERE
  EXISTS (
    SELECT
      *
    FROM
      target_sales AS x
    WHERE
      x.customer_id = c.customer_id
  )
ORDER BY
...

あとは前段と同じようにAddWhereメソッドを使用して検索条件を挿入してください。

// クエリソースを事前に足す
query.AddCTEQuery("select s.sale_id, s.customer_id from sales s", "target_sales")
    .AddExists(["customer_id"], "target_sales");

// 列sale_idを所有するクエリソースが見つかる！
query.AddWhere("sale_id", x => $"{x.Alias}.sale_id = :sale_id");

このときのSQLは以下のようになります。

WITH
  target_sales AS (
    SELECT
      s.sale_id,
      s.customer_id
    FROM
      sales AS s
    WHERE
      s.sale_id = :sale_id --CTEにまで遡って挿入されている！
  )
SELECT
...
FROM
  customers AS c
WHERE
  EXISTS (
    SELECT
      *
    FROM
      target_sales AS x
    WHERE
      x.customer_id = c.customer_id
  )
ORDER BY
...

このように、クエリソースがない場合は「足す」ことができます。

また、検索条件の挿入は何も考えずともAddWhereメソッドを使うだけで適切な位置（最も深度が深いクエリソース）に検索条件を適用するという特徴があります。

これはモデル化しなければ実現が難しい挙動だと思います。

実装結果

今までの内容を踏まえて実装すると、このようになります。

var pageIndex = 0;

//任意検索条件
var first_name = "Ichiro";
var last_name = "Tanaka";
long? customer_id = 1234567890;
long? sale_id = 9999999;
var store_name = "Osaka";
var city = "Tokyo";
DateTime? birthday = new DateTime(1980, 5, 15);

//選択クエリをモデル化
var query = new SelectQuery("SELECT c.customer_id, c.first_name, c.last_name, c.birthday, c.city FROM customers AS c ORDER BY c.first_name, c.last_name, c.birthday, c.city, c.customer_id LIMIT 20 OFFSET :page_index")
    .AddParameter(new QueryParameter(":page_index", pageIndex));

//任意検索条件に合わせ、CTEにクエリソースを追加し、EXISTS文で結合する
if (!string.IsNullOrEmpty(store_name) || sale_id != null)
{
    if (!string.IsNullOrEmpty(store_name))
    {
        query.AddCTEQuery("select s.sale_id, s.customer_id, st.store_name from sales s inner join stores st on s.store_id = st.store_id", "target_sales")
            .AddExists(["customer_id"], "target_sales");
    }
    else
    {
        query.AddCTEQuery("select s.sale_id, s.customer_id from sales s", "target_sales")
            .AddExists(["customer_id"], "target_sales");
    }
}

//任意検索条件をリスト化
var items = new Dictionary<string, object>();
if (sale_id.HasValue) items.Add(nameof(sale_id), sale_id.Value);
if (!string.IsNullOrEmpty(store_name)) items.Add(nameof(store_name), store_name);
if (!string.IsNullOrEmpty(first_name)) items.Add(nameof(first_name), first_name);
if (!string.IsNullOrEmpty(last_name)) items.Add(nameof(last_name), last_name);
if (!string.IsNullOrEmpty(city)) items.Add(nameof(city), city);
if (customer_id.HasValue) items.Add(nameof(customer_id), customer_id.Value);
if (birthday.HasValue) items.Add(nameof(birthday), birthday.Value);

//WHERE条件の挿入
foreach (var item in items)
{
    var column = item.Key;
    var pname = $":{column}";
    query.AddParameter(new QueryParameter(pname, item.Value))
        .AddWhere(column, x => $"{x.Alias}.{column} = {pname}");
}

//選択クエリに書き戻す
string sql = query.ToText();

生成されるSQL文はこちらです。

WITH
    target_sales AS (
        SELECT
            s.sale_id,
            s.customer_id,
            st.store_name
        FROM
            sales AS s
            INNER JOIN stores AS st ON s.store_id = st.store_id
        WHERE
            s.sale_id = :sale_id
            AND st.store_name = :store_name
            AND s.customer_id = :customer_id
    )
SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.birthday,
    c.city
FROM
    customers AS c
WHERE
    EXISTS (
        SELECT
            *
        FROM
            target_sales AS x
        WHERE
            x.customer_id = c.customer_id
    )
    AND c.first_name = :first_name
    AND c.last_name = :last_name
    AND c.city = :city
    AND c.customer_id = :customer_id
    AND c.birthday = :birthday
ORDER BY
    c.first_name,
    c.last_name,
    c.birthday,
    c.city,
    c.customer_id
LIMIT
    20 OFFSET :page_index

補足

Carbunqlは純粋なSQLビルダーであるためマッピング機能やDB接続機能は有りません。このため、以下のライブラリを併用することをお勧めします。

  • Dapper（MicroORM）
  • Carbunql.Dapper（Carbunqlで作成したSQLモデルをDapperに認識させるライブラリ）

まとめ

SQLをモデル化することで、SQLを高度に、かつ簡単に加工することができます。

この手法は、検索条件が動的である場合に非常に効果がありますので、是非お試しください。

Discussion