jayrods/query-builder

简化SQL查询编写的简单查询构建器

维护者

详细信息

github.com/Jadersonrilidio/querybuilder

源代码

问题

安装: 15

依赖: 0

建议者: 0

安全性: 0

星标: 0

关注者: 1

分支: 0

开放问题: 0

v1.1.6 2023-03-01 21:57 UTC

Requires (Dev)

Suggests

GPL-3.0-or-later c1aa4e58ceef01c8888d4b77d9178f0d9a458f5e

sqlqueryquerybuilderjayrods

This package is auto-updated.

Last update: 2024-09-30 01:46:45 UTC

README

一个轻量级、直接且易于使用的SQL查询构建器，适用于DML和DQL查询。

Package logo

Software License Downloads Latest Version Code Size

关于

手动编写SQL查询一直是开发人员关注的重点！这不仅会让你的代码变得复杂（正如一些PHP纯粹主义者可能会说），还会影响可测试性、简单性，并增加开发和维护的工作量和时间。本着这个想法，这个包提供了一个简单的方法，将SQL查询封装到PHP类和方法中，提供易于使用的语法和额外的功能，以确保查询按正确的方式编写。

安装

通过Composer安装非常简单

$ composer require jayrods/query-builder

或者手动将其添加到你的composer.json文件中。

升级

我们遵循语义版本控制，这意味着在主要版本之间可能发生破坏性更改。每当有主要版本发布时，我们都会在这里提供升级指南这里。

入门

在开始使用组件之前，了解其结构是很重要的。

QueryBuilder组件分为4种不同的使用场景，每个场景代表一个CRUD操作（创建、读取、更新和删除）。为了简化，组件使用QueryBuilder工厂对象轻松创建所有类型的构建器。

以下是一个使用该组件的示例脚本

示例 01

use Jayrods\QueryBuilder\QueryBuilder;

// QueryBuilderFactory instance.
$builderFactory = new QueryBuilder();

// Create a SELECT queryBuilder use-case.
$builder = new $builderFactory->create(QueryBuilder::SELECT);

$selectQuery = $builder->selectFrom('users')
    ->column('uuid')
    ->columnAs('name', 'username')
    ->column('email')
    ->where('uuid', '=', 'uuid')
    ->build();

echo $selectQuery;

输出

"SELECT users.uuid, users.name AS username, users.email FROM users WHERE uuid = :uuid"

注意： create()方法需要一个参数来指定要应用的使用场景。在这种情况下，强烈建议使用可用的QueryBuilder对象常量，如下所示

QueryBuilder::DELETE = 'delete';
QueryBuilder::INSERT = 'insert';
QueryBuilder::SELECT = 'select';
QueryBuilder::UPDATE = 'update';

注意： 按照惯例，组件只与参数化值一起工作，遵循:parameter或?的表示法，如下所示参数化选项。

注意： build()方法返回构建的查询并将其保存在内部，提供一个query()方法来检索查询，无论何时需要都可以获取查询。它还将所有其他对象的属性重置为默认值，使其能够在必要时迅速开始构建另一个查询。

echo $builder->query();

注意： 再次调用build()方法将覆盖之前保存的查询。

注意： 组件还允许查询的部分构建，为用户提供更大的灵活性

$builder->selectFrom('users');
$builder->column('uuid');
$builder->column('email');
$builder->columnAs('name', 'username');
$builder->where('uuid', '=', 'uuid');

$query = $builder->build();

echo $query;

输出

"SELECT users.uuid, users.name AS username, users.email FROM users WHERE uuid = :uuid"

部分构建简化了某些情况，其中SQL查询依赖于某些条件，如下面的示例所示

$columns = ['uuid', 'name', 'email'];
$userUuid = 'example-user-uuid';

$builder->selectFrom('users');

foreach ($columns as $column) {
    $builder->column($column)
}

if (isset($userUuid)) {
    $builder->where('uuid', '=', 'uuid');
}

$query = $builder->build();

echo $query;

输出

"SELECT users.uuid, users.name, users.email FROM users WHERE uuid = :uuid"

注意： 重要的是要说明，每个使用场景实例都有其自己的方法集，这些方法可能彼此不同，即使方法名相同。在下面的示例中，您可以看到每个使用场景调用方法的差异

示例 02

use Jayrods\QueryBuilder\QueryBuilder;

// QueryBuilderFactory instance.
$builderFactory = new QueryBuilder();

// Create a INSERT queryBuilder use-case.
$builder = new $builderFactory->create(QueryBuilder::INSERT);

$insertQuery = $builder->insertInto('users')
    ->column('name')
    ->column('email')
    ->build();

echo $insertQuery;

输出

"INSERT INTO users (name, email) VALUES (:name, :email)"

示例 03

use Jayrods\QueryBuilder\QueryBuilder;

// QueryBuilderFactory instance.
$builderFactory = new QueryBuilder();

// Create a DELETE queryBuilder use-case.
$builder = new $builderFactory->create(QueryBuilder::DELETE);

$deleteQuery = $builder->delete('users')
    ->where('uuid', '=')
    ->or('uuid', '=', 'param2')
    ->build();

echo $deleteQuery;

输出

"DELETE FROM users WHERE uuid = :uuid OR uuid = :param2"

示例 04

use Jayrods\QueryBuilder\QueryBuilder;

// QueryBuilderFactory instance.
$builderFactory = new QueryBuilder();

// Create a UPDATE queryBuilder use-case.
$builder = new $builderFactory->create(QueryBuilder::UPDATE);

$deleteQuery = $builder->update('users')
    ->column('name')
    ->column('email')
    ->where('uuid', '=')
    ->build();

echo $deleteQuery;

输出

"UPDATE users SET name = :name, email = :email WHERE uuid = :uuid"

更多详细说明请参阅下面的各节

DELETE查询

方法

// Start building DELETE query.
DeleteQueryBuilder::delete(string $table): self

// Start WHERE clause.
DeleteQueryBuilder::where(string $column, string $operator, ?string $binder = null): self

// Start WHERE NOT clause.
DeleteQueryBuilder::whereNot(string $column, string $operator, ?string $binder = null): self

// Start WHERE IN clause.
DeleteQueryBuilder::whereIn(string $column, string $subquery): self

// Start WHERE NOT IN clause.
DeleteQueryBuilder::whereNotIn(string $column, string $subquery): self

// Start WHERE BETWEEN clause.
DeleteQueryBuilder::whereBetween(string $column, ?string $left = null, ?string $right = null): self

// Start WHERE NOT BETWEEN clause.
DeleteQueryBuilder::whereNotBetween(string $column, ?string $left = null, ?string $right = null): self

// Add AND clause to conditions.
DeleteQueryBuilder::and(string $column, string $operator, ?string $binder = null): self

// Add AND NOT clause to conditions.
DeleteQueryBuilder::andNot(string $column, string $operator, ?string $binder = null): self

// Add AND BETWEEN clause to conditions.
DeleteQueryBuilder::andBetween(string $column, ?string $left = null, ?string $right = null): self

// Add AND NOT BETWEEN clause to conditions.
DeleteQueryBuilder::andNotBetween(string $column, ?string $left = null, ?string $right = null): self

// Add OR clause to conditions.
DeleteQueryBuilder::or(string $column, string $operator, ?string $binder = null): self

// Add OR NOT clause to conditions.
DeleteQueryBuilder::orNot(string $column, string $operator, ?string $binder = null): self

// Add OR BETWEEN clause to conditions.
DeleteQueryBuilder::orBetween(string $column, ?string $left = null, ?string $right = null): self

// Add OR NOT BETWEEN clause to conditions.
DeleteQueryBuilder::orNotBetween(string $column, ?string $left = null, ?string $right = null): self

// Build the query and set it to the query attribute.
DeleteQueryBuilder::build(): string

// Return the last built query or empty string.
DeleteQueryBuilder::query(): string

// Return array with used parameterized names acresced by ':' notation.
DeleteQueryBuilder::getBindParams(): array

示例

示例 01

$builderFactory = new QueryBuilder();
$builder = $builderFactory->create(QueryBuilder::DELETE);

$builder->delete('users')
    ->where('birth_date', '<', 'birth_date')
    ->or('name', 'LIKE', 'username')
    ->build();

echo $builder->query();

输出

"DELETE FROM users WHERE birth_date < :birth_date OR name LIKE :username"

示例 02

$builderFactory = new QueryBuilder();
$builder = $builderFactory->create(QueryBuilder::DELETE);

$builder->delete('products')
    ->whereBetween('price')
    ->build();

echo $builder->query();

输出

"DELETE FROM products WHERE price BETWEEN :price_left AND :price_right"

示例 03

$builderFactory = new QueryBuilder();
$builder = $builderFactory->create(QueryBuilder::DELETE);

$subquery = "SELECT * FROM users WHERE id BETWEEN :id_left AND :id_right";

$builder->delete('users')
    ->whereIn('name', $subquery)
    ->build();

echo $builder->query();

输出

"DELETE FROM users WHERE name IN (SELECT * FROM users WHERE id BETWEEN :id_left AND :id_right)"

INSERT查询

方法

// Start building INSERT INTO query.
InsertQueryBuilder::insertInto(string $table): self

// Set column and respective binder name as value.
InsertQueryBuilder::column(string $column, ?string $binder = null): self

// Build the query and set it to the query attribute.
InsertQueryBuilder::build(): string

// Return the last built query or empty string.
InsertQueryBuilder::query(): string

// Return array with used parameterized names acresced by ':' notation.
InsertQueryBuilder::getBindParams(): array

示例

示例 01

$builderFactory = new QueryBuilder();
$builder = $builderFactory->create(QueryBuilder::INSERT);

$builder->insertInto('users')
    ->column('name', 'username')
    ->column('email', 'useremail')
    ->build();

echo $builder->query();

输出

"INSERT INTO users (name, email) VALUES (:username, :useremail)"

SELECT查询

方法

// Start building SELECT FROM query.
SelectQueryBuilder::selectFrom(string $table): self

// Add column to be selected.
SelectQueryBuilder::column(string $column, ?string $refTable = null): self

// Add column with AS clause to be selected.
SelectQueryBuilder::columnAs(string $column, string $as, ?string $refTable = null): self

// Start WHERE clause.
SelectQueryBuilder::where(string $column, string $operator, ?string $binder = null): self

// Start WHERE NOT clause.
SelectQueryBuilder::whereNot(string $column, string $operator, ?string $binder = null): self

// Start WHERE IN clause.
SelectQueryBuilder::whereIn(string $column, string $subquery): self

// Start WHERE NOT IN clause.
SelectQueryBuilder::whereNotIn(string $column, string $subquery): self

// Start WHERE BETWEEN clause.
SelectQueryBuilder::whereBetween(string $column, ?string $left = null, ?string $right = null): self

// Start WHERE NOT BETWEEN clause.
SelectQueryBuilder::whereNotBetween(string $column, ?string $left = null, ?string $right = null): self

// Add AND clause to conditions.
SelectQueryBuilder::and(string $column, string $operator, ?string $binder = null): self

// Add AND NOT clause to conditions.
SelectQueryBuilder::andNot(string $column, string $operator, ?string $binder = null): self

// Add AND BETWEEN clause to conditions.
SelectQueryBuilder::andBetween(string $column, ?string $left = null, ?string $right = null): self

// Add AND NOT BETWEEN clause to conditions.
SelectQueryBuilder::andNotBetween(string $column, ?string $left = null, ?string $right = null): self

// Add OR clause to conditions.
SelectQueryBuilder::or(string $column, string $operator, ?string $binder = null): self

// Add OR NOT clause to conditions.
SelectQueryBuilder::orNot(string $column, string $operator, ?string $binder = null): self

// Add OR BETWEEN clause to conditions.
SelectQueryBuilder::orBetween(string $column, ?string $left = null, ?string $right = null): self

// Add OR NOT BETWEEN clause to conditions.
SelectQueryBuilder::orNotBetween(string $column, ?string $left = null, ?string $right = null): self

// Add LIMIT clause.
SelectQueryBuilder::limit(int $limit): self

// Add ORDER BY clause.
SelectQueryBuilder::orderBy(string $column): self

// Sort the result order ascending.
SelectQueryBuilder::asc(): self

// Sort the result order descending.
SelectQueryBuilder::desc(): self

// Add INNER JOIN clause.
SelectQueryBuilder::innerJoin(string $joinTable, string $columnTable, string $operator, string $columnJoinTable): self

// Add LEFT JOIN clause.
SelectQueryBuilder::leftJoin(string $joinTable, string $columnTable, string $operator, string $columnJoinTable): self

// Add RIGHT JOIN clause.
SelectQueryBuilder::rightJoin(string $joinTable, string $columnTable, string $operator, string $columnJoinTable): self

