haikara/sequel-builder

There is no license information available for the latest version (0.1.0) of this package.

Maintainers

Details

github.com/HaikaraSakura/sequel-builder

Source

Issues

Installs: 3

Dependents: 0

Suggesters: 0

Security: 0

Stars: 0

Watchers: 1

Forks: 0

Open Issues: 0

0.1.0 2024-08-17 12:24 UTC

Requires

Unknown License d7b78e920ece49ae42cb5e4940fb136b48e0f397

This package is auto-updated.

Last update: 2024-10-17 12:47:28 UTC

README

SQL文の組み立てをおこなうライブラリ。
プレースホルダーを含むSQL文を組み立て、埋め込む値を保持する。
SQLの文法に近い書き心地を目標とする。

利用方法

以下のサンプルではPHP8.0以上を前提とする。

ビルダーのインスタンス化と、SQL文の取得

基本クラスとして、Select、Insert、Update、Deleteが存在する。
いずれもBuilderクラスを継承しており、以下のように用いる。

// コンストラクタの引数にテーブル名を渡す。
$builder = SQL::select('items');

 // SQL文の組み立てを実行
$builder->build();

// SELECT items.* FROM items という文字列を取得できる。
$builder->getQuery();

カラムの指定

SELECT文で取得するカラムを指定したい場合、columnsメソッドに可変長引数で指定する。

// メソッドチェーンで記述する場合、PHPの構文上、インスタンス化部分を()で囲む必要がる。
$builder = SQL::select('items')
    ->columns('id', 'item_code', 'item_name', 'category_id');

/*
    SELECT
        id, item_code, item_name, category_id
    FROM items
*/

WHERE

WHERE句による絞り込みは、whereメソッドにcallableな値を渡す形で記述する。
callable値の実行、引数にRulesクラスのインスタンスが渡されるので、必要なメソッドを呼び出したうえでreturnする。

Rulesクラスに備わるメソッドは様々なので、以下では簡易な例を紹介する。

// item_codeが00001で、deleted_flagがNULLではないレコードを抽出
$builder = SQL::select('items')
    ->where(fn (Rules $rules) => $rules
        ->equals('item_code', '00001')
        ->isNotNull('deleted_flag'));

/*
    SELECT items.*
    FROM items
    WHERE
        item_code = ?
        AND deleted_flag IS NOT NULL
*/

テーブルの結合

LEFT JOINなど、テーブル結合の記述。
ON句での結合条件の記述は、WHEREと同じくcallableとRulesクラスを用いる。

/* equalsの第二引数に、テーブル名.カラム名の文字列をそのまま渡すと、エスケープされてしまい、
テーブル名.カラム名として扱われなくなってしまうので、Rawクラスを渡す必要がある。 */
$builder = SQL::select('items')
    ->columns('items.id', 'items.item_code', 'items.item_name', 'categories.category_name')
    ->leftJoin('categories', fn (Rules $rules) => $rules
        ->equals('categories.id', new Raw('items.category_id')))

/*
    SELECT
        items.id, items.item_code, items.item_name, categories.category_name
    FROM items
    LEFT JOIN categories
        ON categories.id = items.category_id
*/

GROUP BYとHAVING

HAVINGの使い方はWHEREとまったく同じになる。

// 取引先ごとの月間売上テーブルを検索。年間の売上が1000000以上の取引先、という条件で絞り込む。
// customer_code = 取引先コード、amount = 売上金額とする。
$builder = SQL::select('monthly_sales')
    ->columns('customer_code', 'SUM(amount) AS yearly_sales')
    ->groupBy('customer_code')
    ->having(fn (Rules $rules) => $rules
        ->compare('yearly_sales', '>=', 1000000));

/*
    SELECT
        customer_code, SUM(amount) AS yearly_sales
    FROM monthly_sales
    GROUP BY customer_code
    HAVING
        yearly_sales >= 1000000
*/

ORDER BY

// 月間売上テーブルを集計し、年間売上の多い順に取得
$builder = SQL::select('monthly_sales')
    ->columns('customer_code', 'SUM(amount) AS yearly_sales')
    ->groupBy('customer_code')
    ->orderByDesc('yearly_sales');

/*
    SELECT
        customer_code, SUM(amount) AS yearly_sales
    FROM monthly_sales
    GROUP BY customer_code
    ORDER BY yearly_sales DESC
    */

LIMITとOFFSET

limitメソッドとoffsetメソッドがあり、別々に指定できるが、
OFFSET値は、limitの第二引数にまとめて渡すこともできる。

// 月間売上テーブルを集計し、年間売上の多い順に取得
$current_page = 5;
$builder = SQL::select('items')
    ->limit(25)
    ->offset(25 * ($current_page - 1));

/*
    SELECT items.*
    FROM items
    LIMIT 25 OFFSET 100
*/

$builder = SQL::select('items')
    ->limit(25, 25 * ($current_page - 1));

/*
    SELECT items.*
    FROM items
    LIMIT 25, 100
*/

ページング

ページネーションに必要なLIMITとOFFSETの値を算出して指定する。

$builder = SQL::select('items')
    ->paging(current_page: 3, limit: 25);

全件数のカウント

MySQLのFOUND_ROWS関数は非推奨になったので、COUNT関数を用いて全件数を取得する必要があるが、
レコードを取得するするSQLと全件数を取得するSQLは、似たような記述ではあるものの共通化が手間なので、
手軽に全件数を取得するSQLを組み立てる方法としてSelect::buildFoundRowsを用意している。

$builder = SQL::select('customers')
    ->where(fn(Rules $rules) => $rules
        ->when(
            is_string($get['customer_code']) && $get['customer_code'] !== '',
            fn(Rules $rules) => $rules->equals('customer_code', $get['customer_code'])
        )
        ->when(
            is_string($get['customer_name']) && $get['customer_name'] !== '',
            fn(Rules $rules) => $rules->equals('customer_name', $get['customer_name'])
        )
    )
    ->paging(current_page: $get['page'], limit: 25);
    
/*
     SELECT *
     FROM customers
     WHERE customer_code = ? AND customer_name = ?
     LIMIT ?, ?
*/

// WHERE句などの記述はそのままに、COUNT関数を実行するSQLを組み立てるSelectクラスのオブジェクトを取得する。
$count_builder = $builder->buildFoundRows();
  
/*
     SELECT COUNT(*) OVER()
     FROM customers
     WHERE customer_code = ? AND customer_name = ?
     LIMIT ?
  
     ※LIMITは常に1が指定される
*/

Rulesクラスの詳細

whereメソッドなどで用いるRulesクラスのメソッドを詳しく紹介する。

条件分岐

whenメソッドで条件分岐を記述できる。これはSQLの構文ではなく、
PHPのif文によって動的に検索条件を組み変える際の、より良い代替手段として用意されている。

whenの第一引数に条件式（bool値）を渡し、第二引数にcallableを渡す。

// ユーザーが検索フォームで入力した値
$_GET = [
    'item_name' => '商品名',
    'category_id' => ''
];

$builder = SQL::select('items')
    ->where(fn (Rules $rules) => $rules
        ->isNotNull('deleted_flag')
        ->when(
            isset($_GET['name']) && $_GET['item_name'] !== '', // この式がtrueなら第二引数が有効になり、WHERE句に追記される
            fn (Rules $rules) => $rules->like('item_name', $_GET['item_name'])
        )
        ->when(
            isset($_GET['category_id']) && $_GET['category_id'] !== '',
            fn (Rules $rules) => $rules->equals('category_id', $_GET['category_id'])
        ));

/*
    category_idは条件を満たさないので含まれない

    SELECT *
    FROM items
    WHERE
        deleted_flag IS NOT NULL
        AND item_name LIKE ?
*/

OR

Rulesクラスの同じオブジェクトに対して指定した条件は、すべて並列にANDで繋がれる。
ORを書きたい場合は、anyメソッドを用いて、別のRulesオブジェクトに指定する形になる。Rulesのネスト。

// category_idが1か2の商品情報を取得する

$builder = SQL::select('items')
    ->where(fn (Rules $rules) => $rules
        ->isNotNull('deleted_flag')
        ->any(fn (Rules $rules) => $rules
            ->equals('category_id', 1)
            ->equals('category_id', 2)));

/*
    SELECT items.*
    FROM items
    WHERE
        deleted_flag IS NOT NULL
        AND (
            category_id = ?
            OR category_id = ?
        )
*/

INとサブクエリ

$builder = SQL::select('items')
    ->where(fn (Rules $rules) => $rules
        ->isNotNull('deleted_flag')
        ->in('category_id', [1, 2]));

/*
    SELECT items.*
    FROM items
    WHERE
        deleted_flag IS NOT NULL
        AND category_id IN (?, ?)
*/

サブクエリ

サブクエリが静的なSQLなら文字列で記述しても問題ない。
値のバインドを伴う動的なSQLではSQL::selectを使う必要がある。

WHERE ... INにサブクエリを用いる

inメソッドの第二引数に配列ではなくSelectクラスのオブジェクトを渡すことで、サブクエリを用いた絞り込みができる。

// 何らかの申請情報を持つrequestsテーブルと、承認された申請のidを保持するapproved_requestsテーブルを想定。
// サブクエリを用いて、承認済みの申請の情報のみを取得する。

$builder = SQL::select('requests')
    ->where(fn (Rules $rules) => $rules
        ->in('id', SQL::select('approved_requests')->columns('request_id')));

/*
    SELECT requests.*
    FROM requests
    WHERE
        id IN (SELECT request_id FROM approved_requests)
*/

比較にサブクエリを用いる

サブクエリの結果を値の比較に用いる。

// 商品全体の平均価格より価格が高い商品を抽出するSQL
$builder = SQL::select('items')
    ->where(fn (Rules $rules) => $rules
        ->compare('price', '>', SQL::select('items')->columns('AVG(price)')));

テーブル結合にサブクエリを用いる

$builder = SQL::select('items')
    ->columns('id', 'item_name')
    ->leftJoin(
        SQL::alias(
            // サブクエリ
            SQL::select('item_categories')->where(
                fn (Rules $rules) => $rules->isNotNull('deleted_flag')
            ),
            // 別名
            'item_categories'
        ),
        // 結合条件
        SQL::rules()->equals('items.category_id', SQL::raw('item_categories.id'))
    );

// 下記でも同じ結果になる
$builder = SQL::select('items')
    ->columns('id', 'item_name')
    ->leftJoinSubQuery(
        // サブクエリ
        SQL::select('item_categories')->where(
            fn (Rules $rules) => $rules->->isNotNull('deleted_flag')
        ),
        // 別名
        'item_categories',
        // 結合条件
        SQL::rules()->equals('items.category_id', SQL::raw('item_categories.id'))
    );

カラム指定にサブクエリを用いる

// 商品テーブルを集計し、カテゴリごとの平均価格と、全体の平均価格を取得するSQLを想定
$builder = SQL::select('items')
    ->columns(
        'id',
        'item_name',
        'AVG(price) AS category_avg_price' // カテゴリごとの平均
        [SQL::select('items')->columns('AVG(price)'), 'all_avg_price'] // 全体の平均
    )
    ->groupBy('category_id');

CASE文

下記のようなクエリの組み立てを想定する。

SELECT
    id,
    category_name,
    CASE
        WHEN id = ? THEN 'selected'
        ELSE ''
    END AS selected
FROM categories

CaseStatementを用いてCASE文を記述する例

ビルダーの機能だけで組み立てるので安全だが、可読性に難がある。

