Advanced queries

The query builder covered in ModelCriteria & Query handles joins, filters, ordering, and pagination. This page covers the SQL constructs that go beyond that: recursive queries, ranking and running totals, combining result sets, correlated subqueries, and reading the plan the database will actually use.

Everything here is available on plain Criteria as well as on ModelCriteria and generated Query classes. Where a ModelCriteria-level convenience exists, it’s shown alongside the Criteria primitive.

Common table expressions

withCte() prefixes a WITH name AS (<query>) clause onto a query:

use Propulsion\Query\Criteria;

$recentReviews = new Criteria();
$recentReviews->setPrimaryTableName('review');
$recentReviews->addSelectColumn('review.BOOK_ID');
$recentReviews->add('review.CREATED_AT', $cutoff, Criteria::GREATER_EQUAL);

$books = BookQuery::create()
    ->withCte('recent_reviews', $recentReviews)
    ->where('recent_reviews.BOOK_ID = book.ID')
    ->find();

A CTE name is resolved purely by string identity, exactly the way a real table name is. There is no separate “reference a CTE” API — setPrimaryTableName('recent_reviews'), addJoin(), and a plain 'recent_reviews.BOOK_ID' string passed to where() or addSelectColumn() all work against a CTE name as-is.

Multiple withCte() calls are cumulative and chainable, producing WITH a AS (...), b AS (...) SELECT ....

The useCteQuery() convenience

For the common non-recursive case, ModelCriteria::useCteQuery() builds the subquery from a model name in a closure, mirroring withQuery()’s style:

$books = BookQuery::create()
    ->useCteQuery('recent_reviews', 'Review', function ($sub) use ($cutoff) {
        $sub->addSelectColumn(ReviewPeer::BOOK_ID);
        $sub->add(ReviewPeer::CREATED_AT, $cutoff, Criteria::GREATER_EQUAL);
    })
    ->where('recent_reviews.BOOK_ID = book.ID')
    ->find();

Recursive CTEs

Pass $recursive = true and build the query as an anchor branch unionAll()’d with a branch that references the CTE by name:

use Propulsion\Query\Criteria;

// Anchor: the root categories.
$tree = new Criteria();
$tree->setPrimaryTableName('category');
$tree->addSelectColumn('category.ID');
$tree->addSelectColumn('category.PARENT_ID');
$tree->add('category.PARENT_ID', null, Criteria::ISNULL);

// Recursive member: children of anything already in the CTE.
$children = new Criteria();
$children->setPrimaryTableName('category');
$children->addSelectColumn('category.ID');
$children->addSelectColumn('category.PARENT_ID');
$children->addJoin('category.PARENT_ID', 'category_tree.ID', Criteria::INNER_JOIN);

$tree->unionAll($children);

$categories = CategoryQuery::create()
    ->withCte('category_tree', $tree, ['ID', 'PARENT_ID'], true)
    ->where('category.ID IN (SELECT ID FROM category_tree)')
    ->find();

The $columns list is required for a recursive CTE and withCte() throws without it. Two reasons: the self-reference inside the recursive branch needs the CTE’s column names to exist before that branch can be built at all, and Oracle cannot infer them from the anchor branch’s SELECT list the way PostgreSQL, MySQL/MariaDB, and SQLite can. Requiring it uniformly keeps the same schema portable.

useCteQuery() deliberately doesn’t cover the recursive case — a self-referencing UNION ALL branch isn’t expressible as a single model’s worth of subquery. Build it as a plain Criteria and pass it to withCte() directly.

Note

Propulsion emits the literal RECURSIVE keyword where the platform requires it (PostgreSQL, MySQL/MariaDB, SQLite) and omits it where it doesn’t (Oracle) or where it’s rejected outright (MSSQL). You don’t need to branch on this.

A CTE’s own body may itself carry set operations, and CTEs compose with them freely.

Window functions

Propulsion\Query\WindowExpression is a fluent builder for <function>(...) OVER (PARTITION BY ... ORDER BY ... <frame>). Pass the result to withColumn():

use Propulsion\Query\WindowExpression;

