Common Table Expressions (i.e. CTEs)
Common Table Expressions (CTEs) are powerful SQL concept that allow you to create re-usable temporal result sets, which can be referenced as a table within your SQL. CTEs are available in many common database engines and are available in latest versions of all of the support grammars.
CTEs come in two basic types:
Non-recursive — These are statements that do not reference themselves, in simplified terms they are like a derived table that can be referenced by a user-defined name.
Recursive — Recursive CTEs reference themselves and are generally used for creating hierarchical data—such as creating a parent/child relationship within a table.
While all of the grammars currently support CTEs, there is enough difference between the various databases implementations of CTEs that unless your CTEs are fairly basic, using CTEs within your project will most likely tie your project to a specific database, unless you account for the differences in your code.
However, CTEs are can be extremely useful to solve certain use cases.
To add CTEs to your queries, you have two methods available:
with()
— Allows you to define a non-recursive CTE.withRecursive()
— Allows you to define a recursive CTE.
Some database engines require the recursive
keyword anytime at least one of your CTEs is recursive, but some database engines (e.g. SQL Server and Oracle) do not require the keyword. qb will manage adding the keyword, if necessary. If your query does use recursion you should use the withRecursive()
method to avoid issues when migrating grammars.
with
You can build a CTE using a function:
Alternatively, you can use a QueryBuilder instance instead of a function:
A single query can reference multiple CTEs:
withRecursive
IMPORTANT — The way the SQL in a recursive CTEs are written, using them in your code is likely to lock in you in to a specific database engine, unless you structure your code to build the correct SQL based on the current grammar being used.
Here is an example of building a recursive CTE using SQL Server which would return all parent/child rows and show their generation/level depth: