pthreat/sql-query-builder

支持绑定和复杂查询生成的SQL QueryInterface BuilderInterface。

维护者

详细信息

github.com/pthreat/php-sql-query-builder

主页

源代码

问题

安装数: 1,968

依赖: 0

建议者: 0

安全: 0

星标: 1

关注者: 2

分支: 2

开放问题: 0

1.0.4 2023-01-11 16:53 UTC

Requires

Requires (Dev)

MIT 0459230c5275271095fa0196946953faa07c76e9

ormsqlmysqlqueryquery builderbuilder

This package is auto-updated.

Last update: 2024-09-11 20:26:51 UTC

README

原始项目似乎已被放弃（最后修改日期为2020年）

我分叉此项目的最主要原因是为该项目提供支持，其中一个大问题是在查询值中将NULL评估为'NULL'。

这里是原始存储库的链接

https://github.com/nilportugues/php-sql-query-builder

一个优雅、轻量级且高效的SQL查询构建器，具有流畅的接口和SQL语法，支持绑定和复杂查询生成。 无需建立数据库连接即可工作。

1. 安装 ↑

推荐通过Composer安装SQL查询构建器。运行以下命令进行安装

php composer.phar require pthreat/sql-query-builder

2. 构建器 ↑

SQL查询构建器允许使用SQL-2003方言（默认）和MySQL方言（扩展了SQL-2003方言）标准地生成复杂SQL查询。

2.1. 通用构建器 ↑

通用查询构建器是这个类的默认构建器，并写入标准SQL-2003。

默认情况下，所有列别名都使用单引号'进行转义。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()->setTable('user');    

echo $builder->write($query);

输出

SELECT user.* FROM user

2.2. MySQL构建器 ↑