// CASEを使う場合、別名が必要なので、Aliasを利用する。
$builder = SQL::select('categories')
    ->columns(
        'id',
        'category_name',
        // CaseStatementと別名の文字列をAliasに渡す。
        SQL::alias(
            SQL::case()
                ->whenThen(SQL::rules()->equals('id', $post['category_id']), 'selected')
                ->else(''),
            'selected' // ここが別名（AS句）になる
        )
    );

// 下記でも同じ結果になる
$builder = SQL::select('categories')
    ->columns(
        'id',
        'category_name',
        // CaseStatementと別名の文字列をセットの配列として渡す。
        [
            SQL::case()
                ->whenThen(SQL::rules()->equals('id', $post['category_id']), 'selected')
                ->else(''),
            'selected' // ここが別名（AS句）になる
        ]
    );

プレースホルダを含むCASE文の文字列を渡すパターン

$builder = SQL::select('categories')
    ->columns(
        'id',
        'category_name',
        SQL::raw("CASE WHEN id = ? THEN 'selected' ELSE '' END AS selected")
            ->bindValue($post['category_id']) // 値をバインド
    );

INSERT

通常のINSERT文

// 取引先ごとの月間予算を保持するmonthly_budgetsテーブルを想定
$query = SQL::insert('monthly_budgets')
    ->values([
        'customer_code' => '00001',
        'year' => 2022,
        'month' => 1,
        'amount' => 300000
    ]);

/*
    INSERT INTO monthly_budgets (
        customer_code, year, month, amount
    ) VALUES (
        ?, ?, ?, ?
    )
*/

ON DUPLICATE KEY UPDATE

ユニークキーなどの制約に引っかかることでINSERTに失敗したら、代わりにUPDATEを実行したい場合がある。
SELECTで存在チェックをしてPHP側で条件分岐を書いてもいいが、ON DUPLICATE KEY UPDATEを用いる方法もある。
これにより「あればINSERT，なければUPDATE」の動きを、ひとつのSQLで済ませることができる。

多対多の中間テーブルに対して繰り返し登録・更新をおこなうような処理で役に立つかもしれない。

// customer_code、year、monthの3カラムで複合ユニーク制約をかけている前提。
// 制約に引っかかったら、同じ値でのUPDATEが実行される。
$query = SQL::insert('monthly_budgets')
    ->values([
        'customer_code' => '00001',
        'year' => 2022,
        'month' => 1,
        'amount' => 300000
    ])
    ->onDuplicateKeyUpdate();

/*
    INSERT INTO monthly_budgets (
        customer_code, year, month, amount
    ) VALUES (
        ?, ?, ?, ?
    ) ON DUPLICATE KEY UPDATE (
        customer_code = ?, year = ?, month = ?, amount = ?
    )
*/

BULK INSERT

Insert::valuesを続けて呼び出すことで、複数のレコードを一括登録できる。

$query = SQL::insert('monthly_budgets')
    ->values([
        'customer_code' => '00001',
        'year' => 2022,
        'month' => 1,
        'amount' => 300000
    ])
    ->values([
        'customer_code' => '00002',
        'year' => 2022,
        'month' => 2,
        'amount' => 310000
    ]);

UPDATE

UPDATEの条件はSELECTと同様、whereメソッドを呼び出すことで記述する。

// item_codeが1000000かつ、deleted_flagがNULLではないレコードを更新
$record = [
    'item_name' => '商品3',
    'category_id' => 2
];

$query = SQL::update('items')
    ->sets($record)
    ->where(fn(Rules $rules) => $rules
        ->isNotNull('deleted_flag')
        ->equals('item_code', '1000000'));

/*
    UPDATE items
    SET
        item_name = ?,
        category_id = ?
    WHERE
        deleted_flag IS NOT NULL
        AND id = ?
*/

DELETE

DELETEの条件もwhereメソッドにcallableを渡し、Rulesクラスを用いる。

$query = SQL::delete('approved_requests')->where(
    fn(Rules $rules) => $rules->equals('id', 1)
);

/*
    DELETE FROM approved_requests
    WHERE id = ?
*/