masnathan/sql-query-builder

该包已被弃用，不再维护。没有建议的替代包。

一个优雅、轻量级且高效的 SQL 查询接口构建器，支持绑定和复杂的查询生成。

维护者

详细信息

github.com/MASNathan/sql-query-builder

主页

源代码

安装量: 1,644

依赖者: 0

建议者: 0

安全性: 0

星标: 0

关注者: 2

分支: 102

v1.1.1 2015-06-11 09:57 UTC

Requires

Requires (Dev)

MIT a7cd725f70a038500cc4a5ee0f5b076d6cd030f8

ormsqlmysqlqueryquery builderbuilder

This package is not auto-updated.

Last update: 2022-02-01 12:48:00 UTC

README

Build Status Coverage Status Scrutinizer Code Quality SensioLabsInsight Latest Stable Version Total Downloads License

一个优雅、轻量级且高效的 SQL 查询构建器，具有流畅的接口 SQL 语法，支持绑定和复杂的查询生成。

1. 安装 ↑

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

php composer.phar require nilportugues/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');
      
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);

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');

$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. COUNT 行 ↑

行计数有三种可能的方式，使用 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);

$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);
        
$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);

$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);
        
$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 compare($column, $value, $operator);
    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 isNull($column);
    public function isNotNull($column);
    public function exists(Select $select);
    public function notExists(Select $select);
    public function addBitClause($column, $value);

4.2. 更改WHERE逻辑运算符 ↑

WHERE 默认运算符必须通过将逻辑运算符 OR 传递给 where 方法来更改。

用法

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

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->where('OR')
    ->equals('user_id', 1)
    ->like('name', '%N%');       
   
$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 默认运算符必须通过将逻辑运算符 OR 传递给 having 方法来更改。

用法

<?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);
   
$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. 列作为值 ↑

有时您需要强制相同的列结构（例如：UNIONs），即使缺少列或值。使用带有值的列可以确保覆盖。

用法

<?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);
   
$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. 使用FUNCTIONS的列 ↑

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); 
   
$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);
   
$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查询构建器许可协议为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.