梦游者 / cte-builder
用于编程方式构建 CTE SQL 查询(WITH 查询)的库
Requires
- php: >=8.1
- ext-json: *
- beberlei/assert: ^3.3
- doctrine/dbal: ^3.0|^4.0
- somnambulist/collection: ^5.3
Requires (Dev)
- pagerfanta/pagerfanta: ^4.3
- phpunit/phpunit: ^10.5
- somnambulist/domain: ^6.0
- symfony/var-dumper: ^6.4
- vimeo/psalm: ^5.22
Suggests
- pagerfanta/pagerfanta: Paginate ExpressionBuilder results
README
通过 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
来运行测试套件。