$books = BookQuery::create()
    ->withColumn(
        WindowExpression::rowNumber()
            ->partitionBy('Book.PublisherId')
            ->orderBy('Book.Price', 'DESC'),
        'PriceRank'
    )
    ->find();

// ROW_NUMBER() OVER (PARTITION BY book.PUBLISHER_ID ORDER BY book.PRICE DESC) AS PriceRank
foreach ($books as $book) {
    echo $book->getPriceRank();
}

Because the expression goes through withColumn(), its column arguments get the same Model.Column name-replacement treatment any other withColumn() clause does — you can write 'Book.PublisherId' and have it resolved to book.PUBLISHER_ID.

Available functions

Ranking and positional: rowNumber(), rank(), denseRank(), percentRank(), ntile(int $buckets), lag(string $column, int $offset = 1), lead(string $column, int $offset = 1), firstValue(string $column), lastValue(string $column).

Aggregates: sum(), avg(), count(string $column = '*'), min(), max().

Anything else: raw(string $function) takes the function call as a SQL fragment, e.g. WindowExpression::raw('PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY book.PRICE)').

Partitioning, ordering, and frames

$running = WindowExpression::sum('Book.Price')
    ->partitionBy('Book.PublisherId')
    ->orderBy('Book.PublishedAt')
    ->rowsBetween(WindowExpression::UNBOUNDED_PRECEDING, WindowExpression::CURRENT_ROW);

// SUM(book.PRICE) OVER (PARTITION BY book.PUBLISHER_ID ORDER BY book.PUBLISHED_AT
//                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

partitionBy() and orderBy() are both cumulative — repeated calls append rather than replace. rowsBetween() and rangeBetween() are mutually exclusive; the later call wins. Frame bounds are plain strings, with WindowExpression::UNBOUNDED_PRECEDING, UNBOUNDED_FOLLOWING, and CURRENT_ROW provided as constants, plus WindowExpression::preceding(int $n) and following(int $n) for the numeric forms.

Caution

A window function’s arguments and its OVER clause are SQL fragments, not bound parameters — the same trust model ColumnExpression::raw() has. This is a deliberate constraint of what the construct is: a window function’s argument list and OVER clause can only ever reference columns and expressions, never literal values you’d want parameterized. Never interpolate user input into one.

Reading inside a JSON column

Propulsion\Query\JsonExpression extracts a value from a JSON column, and whereJsonPath() filters on one:

use Propulsion\Query\JsonExpression;

$books = BookQuery::create()
    ->withColumn(JsonExpression::text('Book.Meta', '$.author.name'), 'AuthorName')
    ->whereJsonPath('Book.Meta', '$.published', true)
    ->find();

foreach ($books as $book) {
    echo $book->getAuthorName();
}

whereJsonPath(string $column, string $path, mixed $value = null, string $comparison = Criteria::EQUAL) takes the same comparison constants as filterBy*(), so Criteria::IN with an array works, and a null value with the default comparison becomes IS NULL rather than a never-true = NULL.

text() versus json()

This is the distinction that decides whether a comparison matches at all:

• JsonExpression::text($column, $path) yields the SQL value — a scalar, unquoted, directly comparable against a bound parameter. What you want almost always, and what whereJsonPath() always uses.
• JsonExpression::json($column, $path) yields the JSON value: an object or array stays a document instead of collapsing to a scalar, and a string keeps its quotes. Use it to pull out a nested structure, never to compare against a scalar — json() on a string column compared against 'foo' is comparing against "foo", quotes included, and never matches.

Both default to '$', the whole document.

One consequence of extracting as text: the comparison is textual on every platform, so '$.count' against 5 matches the string "5". That is a property of JSON extraction rather than of Propulsion — the platforms disagree about implicit casts here, and picking one would make the same query behave differently per database. Cast explicitly in a where() clause when you need numeric ordering.

One path syntax, five platforms

The JSONPath-ish spelling — $.author.name, $.tags[0] — is what MySQL/MariaDB, SQLite, MSSQL and Oracle take natively. PostgreSQL’s operators want a text array, so the path is parsed into segments and re-rendered for it ('{author,name}') rather than pasted through. A malformed path is an error where you wrote it, not a syntax error from the server three layers down.