MySQL查询构建器有一个自己的类，该类继承自SQL-2003构建器。所有列都将用波浪线`符号包裹。

默认情况下，所有表和列别名都使用波浪线符号进行转义。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\MySqlBuilder;

$builder = new MySqlBuilder(); 

$query = $builder->select()->setTable('user'); 

echo $builder->write($query);

输出

SELECT user.* FROM `user`

2.3. 易读输出 ↑

通用和MySQL查询构建器都可以编写复杂的SQL查询。

每个开发者迟早都需要修改复杂查询的输出，SQL查询构建器包含一个人性化的输出方法，因此当需要时，writeFormatted方法就派上用场。

请注意，在生产模式下应尽可能避免使用writeFormatted，因为它会增加解析和重新格式化生成的语句所带来的不必要的开销。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()->setTable('user');  

echo $builder->writeFormatted($query);

输出

SELECT
    user.*
FROM
    user

更复杂的例子可以在文档中找到。

3. 构建查询 ↑

3.1. SELECT语句 ↑

3.1.1. 基本SELECT语句 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(['user_id','name','email']);
     
echo $builder->write($query);

输出

SELECT user.user_id, user.name, user.email FROM user

3.1.2. 别名SELECT语句 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(['userId' => 'user_id', 'username' => 'name', 'email' => 'email']);
       
echo $builder->write($query);

输出

SELECT user.user_id AS 'userId', user.name AS 'username', user.email AS 'email' FROM user

3.1.3. 包含WHERE语句的SELECT ↑

使用WHERE条件进行过滤的默认逻辑运算符是AND。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns([
        'userId' => 'user_id',
        'username' => 'name',
        'email' => 'email'
    ])
    ->where()
    ->greaterThan('user_id', 5)
    ->notLike('username', 'John')
	->end();
      
echo $builder->writeFormatted($query);

输出

SELECT
    user.user_id AS 'userId',
    user.name AS 'username',
    user.email AS 'email'
FROM
    user
WHERE
    (user.user_id < :v1)
  AND (user.username NOT LIKE :v2)

3.1.4. 复杂WHERE条件 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->where()
    ->equals('user_id', 1)
    ->equals('user_id', 2)
    ->subWhere("OR")
    ->lessThan($column, 10)
    ->greaterThan('user_id', 100)
	->end();

echo $builder->writeFormatted($query);

输出

SELECT
    user.*
FROM
    user
WHERE
    (user.user_id = :v1)
  AND (user.user_id = :v2)
  AND (
        (user.user_id < :v3)
        OR (user.user_id > :v4)
    )

3.1.5. JOIN & LEFT/RIGHT/INNER/CROSS JOIN SELECT语句 ↑

JOIN、LEFT JOIN、RIGHT JOIN、INNER JOIN、CROSS JOIN的语法完全相同。

以下是一个示例，选择两个表和连接的表列，并使用两个表中的列进行排序。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns([
            'userId'   => 'user_id',
            'username' => 'name',
            'email'    => 'email',
            'created_at'
    ])
    ->orderBy('user_id', OrderBy::DESC)
    ->leftJoin(
        'news', //join table
        'user_id', //origin table field used to join
        'author_id', //join column
         ['newsTitle' => 'title', 'body', 'created_at', 'updated_at']
     )
    ->on()
    ->equals('author_id', 1); //enforcing a condition on the join column

$query
    ->where()
    ->greaterThan('user_id', 5)
    ->notLike('username', 'John')
	->end();

$query
    ->orderBy('created_at', OrderBy::DESC);

echo $builder->writeFormatted($query);

输出

SELECT
    user.user_id AS 'userId',
    user.name AS 'username',
    user.email AS 'email',
    user.created_at,
    news.title AS 'newsTitle',
    news.body,
    news.created_at,
    news.updated_at
FROM
    user
        LEFT JOIN
    news
    ON
            (news.author_id = user.user_id)
            AND (news.author_id = :v1)
WHERE
    (user.user_id < :v2)
  AND (user.username NOT LIKE :v3)
ORDER BY
    user.user_id DESC,
    news.created_at DESC;

3.1.6. 计数行 ↑

计数行有三种可能的方式，使用ALL选择器*，指定列或指定列及其别名。

3.1.6.1. 使用ALL选择器进行计数

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
        ->setTable('user')
        ->count()

echo $builder->write($query);

输出

SELECT COUNT(*) FROM user;

3.1.6.2. 使用列作为选择器

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
        ->setTable('user')
        ->count('user_id')

echo $builder->write($query);

输出

SELECT COUNT(user.user_id) FROM user;

3.1.6.3. 使用列作为选择器

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
        ->setTable('user')
        ->count('user_id', 'total_users')

echo $builder->write($query);

输出

SELECT COUNT(user.user_id) AS 'total_users' FROM user;

3.2. INSERT语句 ↑

INSERT语句非常简单。

3.2.1. 基本INSERT语句 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->insert()
    ->setTable('user')
    ->setValues([
        'user_id' => 1,
        'name'    => 'Nil',
        'contact' => 'contact@nilportugues.com',
    ]);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

INSERT INTO user (user.user_id, user.name, user.contact) VALUES (:v1, :v2, :v3)
[':v1' => 1, ':v2' => 'Nil', ':v3' => 'contact@nilportugues.com'];

3.3. UPDATE语句 ↑

UPDATE语句的工作方式与预期一致，设置值和条件以匹配行，然后设置。

以下提供了示例。

3.3.1. 基本UPDATE语句 ↑

重要的是要包含where语句，否则在执行语句时，所有表行都将被提供的值替换。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->update()
    ->setTable('user')
    ->setValues([
        'user_id' => 1,
        'name' => 'Nil',
        'contact' => 'contact@nilportugues.com'
    ])
    ->where()
    ->equals('user_id', 1)
	->end();

$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

UPDATE
    user
SET
    user.user_id = :v1,
    user.name = :v2,
    user.contact = :v3
WHERE
    (user.user_id = :v4)
[':v1' => 1, ':v2' => 'Nil', ':v3' => 'contact@nilportugues.com', ':v4' => 1];

3.3.2. 详细UPDATE语句 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Syntax\OrderBy;
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->update()
    ->setTable('user')
    ->setValues([
        'name' => 'UpdatedName',
    ]);
    
$query
    ->where()
    ->like('username', '%N')
    ->between('user_id', 1, 2000)
	->end();
        
$query
    ->orderBy('user_id', OrderBy::ASC)
    ->limit(1);            
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

UPDATE
    user
SET
    user.name = :v1
WHERE
    (user.username LIKE :v2)
  AND (user.user_id BETWEEN :v3 AND :v4)
ORDER BY
    user.user_id ASC
LIMIT :v5

3.4. DELETE语句 ↑

DELETE语句的使用方式与UPDATE相同，但不设置任何值。

以下提供了示例。

3.4.1. 使用DELETE语句清空表 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->delete()
    ->setTable('user');
   
$sql = $builder->write($query);

输出

DELETE FROM user

3.4.2. 基本DELETE语句 ↑

重要的是要包含where语句，否则在执行语句时，所有表行都将被提供的值删除。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->delete()
    ->setTable('user');

$query
    ->where()
    ->equals('user_id', 100)
	->end();

$query
    ->limit(1);
   
$sql = $builder->write($query);    
$values = $builder->getValues();

输出

DELETE FROM user WHERE (user.user_id = :v1) LIMIT :v2
[':v1' => 100, ':v2' => 1];

3.4.2. 详细DELETE语句 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Syntax\OrderBy;
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->delete()
    ->setTable('user');
    
$query
    ->where()
    ->like('username', '%N')
    ->between('user_id', 1, 2000)
	->end();
        
$query
    ->orderBy('user_id', OrderBy::ASC)
    ->limit(1);            
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

DELETE FROM
    user
WHERE
    (user.username LIKE :v1)
  AND (user.user_id BETWEEN :v2 AND :v3)
ORDER BY
    user.user_id ASC
LIMIT :v4

3.5. INTERSECT语句 ↑

MySQL不支持INTERSECT。可以通过使用INNER JOIN语句获得相同的结果。

INTERSECT语句非常简单。

3.5.1. 基本INTERSECT语句 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
   
$query = $builder->intersect()
    ->add($select1)
    ->add($select2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT user.* FROM user
                       INTERSECT
SELECT user_email.* FROM user_email

3.6. MINUS语句 ↑

MySQL 不支持 MINUS 操作符。可以使用 LEFT JOIN 结合 IS NULL 或 NOT IN 条件来实现相同的结果。

MINUS 语句非常简单。

3.6.1 基础 MINUS 语句 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
   
$query = $builder->minus($select1, $select2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT user.* FROM user
                       MINUS
SELECT user_email.* FROM user_email

3.7. UNION 语句 ↑

UNION 语句非常简单。

3.7.1 基础 UNION 语句 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
   
$query = $builder->union()
    ->add($select1)
    ->add($select2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT user.* FROM user
UNION
SELECT user_email.* FROM user_email

3.8. UNION ALL 语句 ↑

UNION ALL 语句非常简单。

3.8.1 基础 UNION ALL 语句 ↑

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
   
$query = $builder->unionAll()
    ->add($select1)
    ->add($select2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT user.* FROM user
UNION ALL
SELECT user_email.* FROM user_email

4. 高级查询 ↑

4.1. 使用 WHERE 条件进行筛选 ↑

以下运算符可用于 WHERE 条件筛选

public function subWhere($operator = 'OR');
public function equals($column, $value);        
public function notEquals($column, $value);
public function greaterThan($column, $value);
public function greaterThanOrEqual($column, $value);
public function lessThan($column, $value);
public function lessThanOrEqual($column, $value);
public function like($column, $value);
public function notLike($column, $value);
public function match(array $columns, array $values);
public function matchBoolean(array $columns, array $values);
public function matchWithQueryExpansion(array $columns, array $values);
public function in($column, array $values);
public function notIn($column, array $values);
public function between($column, $a, $b);
public function notBetween($column, $a, $b);
public function isNull($column);
public function isNotNull($column);
public function exists(Select $select);
public function notExists(Select $select);
public function addBitClause($column, $value);    
public function asLiteral($literal);

4.2. 修改 WHERE 逻辑运算符 ↑

WHERE 默认的运算符必须修改，通过向 where 方法传递逻辑运算符 OR。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->where('OR')
    ->equals('user_id', 1)
    ->like('name', '%N%')
	->end();       
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT user.* FROM user WHERE (user.user_id = :v1) OR (user.name LIKE :v2)

4.3. 使用 GROUP BY 和 HAVING 进行分组 ↑

多个 HAVING 条件连接的默认逻辑运算符是 AND。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns([
        'userId'   => 'user_id',
        'username' => 'name',
        'email'    => 'email',
        'created_at'
    ])
    ->groupBy(['user_id', 'name'])
    ->having()
    ->equals('user_id', 1)
    ->equals('user_id', 2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT
    user.user_id AS 'userId',
    user.name AS 'username',
    user.email AS 'email',
    user.created_at
FROM
    user
GROUP BY
    user.user_id, user.name
HAVING
    (user.user_id = :v1)
   AND (user.user_id = :v2)

4.3.1 可用的 HAVING 运算符 ↑

WHERE 语句中使用的相同运算符也可用于 HAVING 操作。

4.4. 修改 HAVING 逻辑运算符 ↑

HAVING 默认的运算符必须修改，通过向 having 方法传递逻辑运算符 OR。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns([
        'userId'   => 'user_id',
        'username' => 'name',
        'email'    => 'email',
        'created_at'
    ])
    ->groupBy(['user_id', 'name'])
    ->having('OR')
    ->equals('user_id', 1)
    ->equals('user_id', 2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT
    user.user_id AS 'userId',
    user.name AS 'username',
    user.email AS 'email',
    user.created_at
FROM
    user
GROUP BY
    user.user_id, user.name
HAVING
    (user.user_id = :v1)
    OR (user.user_id = :v2)

4.5. 将列作为 SELECT 语句 ↑

有时，需要将列设置为列。SQL 查询构建器也涵盖了这一点！请查看下面的示例。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$selectRole = $builder->select()
    ->setTable('role')
    ->setColumns(array('role_name'))
    ->limit(1)
    ->where()
    ->equals('role_id', 3);

$query = $builder->select()
    ->setTable('user')
    ->setColumns(array('user_id', 'username'))
    ->setSelectAsColumn(array('user_role' => $selectRole))
    ->setSelectAsColumn(array($selectRole))
    ->where()
    ->equals('user_id', 4)
	->end();
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT
    user.user_id,
    user.username,
    (
        SELECT
            role.role_name
        FROM
            role
        WHERE
            (role.role_id = :v1)
        LIMIT :v2, :v3
    ) AS 'user_role',
    (
        SELECT
            role.role_name
        FROM
            role
        WHERE
            (role.role_id = :v4)
        LIMIT :v5, :v6
    ) AS 'role'
FROM
    user
WHERE
    (user.user_id = :v7)

4.6. 列作为值 ↑

有时需要强制相同的列结构（例如：UNION），即使缺少列或值。使用值强制列可以满足这一需求。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(array('user_id', 'username'))
    ->setValueAsColumn('10', 'priority')
    ->where()
    ->equals('user_id', 1)
	->end();
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT
    user.user_id,
    user.username,
    :v1 AS 'priority'
FROM
    user
WHERE
    (user.user_id = :v2)

4.7. 使用函数的列 ↑

MAX 函数的示例。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(array('user_id', 'username'))
    ->setFunctionAsColumn('MAX', array('user_id'), 'max_id')
    ->where()
    ->equals('user_id', 1)
	->end(); 
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT
    user.user_id,
    user.username,
    MAX(user_id) AS 'max_id'
FROM
    user
WHERE
    (user.user_id = :v1)

CURRENT_TIMESTAMP 函数的示例。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(array('user_id', 'username'))
    ->setFunctionAsColumn('CURRENT_TIMESTAMP', array(), 'server_time')
    ->where()
    ->equals('user_id', 1)
	->end();
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();

输出

SELECT
    user.user_id,
    user.username,
    CURRENT_TIMESTAMP AS 'server_time'
FROM
    user
WHERE
    (user.user_id = :v1)

5. 查询注释 ↑

查询构建器允许使用 setComment 方法添加所有查询方法的注释。

一些有用的用例示例包括

  • 解释复杂查询或其存在的原因。
  • 从注释中找出慢查询。

用法

<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setComment('This is a comment');
    
$sql = $builder->write($query);

输出

-- This is a comment
SELECT user.* FROM user

6. 代码质量 ↑

测试已使用 PHPUnit 和 Travis-CI 完成。所有代码都已测试，以确保与 PHP 5.4 到 PHP 5.6 和 HHVM 兼容。

要运行测试套件，您需要 Composer

    php composer.phar install --dev
    php bin/phpunit

7. 作者 ↑

Nil Portugués Calderó

8. 许可证 ↑

SQL Query Builder 在 MIT 许可证下授权。

Copyright (c) 2014 Nil Portugués Calderó

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.