Repository files navigation

Provides a programmatic interface via Doctrine DBAL Query Builder for creating CommonTable Expressions (WITH clauses). Includes support for CTE dependencies and debugging.A CTE allows extracting a sub-select or derived query that can be executed separatelyto the main query, but then referenced again. Depending on the database server beingused, there may be significant performance advantages to this approach, for example:in Postgres a CTE is evaluated once and the same result used no matter how many timesit is referenced.

CTEs can also be used to pre-generate content that is needed multiple times, ensuringthat any significant computational cost is only incurred once.

Be sure to read up on CTEs and WITH clauses for your chosen database server.

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

Install using composer, or checkout / pull the files from github.com.

• composer require somnambulist/cte-builder

Note: 3.2.0 changes the return type on the main execute method for compatibility with doctrine/dbal.This method now returns theDoctrine\DBAL\Result object and not aStatement object.

CTE Builder consists of the ExpressionBuilder and the Expression. Expressions are createdeither directly and bound to the builder, or via the builder. The builder requires:

• DBAL Connection

If using Symfony the default configured connection can be used.

<?phpuseSomnambulist\Components\CTEBuilder\ExpressionBuilder;$eb =newExpressionBuilder($connection);$expr =$eb->createExpression('first_clause');$result =$eb->select('field','another field')->from('table_or_cte')->execute();

Each expression is its own independent query builder instance using the same connection.Each CTE can be as complex as required.

CTEs can reference other CTEs. When creating the query, use the CTE alias. It helps to useconstants for the aliases to make it easier to keep them in-sync. When referencing otherCTEs it is important to then set that as an explicit dependency of the CTE:

<?phpuseSomnambulist\Components\CTEBuilder\ExpressionBuilder;$eb =newExpressionBuilder($connection);$expr1 =$eb->createExpression('first_clause');$expr1->from('second_clause');$expr2 =$eb->createExpression('second_clause');$expr1->dependsOn('second_clause');

It is very important to keep track of your dependencies as CTEs must be defined beforethey are referenced - they cannot be back-referenced; hence the need to set the dependency.

Alternatively, specify the dependencies when creating the expression:

<?phpuseSomnambulist\Components\CTEBuilder\ExpressionBuilder;$eb =newExpressionBuilder($connection);$expr1 =$eb->createExpression('first_clause','second_clause');$expr1->from('second_clause');

Note: if dependencies are specified at creation they cannot be undone i.e. they arepermanently bound to the expression.

Both the ExpressionBuilder and Expression exposequery() for accessing the QueryBuilderdirectly. Parameters can be bound to both and will be automatically merged together in theExpressionBuilder as needed.

Note: because the CTEs can be re-ordered and all parameters must be collected togetherand passed to the compiled query named-placeholdersMUST be used. If positionalplaceholders are used, the query will almost certainly fail.

Once defined a CTE can be accessed from the ExpressionBuilder either via theget() methodor via a dynamic property accessor:

<?phpuseSomnambulist\Components\CTEBuilder\ExpressionBuilder;$eb =newExpressionBuilder($connection);$eb->createExpression('first_clause');$eb->createExpression('second_clause');$eb->createExpression('third_clause');$eb->third_clause->select();

From v3.3 there is very basic support for UNION / UNION ALL when using Expression objects. This allows moreeasily creating UNIONs on the CTE expressions.

Note: this is very limited and does not support wrapped statements / ORDER BY on the expressions usedin UNIONs. Attempts to add ORDER BY to an expression passed tounion(),unionAll(), oraddUnion()will result in an exception being thrown at query compile time.

To build a union query for a CTE expression, first create the holder expression viacreateExpression()and then callunion() orunionAll() with the expressions to join together. Multiple expressions canbe passed as separate arguments, or a single expression can be added viaaddUnion(). Note that bothunion() andunionAll() will reset the store of union expressions.

For example:

<?phpuseSomnambulist\Components\CTEBuilder\ExpressionBuilder;$eb =newExpressionBuilder($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);

Or add the unions usingaddUnion():

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

Note: you should not use the union methods with recursive CTEs. This will cause unpredictablebehaviour and/or execution errors.

To create a recursive CTE, first create the builder as before and then usecreateRecursiveExpression.This will return aRecursiveExpression instance. It is largely the same as the standardExpressionexcept it provides the additional methods:

• withInitialSelect
• withUniqueRows

withInitialSelect is used to initialise the carry that is used in the following recursive call. Thiscan be simple value e.g.:VALUES(1) orSELECT 1 or a more complex query / query builder instance.If using a query builder instance any parametersMUST be named parameters. The parameters will bemerged into the CTE and the SQL cast to a string.

withUniqueRows (default false) if set totrue will change the UNION ALL to a UNION.

Finally: the standardquery() is for setting up the recursive query itself i.e: the right side ofthe UNION.

Recursive expressions support the same dependencies and calls as Expressions (they inherit all methods).

Note: if your main query requires furtherUNION statements, then you will need to force thequery into the SELECT clause as the underlying DBAL QueryBuilder does not support UNION queries.

Note: as the initial select clause could have no column names, you must specify the names of thefields that will be returned by calling:withFields() and then supplying a list of fields.

See the test cases for some examples of simple queries and then a more complex case adapted from theSQlite documentation.

An adapter is included for Pagerfanta to handle pagination of results. To use it:composer req pagerfanta/pagerfanta, and then create your CTE as normal. To add paginated results,consider this contrived example:

$cte =newExpressionBuilder($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 =newPagerfantaAdapter($cte,function (ExpressionBuilder$qb) {$qb->select('COUNT(*) AS total_results');});$pf =newPagerfanta($paginator);$pf->setMaxPerPage(1)->setCurrentPage(3);foreach ($pfas$result) {dump($result);}

The paginator adapter will clone theExpressionBuilder to apply whatever counting mechanic you needwithout affecting the main query.

If you use Symfony; using the standard Doctrine DBAL connection from your entity manager willautomatically ensure that the main SQL query is automatically profiled. However: the fullycompiled query with substituted parameters can be dumped by passing in a logger instance. Thequery will be logged using debug. This should be done when testing / debugging building acomplex query.

For further insights consider using an application profiler such as:

• Tideways
• BlackFire

For other frameworks; as DBAL is used, hook into the Configuration object and add an SQLlogger instance that can report to your frameworks' profiler.

Run the test suite via:vendor/bin/phpunit.