README

QueryBuilder 帮助您使用纯PHP代码生成SQL查询。使用此包的好处是

创建更快、更好、更精确的SQL查询，而不出现任何语法错误。
即使您是专家，也能克服SQL的复杂性。
防止您的数据遭受如SQL注入等安全风险。

如果您熟悉著名的PHP框架 Laravel，您会非常喜欢这个包，因为您不需要学习如何使用此包。

安装

通过下载.zip文件

下载
解压zip文件
将文件夹 querybuilder 复制到 koolreport 文件夹，使其看起来如下

koolreport
├── core
├── querybuilder

通过composer

composer require koolreport/querybuilder

文档

为不同的数据库系统生成兼容的SQL查询

QueryBuilder 包支持 MySQL、PostgreSQL、SQLServer 查询类型。

MySQL查询

可以在 MySQL::type() 函数中使用您的查询，以获取字符串形式的SQL或使用查询的 toMySQL()。

$this->src('mysql_database')->query(MySQL::type(
    DB::table('orders')
))

$this->src('mysql_database')->query(
    DB::table('orders')->toMySQL()
)

PostgreSQL查询

可以在 PostgreSQL::type() 函数中使用您的查询，以获取字符串形式的SQL或使用查询的 toPostgreSQL()。

$this->src('mysql_database')->query(PostgreSQL::type(
    DB::table('orders')
))

$this->src('postgresql_database')->query(
    DB::table('orders')->toPostgreSQL()
)

SQLServer查询

可以在 SQLServer::type() 函数中使用您的查询，以获取字符串形式的SQL或使用查询的 toSQLServer()。

$this->src('sqlserver_database')->query(SQLServer::type(
    DB::table('orders')
))

$this->src('sqlserver_database')->query(
    DB::table('orders')->toSQLServer()
)

参数化查询和参数（版本 >= 3.0.0）

当您使用来自不受信任来源（例如，用户输入）的数据构建查询构建器时，直接使用查询构建器生成的查询是危险的，因为可能存在SQL注入攻击的风险。在这些情况下，建议获取查询构建器生成的参数化查询及其参数，并使用它们来获取数据。

$querybuilder = DB::...;

$queryWithParams = $querybuilder->toMySQL(["useSQLParams" => "name"]); // or "useSQLParams" => "question mark"
$params = $querybuilder->getSQLParams();

设置模式

出于安全和身份验证的原因，用户可以为查询构建器设置模式，以便其生成的查询中只包含那些模式中的表和字段。

$querybuilder = DB::...;

$querybuilder->setSchemas(array(
    "salesSchema" => array(
        "tables" => array(
            "customers"=>array(
                "customerNumber"=>array(
                    "alias"=>"Customer Number",
                ),
                "customerName"=>array(
                    "alias"=>"Customer Name",
                ),
            ),
            "orders"=>array(
                "orderNumber"=>array(
                    "alias"=>"Order Number"
                ),
                "orderDate"=>array(
                    "alias"=>"Order Date",
                    "type" => "datetime"
                ),
                "orderMonth" => [
                    "expression" => "month(orderDate)",
                ]
            ),
            ...
        ),
    ),
    ...
));

检索结果

从表中检索所有行

您可以使用 DB 门面的 table 方法开始查询。该 table 方法返回给定表的流畅查询构建器实例，允许您将更多约束添加到查询中。


use \koolreport\querybuilder\DB;
use \koolreport\querybuilder\MySQL;

class MyReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "automaker"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=automaker",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        );
    }
    function setup()
    {
        $this->src('automaker')->query(MySQL::type(
            DB::table("payments") // Equivalent to : "SELECT * FROM payments"
        ))
        ->pipe($this->dataStore('payments'));
    }
}

检索单行

如果您只需要从数据库表检索一行，您可以使用 first 方法。

DB::table('users')->where('name', 'John')->first()

// Equivalent: "SELECT * FROM users WHERE `name`='John' LIMIT 1"

聚合

查询构建器还提供了各种聚合方法，如 count、max、min、avg 和 sum。您可以在构建查询后调用任何这些方法。

DB::table('orders')->groupBy('country')->sum('amount')

DB::table('orders')->count()

DB::table('customers')->groupBy('state')
    ->avg('income')->alias('avgIncome')

子查询表

QueryBuilder 支持创建子查询。这意味着您可以从由另一个查询生成的表中查询。

