mini\Database\PartialQuery::withCTE() Method

Signature

public function withCTE(string $name, self $query): self

Parameters

Returns

self

Documentation

Add a PartialQuery as a named CTE (Common Table Expression)

Allows composing queries from other PartialQueries. The CTE can then be referenced by name in the main query's SQL.

When adding a CTE with the same name as an existing CTE, the existing CTE is renamed and the new CTE wraps it, creating a filter chain:

$q = db()->query('SELECT * FROM users WHERE age >= 18');
$q = $q->withCTE('users', db()->query('SELECT * FROM users WHERE age <= 67'));
$q = $q->withCTE('users', db()->query('SELECT * FROM users WHERE gender = "male"'));

Produces:

WITH _cte_1 AS (SELECT * FROM users WHERE age <= 67),
     users AS (SELECT * FROM _cte_1 WHERE gender = "male")
SELECT * FROM users WHERE age >= 18

Each new CTE wraps the previous one, chaining filters together. The baseSql always references the outermost CTE.

Source

src/Database/PartialQuery.php:986-1051