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 #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.