DB::table([
    'orders',
    't'=>function($query){
        $query->select('name','age')->from('customers');
    }]
)->...

上述将生成

SELECT *
FROM orders, (SELECT name,age FROM customer) t

选择

指定选择子句

当然，您不一定总是想从数据库表中选择所有列。使用 select 方法，您可以指定查询的定制 select 子句。

DB::table('users')->select('name', 'email')

要更改列名，您可以使用 alias 函数

DB::table('users')
    ->select('customerName')->alias('name')
    ->addSelect('customerAge')->alias('age')

distinct 方法允许您强制查询返回不同的结果

DB::table('users')->distinct()

如果您已经有一个查询构建器实例，并且希望向其现有的选择子句中添加列，您可以使用 addSelect 方法，或者简单连续使用 select 方法。

DB::table('users')->select('name')->addSelect('age')

原始表达式

有时您可能需要在查询中使用原始表达式。

selectRaw

selectRaw 方法可以用来创建原始选择。此方法接受一个可选的绑定数组作为其第二个参数。

DB::table('orders')->selectRaw('price * ? as price_with_tax', [1.0825])

whereRaw / orWhereRaw

whereRaw 和 orWhereRaw 方法可以用来在查询中注入原始的 where 子句。这些方法接受一个可选的绑定数组作为它们的第二个参数。

DB::table('orders')->whereRaw('price > IF(state = "TX", ?, 100)', [200])

havingRaw / orHavingRaw

havingRaw 和 orHavingRaw 方法可以用来将一个原始字符串设置为 having 子句的值。

DB::table('orders')
    ->select('department')
    ->sum('price')->alias('total_sales')
    ->groupBy('department')
    ->havingRaw('SUM(price) > 2500')

orderByRaw

orderByRaw 方法可以用来将一个原始字符串设置为 order by 子句的值。

DB::table('orders')
    ->orderByRaw('updated_at - created_at DESC')

连接

内部连接子句

查询构建器也可以用来编写连接语句。要执行基本的 "内部连接"，您可以使用 join 方法或在查询构建器实例上使用 innerJoin。传递给 join 方法的第一个参数是需要连接的表名，而其余参数指定了连接的列约束。当然，如您所见，您可以在单个查询中将多个表连接起来。

DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')

leftJoin/rightJoin/outerJoin

DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')

crossJoin

要执行 "交叉连接"，请使用 crossJoin 方法，并指定要交叉连接的表名。交叉连接会在第一个表和连接的表之间生成笛卡尔积。

DB::table('sizes')
    ->crossJoin('colours')

高级连接子句

您还可以指定更高级的连接子句。要开始，请将 Closure 作为 join 方法的第二个参数传递。此 Closure 将接收一个 JoinClause 对象，它允许您指定连接子句的约束。

DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
    })

如果您想在连接上使用类似 "where" 的子句，您可以在连接上使用 where 和 orWhere 方法。与比较两个列不同，这些方法将列与一个值进行比较。

DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
    })

联合

查询构建器还提供了一个快速将两个查询 "联合" 在一起的方法。例如，您可能创建一个初始查询，然后使用联合方法将其与第二个查询 union。

DB::table('users')->whereNull('first_name')->union(
    DB::table('users')->whereNull('last_name')
);

Where 子句

简单的 Where 子句

您可以在查询构建器实例上使用 where 方法向查询添加 where 子句。对 where 的最基本调用需要三个参数。第一个参数是列名。第二个参数是一个操作符，可以是数据库支持的操作符中的任何一个。最后，第三个参数是要与列进行比较的值。

例如，这是一个验证 "votes" 列的值等于 100 的查询。

DB::table('users')->where('votes', '=', 100)

为了方便，如果您只想验证列是否等于给定的值，您可以将该值直接作为第二个参数传递给 where 方法。

DB::table('users')->where('votes', 100)

当然，您可以在编写 where 子句时使用各种其他操作符。

DB::table('users')->where('votes', '>=', 100)

DB::table('users')->where('votes', '<>', 100)

DB::table('users')->where('name', 'like', 'T%')

您还可以将条件数组传递给 where 函数。

DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])

Or 语句

您还可以将 where 约束链接起来，以及向查询添加 or 子句。orWhere 方法接受与 where 方法相同的参数。

DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere('name', 'John')

Where 中的括号

您可以使用 whereOpenBracket 和 whereCloseBracket 方法向 where 子句中添加开括号和闭括号。

DB::table('users')
    ->where(...)
    ->whereOpenBracket()
    ->where(...)
    ->whereCloseBracket()

这些括号适用于多级 where 条件。

附加 Where 子句

whereBetween

whereBetween 方法验证列的值是否介于两个值之间。

DB::table('users')->whereBetween('votes', [1, 100])

whereNotBetween

《whereNotBetween》方法用于验证某列的值是否在两个值之外。

DB::table('users')->whereNotBetween('votes', [1, 100])

whereIn / whereNotIn

《whereIn》方法用于验证给定列的值是否包含在给定的数组中。

DB::table('users')->whereIn('id', [1, 2, 3])

《whereNotIn》方法用于验证给定列的值是否不包含在给定的数组中。

DB::table('users')->whereNotIn('id', [1, 2, 3])

whereNull / whereNotNull

《whereNull》方法用于验证给定列的值是否为NULL。

DB::table('users')->whereNull('updated_at')

《whereNotNull》方法用于验证列的值不是NULL。

DB::table('users')->whereNotNull('updated_at')

whereDate / whereMonth / whereDay / whereYear / whereTime

《whereDate》方法可以用来比较列的值与日期。

DB::table('users')->whereDate('created_at', '2016-12-31')

《whereMonth》方法可以用来比较列的值与年份中的特定月份。

DB::table('users')->whereMonth('created_at', '12')

《whereDay》方法可以用来比较列的值与月份中的特定一天。

DB::table('users')->whereDay('created_at', '31')

《whereYear》方法可以用来比较列的值与特定的年份。

DB::table('users')->whereYear('created_at', '2016')

《whereTime》方法可以用来比较列的值与特定的时间。

DB::table('users')->whereTime('created_at', '=', '11:20')

whereColumn

《whereColumn》方法可以用来验证两列是否相等。

DB::table('users')->whereColumn('first_name', 'last_name')

您还可以向该方法传递一个比较运算符。

DB::table('users')->whereColumn('updated_at', '>', 'created_at')

《whereColumn》方法还可以传递一个包含多个条件的数组。这些条件将使用and运算符连接。

DB::table('users')
    ->whereColumn([
        ['first_name', '=', 'last_name'],
        ['updated_at', '>', 'created_at']
])

参数分组

有时您可能需要创建更复杂的where子句，例如"where exists"子句或嵌套参数分组。KoolReport查询构建器也可以处理这些。要开始，让我们看看括号内分组约束的例子。

DB::table('users')
    ->where('name', '=', 'John')
    ->orWhere(function ($query) {
        $query->where('votes', '>', 100)
                ->where('title', '<>', 'Admin');
    })

如您所见，将Closure传递给orWhere方法指示查询构建器开始一个约束组。这个Closure将接收一个查询构建器实例，您可以使用它来设置应包含在括号组内的约束。上面的例子将生成以下SQL。

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

Where Exists子句

《whereExists》方法允许您编写where exists SQL子句。该whereExists方法接受一个Closure参数，该参数将接收一个查询构建器实例，允许您定义应放在"exists"子句内的查询。

DB::table('users')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id');
            })

上面的查询将生成以下SQL。

select * from users
where exists (
    select 1 from orders where orders.user_id = users.id
)

JSON Where子句

《QueryBuilder》包也支持在提供对JSON列类型支持的数据库上查询JSON列类型。目前包括MySQL 5.7和PostgreSQL。要查询JSON列，请使用->运算符。

DB::table('users')
                ->where('options->language', 'en')

DB::table('users')
                ->where('preferences->dining->meal', 'salad')

排序、分组、限制、偏移量

orderBy

《orderBy》方法允许您根据给定的列排序查询结果。该orderBy方法的第一个参数应该是您希望排序的列，而第二个参数控制排序方向，可以是asc或desc。

DB::table('users')
                ->orderBy('name', 'desc')

latest / oldest

《latest》和《oldest》方法允许您轻松按日期排序结果。默认情况下，结果将按created_at列排序。或者，您也可以传递您希望排序的列名。

DB::table('users')
        ->latest()
        ->first()

groupBy / having

《groupBy》和《having》方法可用于分组查询结果。《having》方法的签名与《where》方法类似。

DB::table('users')
        ->groupBy('account_id')
        ->having('account_id', '>', 100)

您可以向《groupBy》方法传递多个参数来按多列分组。

DB::table('users')
        ->groupBy('first_name', 'status')
        ->having('account_id', '>', 100)

