1.2.3. Common Table Expressions

Every Statement supports Common Table Expressions. To add one or more CTEs to a Statement, call its with*() methods:

// WITH cte_1 AS (SELECT ...)
$insert->with('cte_1', "SELECT ...")

// WITH cte_2 (foo, bar, baz) AS (SELECT ...)
$update->withColumns('cte_2', ['foo', 'bar', 'baz'], "SELECT ...");

Note:

You can use any kind of Statement as a CTE, not just a SELECT.

To enable or disable recursive CTEs, call withRecursive():

// enable
$select->withRecursive();

// disable
$select->withRecursive(false);

Further, you can pass a Statement object instead of a string:

$cteSelect = Select::new('sqlite');
$cteSelect->...;

$delete->with('cte_3', $cteSelect);

Note:

Any values bound to the CTE Statement will be transferred to the main Statement.