梦游者/cte-builder

用于编程方式构建 CTE SQL 查询(WITH 查询)的库

3.4.0 2024-03-03 02:46 UTC

This package is auto-updated.

Last update: 2024-09-03 17:27:00 UTC


README

GitHub Actions Build Status Issues License PHP Version Current Version

通过 Doctrine DBAL Query Builder 提供编程接口来创建公共表表达式(WITH 子句)。包括对 CTE 依赖和调试的支持。CTE 允许提取一个子查询或派生查询,该查询可以单独执行,但随后又可以在主查询中引用。根据所使用的数据库服务器,此方法可能具有显著的性能优势,例如:在 Postgres 中,CTE 只计算一次,无论引用多少次,都使用相同的计算结果。

CTE 也可以用来预先生成需要多次使用的内容,确保任何重大的计算成本只发生一次。

请务必查阅您所选择数据库服务器上的 CTE 和 WITH 子句的相关信息。

要求

  • PHP 8.0+
  • doctrine/dbal
  • somnambulist/collection

安装

使用 composer 安装,或从 github.com 检出/拉取文件。

  • composer require somnambulist/cte-builder

注意: 3.2.0 版本更改了主执行方法的返回类型,以与 doctrine/dbal 兼容。现在此方法返回 Doctrine\DBAL\Result 对象,而不是 Statement 对象。

用法

CTE Builder 由 ExpressionBuilder 和 Expression 组成。表达式可以直接创建并绑定到构建器,或者通过构建器创建。构建器需要

  • DBAL 连接

如果使用 Symfony,则可以使用默认配置的连接。

<?php
use Somnambulist\Components\CTEBuilder\ExpressionBuilder;

$eb = new ExpressionBuilder($connection);
$expr = $eb->createExpression('first_clause');

$result = $eb->select('field', 'another field')->from('table_or_cte')->execute();

每个表达式都是其自身的独立查询构建器实例,使用相同的连接。每个 CTE 可以根据需要具有任何复杂度。

CTE 可以引用其他 CTE。在创建查询时,使用 CTE 别名。使用常量作为别名有助于保持一致性。在引用其他 CTE 时,将它们设置为 CTE 的显式依赖项非常重要

<?php
use Somnambulist\Components\CTEBuilder\ExpressionBuilder;

$eb = new ExpressionBuilder($connection);
$expr1 = $eb->createExpression('first_clause');
$expr1->from('second_clause');
$expr2 = $eb->createExpression('second_clause');

$expr1->dependsOn('second_clause');

跟踪依赖关系非常重要,因为 CTE 必须在引用之前定义——它们不能反向引用;因此需要设置依赖项。

或者,在创建表达式时指定依赖项

<?php
use Somnambulist\Components\CTEBuilder\ExpressionBuilder;

$eb = new ExpressionBuilder($connection);
$expr1 = $eb->createExpression('first_clause', 'second_clause');
$expr1->from('second_clause');

注意: 如果在创建时指定了依赖项,则无法撤销,即它们将永久绑定到表达式。

ExpressionBuilder 和 Expression 都公开了 query() 以直接访问 QueryBuilder。参数可以绑定到两者,并将根据需要自动合并到 ExpressionBuilder 中。

注意: 由于 CTE 可以重新排序,并且所有参数都必须收集在一起并传递给编译查询,因此必须使用命名占位符。如果使用位置占位符,查询几乎肯定会失败。

定义后,可以从 ExpressionBuilder 通过 get() 方法或动态属性访问器访问 CTE。

<?php
use Somnambulist\Components\CTEBuilder\ExpressionBuilder;

$eb = new ExpressionBuilder($connection);
$eb->createExpression('first_clause');
$eb->createExpression('second_clause');
$eb->createExpression('third_clause');

$eb->third_clause->select();

CTE 表达式中的 UNION 查询

从 v3.3 版本开始,当使用 Expression 对象时,对 UNION / UNION ALL 提供了非常基本的支持。这允许更轻松地在 CTE 表达式上创建 UNION。

注意:这非常有限,不支持在UNION中使用的表达式的换行语句/ORDER BY。尝试将ORDER BY添加到传递给union()unionAll()addUnion()的表达式中,将在查询编译时抛出异常。

要为CTE表达式构建一个UNION查询,首先通过createExpression()创建持有表达式,然后使用要连接的表达式调用union()unionAll()。可以单独传递多个表达式,或通过addUnion()添加单个表达式。请注意,union()unionAll()都将重置UNION表达式的存储。

例如

<?php
use Somnambulist\Components\CTEBuilder\ExpressionBuilder;

$eb = new ExpressionBuilder($connection);
$expr = $eb->createExpression('unioned_data');
$expr->select('field1', 'field2')->from('some_table');

$otherData = $eb->createDetachedExpression();
$otherData->select('id AS field1', 'name AS field2')->from('some_other_table');
$otherData2 = $eb->createDetachedExpression();
$otherData2->select('id AS field1', 'name AS field2')->from('some_other_table');

$expr->union($otherData, $otherData2);
//$expr->unionAll($otherData, $otherData2);

或使用addUnion()添加UNION

$expr->addUnion($otherData)->addUnion($otherData2);

注意:不应在递归CTE中使用UNION方法。这可能会导致不可预测的行为和/或执行错误。

递归CTE

要创建递归CTE,首先创建构建器,然后使用createRecursiveExpression。这将返回一个RecursiveExpression实例。它基本上与标准Expression相同,但它提供了额外的功能

  • withInitialSelect
  • withUniqueRows

withInitialSelect用于初始化在后续递归调用中使用的携带值。这可以是简单的值,例如:VALUES(1)SELECT 1,或更复杂的查询/查询构建器实例。如果使用查询构建器实例,任何参数必须是命名参数。参数将被合并到CTE中,并转换为字符串。

withUniqueRows(默认为false)如果设置为true,则将UNION ALL更改为UNION。

最后:标准的query()用于设置递归查询本身,即:UNION的右侧。

递归表达式支持与表达式相同的依赖项和调用(它们继承所有方法)。

注意:如果主查询需要进一步的UNION语句,那么您需要将查询强制进入SELECT子句,因为底层的DBAL QueryBuilder不支持UNION查询。

注意:由于初始SELECT子句可能没有列名,您必须通过调用withFields()并提供一个字段列表来指定将返回的字段名。

请参阅测试用例以获取简单查询的一些示例,然后是一个更复杂的案例,该案例改编自SQLite文档。

分页

包含了一个用于处理结果分页的适配器。要使用它:运行composer req pagerfanta/pagerfanta,然后像往常一样创建您的CTE。要添加分页结果,考虑以下虚构示例

$cte = new ExpressionBuilder($conn);
// this is just as example, this is a poor use of CTEs
$users = $cte->createExpression('only_users');
$users->select('*')->from('users')->where('type = :type')->setParameter('type', 'user');

$cte->select('*')->from('only_users');

$paginator = new PagerfantaAdapter($cte, function (ExpressionBuilder $qb) {
    $qb->select('COUNT(*) AS total_results');
});
$pf = new Pagerfanta($paginator);
$pf->setMaxPerPage(1)->setCurrentPage(3);

foreach ($pf as $result) {
    dump($result);
}

分页适配器将克隆ExpressionBuilder以应用所需的任何计数机制,而不会影响主查询。

性能分析

如果您使用Symfony;从实体管理器使用标准的Doctrine DBAL连接将自动确保主SQL查询被自动分析。但是:可以通过传递日志记录器实例将完全编译的查询(带有替换的参数)转储。该查询将以调试方式记录。这应在测试/调试构建复杂查询时进行。

为了获得更多见解,请考虑使用应用程序分析器,如

对于其他框架;由于使用了DBAL,请挂钩到配置对象并添加一个可以报告给框架分析器的SQL记录器实例。

测试套件

通过运行:vendor/bin/phpunit来运行测试套件。