The supported subset is deliberately small: object keys and non-negative integer indexes. No wildcards, slices, filter expressions or recursive descent — the platforms disagree about all of those and several support none, so accepting one would mean emitting a query that works on one database and not the next. Containment and existence predicates (@>, ?, JSON_CONTAINS, JSON_EXISTS) are likewise absent for lack of a common shape; reach for a raw clause if you need one on a specific platform.

All five platforms support extraction. Propulsion::getDB()->supportsJsonPath() is there to branch on, and an unsupported platform throws rather than emitting something that cannot run.

Vector distance queries

Propulsion\Query\VectorExpression builds a distance expression against a VECTOR column, which is what turns a stored embedding into a nearest-neighbour query:

use Propulsion\Query\VectorExpression;

$nearest = DocumentQuery::create()
    ->withColumn(VectorExpression::l2Distance('Document.Embedding', $needle), 'Distance')
    ->orderBy('Distance')
    ->limit(10)
    ->find();

$needle is a PHP array<float> (or an already-formatted literal). Four metrics: l2Distance(), cosineDistance(), innerProduct() and l1Distance(). Inner product is negated, as pgvector’s <#> is, so “smaller is closer” holds for every metric — which is what lets an index serve the ordering.

Platform support is uneven, and asking for a metric a platform lacks throws with a message naming it rather than quietly resolving to a different one:

Platform	Metrics	SQL
PostgreSQL + pgvector	all four	infix operators <->, <=>, <#>, <+> (L1 needs pgvector 0.7+)
MariaDB 11.7+	L2, cosine	VEC_DISTANCE_EUCLIDEAN(), VEC_DISTANCE_COSINE()
MySQL 9	none	DISTANCE() is a HeatWave feature, not part of the community server
Everything else	none	the column is emulated as text

The query vector is a literal, not a bound parameter

A deliberate exception to how this query builder treats every other value, and it is what makes an ANN index scan possible — the planner has to see the operand. It is safe for the same reason it is necessary: the operand is a fixed-length list of numbers that VectorExpression formats itself from PHP floats, rejecting non-numeric input outright rather than escaping it. There is no string for anything to be routed through.

To have the index actually serve the query on PostgreSQL, give the index column an operator class — pgvector’s HNSW and IVFFlat access methods refuse to build without one:

<index name="doc_embedding_idx" indexType="hnsw">
  <index-column name="embedding" opclass="vector_l2_ops" />
</index>

See opclass in the schema reference.

Set operations

union(), unionAll(), intersect(), and except() combine two complete queries:

$expensive = BookQuery::create()->filterByPrice(100, Criteria::GREATER_THAN);
$recent = BookQuery::create()->filterByPublishedAt($cutoff, Criteria::GREATER_EQUAL);

$books = $expensive
    ->union($recent)
    ->orderByTitle()
    ->limit(20)
    ->find();

// (SELECT ... FROM book WHERE book.PRICE > ?) UNION (SELECT ... FROM book WHERE book.PUBLISHED_AT >= ?)
// ORDER BY book.TITLE LIMIT 20

union() deduplicates rows; unionAll() keeps duplicates and is cheaper (no dedup pass). intersect() returns rows present in both result sets, except() rows in the first that aren’t in the second.

orderBy(), limit(), and offset() on the outer query apply to the combined result, not to its own branch alone — the outer query’s own branch is built from an internal clone with those cleared first, and they’re re-applied to the whole combined statement. GROUP BY and HAVING belong to a single branch and stay with the query they were set on, since SQL doesn’t allow them on a raw UNION result.

Each $other branch keeps whatever ORDER BY, LIMIT, or lock it carries as part of its own parenthesized branch. Most platforms accept this, but it isn’t exhaustively verified beyond PostgreSQL.

Set operations are chainable and composable — $other may itself already carry set operations.

Oracle

Combining limit() with set operations on Oracle specifically is not reliable when BasePeer::needsSelectAliases() applies: Oracle’s applyLimit() locates the string FROM to rewrite the query, which can misfire against a multi-branch combined statement. Paginate the branches individually, or apply pagination in PHP, if you need both on Oracle.

EXISTS and IN subquery filters