// Build the query and set it to the query attribute.
SelectQueryBuilder::build(): string

// Return the last built query or empty string.
SelectQueryBuilder::query(): string

// Return array with used parameterized names acresced by ':' notation.
SelectQueryBuilder::getBindParams(): array

示例

示例 01

$builderFactory = new QueryBuilder();
$builder = $builderFactory->create(QueryBuilder::SELECT);

$builder->selectFrom('users')
    ->build();

echo $builder->query();

输出

"SELECT * FROM users"

注意：在SELECT用例中，如果用户没有分配任何列，构建器会理解应该检索所有列，并应用*语法。

示例 02

$builderFactory = new QueryBuilder();
$builder = $builderFactory->create(QueryBuilder::SELECT);

$builder->selectFrom('users')
    ->columnAs('name', 'username')
    ->columnAs('email', 'useremail')
    ->columnAs('area_code', 'phonearea', 'phones')
    ->columnAs('number', 'phonenumber', 'phones')
    ->innerJoin('phones', 'user_uuid', '=', 'uuid')
    ->where('uuid', '=', 'uuid')
    ->build();

echo $builder->query();

输出

"SELECT
    users.name AS username, users.email AS useremail,
    phones.area_code AS phonearea, phones.number AS phonenumber
    FROM users
    INNER JOIN phones ON phones.user_uuid = users.uuid
    WHERE uuid = :uuid"

示例 03

$builderFactory = new QueryBuilder();
$builder = $builderFactory->create(QueryBuilder::SELECT);

$builder->selectFrom('users')
    ->column('uuid')
    ->column('name')
    ->column('email')
    ->orderBy('name')
    ->asc()
    ->limit(20)
    ->build();

echo $builder->query();

输出

"SELECT users.uuid, users.name, users.email FROM users ORDER BY users.name ASC LIMIT 20;

UPDATE查询

方法

// Start building UPDATE query.
UpdateQueryBuilder::update(string $table): self

// Add column to be updated with respective binder name.
UpdateQueryBuilder::column(string $column, ?string $binder = null): self

// Start WHERE clause.
UpdateQueryBuilder::where(string $column, string $operator, ?string $binder = null): self

// Start WHERE NOT clause.
UpdateQueryBuilder::whereNot(string $column, string $operator, ?string $binder = null): self

// Start WHERE IN clause.
UpdateQueryBuilder::whereIn(string $column, string $subquery): self

// Start WHERE NOT IN clause.
UpdateQueryBuilder::whereNotIn(string $column, string $subquery): self

// Start WHERE BETWEEN clause.
UpdateQueryBuilder::whereBetween(string $column, ?string $left = null, ?string $right = null): self

// Start WHERE NOT BETWEEN clause.
UpdateQueryBuilder::whereNotBetween(string $column, ?string $left = null, ?string $right = null): self

// Add AND clause to conditions.
UpdateQueryBuilder::and(string $column, string $operator, ?string $binder = null): self

// Add AND NOT clause to conditions.
UpdateQueryBuilder::andNot(string $column, string $operator, ?string $binder = null): self

// Add AND BETWEEN clause to conditions.
UpdateQueryBuilder::andBetween(string $column, ?string $left = null, ?string $right = null): self

// Add AND NOT BETWEEN clause to conditions.
UpdateQueryBuilder::andNotBetween(string $column, ?string $left = null, ?string $right = null): self

// Add OR clause to conditions.
UpdateQueryBuilder::or(string $column, string $operator, ?string $binder = null): self

// Add OR NOT clause to conditions.
UpdateQueryBuilder::orNot(string $column, string $operator, ?string $binder = null): self

// Add OR BETWEEN clause to conditions.
UpdateQueryBuilder::orBetween(string $column, ?string $left = null, ?string $right = null): self

// Add OR NOT BETWEEN clause to conditions.
UpdateQueryBuilder::orNotBetween(string $column, ?string $left = null, ?string $right = null): self

// Build the query and set it to the query attribute.
UpdateQueryBuilder::build(): string

// Return the last built query or empty string.
UpdateQueryBuilder::query(): string

// Return array with used parameterized names acresced by ':' notation.
UpdateQueryBuilder::getBindParams(): array

示例

示例 01