有关更高级的《having》语句，请参阅《havingRaw》方法。

skip / take

要限制查询返回的结果数量，或跳过查询中的给定数量的结果，您可以使用《skip》和《take》方法。

DB::table('users')->skip(10)->take(5)

或者，您可以使用《limit》和《offset》方法。

DB::table('users')
        ->offset(10)
        ->limit(5)

条件子句

when

有时你可能希望只有当其他条件为真时，某些条款才适用于查询。例如，你可能只想在给定的输入值出现在传入请求上时应用一个 where 语句。你可以使用 when 方法来实现这一点

$role = $_POST['role'];

DB::table('users')
    ->when($role, function ($query) use ($role) {
        return $query->where('role_id', $role);
    })

when 方法仅在第一个参数为 true 时执行给定的闭包。如果第一个参数为 false，则不会执行闭包。

你可以将另一个闭包作为 when 方法的第三个参数。如果第一个参数评估为 false，则将执行该闭包。为了说明如何使用此功能，我们将使用它来配置查询的默认排序

$sortBy = null;

$users = DB::table('users')
->when($sortBy, 
    function ($query) use ($sortBy) {
        return $query->orderBy($sortBy);
    },
    function ($query) {
        return $query->orderBy('name');
    }
)

分支

有时你可能需要在参数具有特定值时将条款应用于查询，你可以使用 branch 语句。

你需要将 Closure 的列表作为第二个参数传递给 branch 函数。

$user_role = "admin"; //"registered_user","public"

DB::table('orders')
    ->branch($user_role,[
        "admin"=>function($query){
            $query->whereIn('state',['TX','NY','DC'])
        },
        "registered_user"=>function($query){
            $query->whereIn('state',['TX','NY'])
        },
        "public"=>function($query){
            $query->where('state','TX')
        },        
    ])

插入

虽然在使用 KoolReport 时，你大部分时间都会处理 select 语句，但查询构建器还提供了一个 insert 方法，用于将记录插入到数据库表中。该 insert 方法接受一个包含列名称和值的数组

DB::table('users')->insert(
    ['email' => 'john@example.com', 'votes' => 0]
);

你甚至可以使用单个调用到 insert 的方式插入多个记录到表中，通过传递一个数组的数组。每个数组代表要插入到表中的一行

DB::table('users')->insert([
    ['email' => 'taylor@example.com', 'votes' => 0],
    ['email' => 'dayle@example.com', 'votes' => 0]
]);

更新

虽然在使用 KoolReport 时，你大部分时间都会处理 select 语句，但查询构建器也可以使用 update 方法来更新现有记录。与 insert 方法类似，update 方法接受一个包含要更新的列的列和值对的数组。你可以使用 where 子句来限制 update 查询

DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 1]);

增量 & 减量

查询构建器还提供了一些方便的方法来增加或减少给定列的值。这是一个快捷方式，与手动编写 update 语句相比，提供了一种更简洁、更具有表现力的接口。

这两个方法都至少接受一个参数：要修改的列。你可以可选地传递第二个参数来控制列应该增加或减少的量

DB::table('users')->increment('votes')

DB::table('users')->increment('votes', 5)

DB::table('users')->decrement('votes')

DB::table('users')->decrement('votes', 5)

删除

虽然在使用 KoolReport 时，你大部分时间都会处理 select 语句，但查询构建器也可以通过 delete 方法从表中删除记录。你可以在调用 delete 方法之前添加 where 子句来限制 delete 语句

DB::table('users')->delete()

DB::table('users')->where('votes', '>', 100)->delete()

如果你希望截断整个表，这将删除所有行并将自动递增的 ID 重置为零，你可以使用 truncate 方法

DB::table('users')->truncate();

悲观锁定

查询构建器还包含了一些函数，可以帮助你在 select 语句上执行“悲观锁定”。要使用“共享锁定”运行语句，你可以在查询上使用 sharedLock 方法。共享锁定阻止所选行在你事务提交之前被修改

DB::table('users')->where('votes', '>', 100)->sharedLock()

或者，你可以使用 lockForUpdate 方法。“for update”锁定阻止行被修改或被其他共享锁定选中

DB::table('users')->where('votes', '>', 100)->lockForUpdate()

支持

如果您需要支持，请使用我们的论坛，这样其他人也可以从中受益。如果支持请求需要保密，您可以通过发送电子邮件到 support@koolreport.com 来联系我们。

koolreport / querybuilder

维护者

详细信息