addSelectQuery() nests a subquery in the FROM clause. These two nest one in the WHERE clause instead, as a correlated or uncorrelated filter.

EXISTS

$books = BookQuery::create()
    ->useExistsQuery('Review', function ($sub) {
        $sub->where('review.BOOK_ID = book.ID');
    })
    ->find();

// SELECT ... FROM book WHERE EXISTS (SELECT 1 FROM review WHERE review.BOOK_ID = book.ID)

Correlating the subquery to the parent is your job. Unlike useQuery()/withQuery(), there’s no relation lookup and no automatic join, so $modelName doesn’t need a declared relation to the outer query at all. A raw, unbound where() clause referencing the parent’s table or alias works for the correlation: with no column to bind a value to and no ? placeholder, where() falls through to a literal expression.

If the callback adds no select column, the subquery defaults to SELECT 1 — EXISTS never cares what a subquery selects, only whether it returns rows.

useNotExistsQuery() is the negated form. On plain Criteria, the primitive is addExistsQuery(Criteria $subQuery, bool $negate = false).

IN

$books = BookQuery::create()
    ->useInQuery('Id', 'Review', function ($sub) {
        $sub->addSelectColumn(ReviewPeer::BOOK_ID);
        $sub->add(ReviewPeer::RECOMMENDED, true);
    })
    ->find();

// SELECT ... FROM book
// WHERE book.ID IN (SELECT review.BOOK_ID FROM review WHERE review.RECOMMENDED = ?)

The first argument is the phpName of the column on this query’s model to filter; the subquery must select exactly the one column being matched against, set explicitly with addSelectColumn(). Unlike useExistsQuery(), the subquery doesn’t need to correlate itself to the outer query.

Pass $negate = true for NOT IN. On plain Criteria, the primitive is addInQuery(string $column, Criteria $subQuery, bool $negate = false) and $column is a fully-qualified name.

Note

Inside an IN subquery, prefer add() with the column’s Peer constant over where() for value-bound conditions. where()’s pseudo-SQL clause syntax resolves column phpNames against the subquery’s own model, which reads confusingly here. where() is still the right tool for a raw correlation condition with no bound value.

Inspecting a query plan

explain() returns the platform’s execution plan for the query instead of its rows:

$plan = BookQuery::create()
    ->filterByTitle('War And Peace')
    ->joinAuthor()
    ->explain();

print_r($plan);

It builds the exact same SELECT SQL find() would — the same joins, WHERE, ORDER BY, and LIMIT, through the same internal seam — so the plan reflects the query as it would really run, not a reconstruction of it.

Pass $analyze = true for PostgreSQL’s EXPLAIN ANALYZE, which actually executes the query while collecting timing:

$plan = BookQuery::create()->filterByTitle('War And Peace')->explain(true);

$analyze is accepted but ignored where the platform has no such distinction: SQLite’s EXPLAIN QUERY PLAN never executes the query either way, and MySQL’s own EXPLAIN ANALYZE changes the output shape to a text execution tree rather than adding detail to the same tabular result, so plain EXPLAIN is used there.

The return value is a list of associative arrays, one per plan row, shaped however that platform’s EXPLAIN output is shaped. This is not normalized across platforms — don’t write code that assumes fixed keys if it has to run against more than one engine.

explain() deliberately bypasses the query result cache: a plan is diagnostic, and serving a stale one from an unrelated earlier call would be useless.

Caution

explain() is supported on PostgreSQL, MySQL/MariaDB, and SQLite. It throws a PropulsionException on MSSQL and Oracle, neither of which can produce a plan by rewriting a single statement — MSSQL needs SET SHOWPLAN_ALL ON toggled around the query as separate statements, and Oracle needs EXPLAIN PLAN FOR <sql> followed by a second query against PLAN_TABLE. See Supported databases.

Related

• Bulk writes, upserts, and returning rows — the write-side counterparts: upsert, bulk load, computed UPDATE values, and RETURNING.
• Locking — FOR UPDATE / FOR SHARE on a query, and the optimistic-lock behavior.
• Query result cache — caching a query’s rows, and caching its compiled SQL string.
• Supported databases — which of the above works where.