haikara/sequel-builder

此包的最新版本（0.1.0）没有提供许可信息。

维护者

详细信息

github.com/HaikaraSakura/sequel-builder

源代码

问题

安装: 3

依赖: 0

建议者: 0

安全: 0

星标: 0

关注者: 1

分支: 0

开放问题: 0

0.1.0 2024-08-17 12:24 UTC

Requires

Unknown License d7b78e920ece49ae42cb5e4940fb136b48e0f397

This package is auto-updated.

Last update: 2024-09-17 12:36:19 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子句进行筛选，请以callable值的形式向where方法传递。
callable值的执行，将Rules类的实例传递给参数，在调用所需方法后返回。

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语句动态构建搜索条件时的更好替代方案。

将条件式（bool值）传递给when的第一个参数，将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对象。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中使用子查询

通过将Select类的对象传递给in方法的第二个参数而不是数组，可以使用子查询进行筛选。

// 何らかの申請情報を持つ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。
可以在PHP侧使用SELECT进行存在检查并编写条件分支，也可以使用ON DUPLICATE KEY UPDATE。
这样，可以使用一个SQL语句完成“如果存在则INSERT，如果不存在则UPDATE”的操作。

在需要重复注册和更新多对多中间表的处理中可能很有用。

// 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的条件也通过将callable传递给where方法并使用Rules类来描述。

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

/*
    DELETE FROM approved_requests
    WHERE id = ?
*/