$builderFactory = new QueryBuilder();
$builder = $builderFactory->create(QueryBuilder::UPDATE);

$builder->update('users')
    ->column('name')
    ->column('email')
    ->where('uuid', '=')
    ->build();

echo $builder->query();

输出

"UPDATE users SET name = :name, email = :email WHERE uuid = :uuid"

高级选项

QueryBuilder组件还提供了一些功能，通过抛出错误来帮助确保查询的正确构建，在一致性不一致时在控制台输出警告，在错误的构建方法调用时忽略意外。

主要的辅助功能是约束QueryBuilder模式查看约束模式。其主要功能是忽略错误的构建方法以避免错误的查询编写，并允许组件通过抛出异常或运行时回显控制台消息来通知用户。

所有不同的模式都可以通过环境变量或配置文件设置启用，如此处 - 环境变量和此处 - 配置文件所示。

约束模式

约束模式的主要功能是忽略错误的构建方法，避免错误的查询编写。它还允许组件通过在运行时抛出异常或回显控制台消息来通知发生的错误和采取的操作。

约束模式可以通过两种方式启用/禁用

首先，在.env文件中设置环境变量QB_ENABLE_CONSTRAINED_MODE；

其次，覆盖配置文件中的ENABLE_CONSTRAINED_MODE参数。更多关于配置文件信息

注意：默认情况下，约束模式设置为true。

QB_ENABLE_CONSTRAINED_MODE=true

在约束模式开启（QB_ENABLE_CONSTRAINED_MODE=true）的情况下，用户还有两个可选选项

通过环境变量QB_FAIL_ON_WRONG_METHOD_CALL启用/禁用约束模式在失败时抛出异常；

通过环境变量QB_ECHO_WARNINGS_ON_WRONG_METHOD_CALL启用/禁用约束模式在失败时回显消息；

注意：默认情况下

QB_FAIL_ON_WRONG_METHOD_CALL=false
QB_ECHO_WARNINGS_ON_WRONG_METHOD_CALL=true

重要！由于约束模式用于断言查询的正确构建，我们建议仅在开发中使用它，并在生产中禁用约束模式以提高组件性能。

环境变量选项

以下列出所有可用环境变量及其默认值

QB_ENABLE_CONSTRAINED_MODE=true
QB_FAIL_ON_WRONG_METHOD_CALL=false
QB_ECHO_WARNINGS_ON_WRONG_METHOD_CALL=true
QB_PARAMETERIZED_MODE=true
QB_PARAMETERIZED_MODE_FAIL_ON_ERROR=false
QB_PARAMETERIZED_MODE_ECHO_WARNINGS_ON_ERROR=true

参数化选项

QB_PARAMETERIZED_MODE=true
QB_PARAMETERIZED_MODE_FAIL_ON_ERROR=false
QB_PARAMETERIZED_MODE_ECHO_WARNINGS_ON_ERROR=true

配置文件

此外，还可以通过queryBuilderConfig.php文件设置所有组件的选项。默认情况下，组件使用包中./config文件夹中的配置文件，但是可以将此文件发布到根./config文件夹中，甚至到用户定义的目录查看。

要使用用户定义目录中的配置文件，需要向QueryBuilder对象提供用户定义的路径，如下面的示例所示

$configFilePath = dirname(__DIR__) . '/path/to/file/queryBuilderConfig.php';

$builderFactory = new QueryBuilder($configFilePath);

发布配置文件

组件提供了一个bin脚本来正确发布queryBuilderConfig.php文件。用户可以在控制台输入以下命令来发布配置文件

php vendor/bin/qb_publish_config <optional:path>

不带参数时，尝试在./config/文件夹中创建配置文件，如果存在

php vendor/bin/qb_publish_config

带参数时，尝试在指定的文件夹中创建，如果存在

php vendor/bin/qb_publish_config ./path/to/folder/

贡献

我们感谢任何愿意为使该项目足够完整以适用于实际项目使用而做出贡献的开发者的善意，无论是通过建议或添加新功能、修复漏洞和其他性质问题。如果您愿意为此项目做出贡献，请发送电子邮件至jayrods。

安全性

如果在该软件包中发现安全漏洞，请报告问题或发送电子邮件至 jayrods。我们将及时处理所有安全漏洞。感谢您的关注。

许可证

QueryBuilder遵循GPL V3.0许可证。