Add WITH clause (Common Table Expressions/CTE) support to SQL Query Builder

[0xPaul]

Feature Request

Q	A
New Feature	yes
RFC	no
BC Break	no

Summary

$queryBuilder->addWith('name', $this->createQueryBuilder()->select(...));

References

• https://www.postgresql.org/docs/13/queries-with.html
• https://dev.mysql.com/doc/refman/8.0/en/with.html
• https://mariadb.com/kb/en/with/

[morozov]

WITH seems to be a special case of a sub-query from the building/syntax standpoint. Linking https://github.com/doctrine/dbal/issues/2305 for reference.

[ianef]

SQL Server also supports CTEs: https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

I'd love to see this included. I have a lot DB views and TVFs that use CTEs and it would be a lot simpler to maintain these as queries in my repository classes. 👍

[derrabus]

Someone has to build this feature so we can include it. Do you want to give it a try?

[ianef]

I’d love to but I have a lot of work at present. Hopefully December will be quieter and I can have a look then.

[Cmrickels]

Has there been any traction on bringing CTEs into query builder ?

[derrabus]

No. Do you want to work on the feature?

[n0099]

An wrapper library for DBAL level native SQL: https://github.com/somnambulist-tech/cte-builder

[ianef]

Thanks @n0099, that looks like an interesting project. Sadly for me that would mean a rewrite of all my queries as it doesn't look like it supports DQL.

[derrabus]

This is the DBAL repository. DQL is out of scope here anyway. The ORM has its own query builder for DQL.

[nio-dtp]

I made a PR for this. Could I have a review on implementation before adding doc and tests ?

[morozov]

@nio-dtp, thank you for the PR. Reviewing the code w/o being able to run it (by means of unit and integration tests) is challenging. Please add the tests.