Skip to content
Quiote

Caching hand-written SQL

Some queries are genuinely easier to write by hand than to express as a Criteria — and those tend to be the expensive ones, which makes them the best candidates for caching. Propulsion::rawQuery() runs hand-written SQL through the whole caching stack: both tiers, invalidation when an ORM write touches one of the tables it reads, admission control, and single-flight.

$books = Propulsion::rawQuery(
        'SELECT b.* FROM book b JOIN author a ON (b.author_id = a.id) WHERE a.country = ?',
        [$country]
    )
    ->dependsOn('book', 'author')
    ->cache(ttl: 300)
    ->hydrate(BookPeer::class);

Nothing about the cache is specific to ModelCriteria: the shared tier stores raw pre-hydration rows, so a caller who can supply the SQL, its parameters, and the tables it reads gets the whole thing.

This is not a general-purpose “run some SQL” helper — for that, use the connection directly (Propulsion::getConnection() returns a PDO instance). rawQuery() exists for the caching.

Building the query

Section titled “Building the query”
Propulsion::rawQuery(string $sql, array $params = [], ?string $dbName = null): RawQuery

Parameters may be positional or named: pass a list for ? placeholders, or a name-keyed map for :name placeholders. $dbName defaults to the default datasource.

MethodPurpose
dependsOn(string ...$tables)Declare the tables this query reads. Required before cache().
cache(?int $ttl = null, bool $shared = true)Opt into the result cache, optionally overriding the TTL, optionally restricting the result to the request-scoped tier.
on(PropulsionPDO $con)Run on a specific connection instead of the datasource’s default write connection.

Then one terminal:

TerminalReturns
rows()Raw rows, exactly as PDO::FETCH_NUM produces them.
one()The first row, or null if nothing matched.
hydrate(string $peerClass)Model objects, hydrated through the generated Peer.
formatWith(PropulsionFormatter $formatter)Whatever that formatter produces.

Declaring the tables is mandatory

Section titled “Declaring the tables is mandatory”

cache() without dependsOn() throws. Propulsion will not inspect the SQL to work the tables out: a parser would be wrong about CTEs, views, aliases, and subqueries, and a cache that is silently wrong about invalidation is worse than one that insists you say.

dependsOn() validates every name against the runtime DatabaseMap, so a typo throws immediately instead of quietly never invalidating. Names are the table names as they appear in the database, including the schema qualifier for a table declared with a schema attribute (contest.bookstore_contest).

Propulsion::rawQuery($sql)->cache();
// PropulsionException: A cached raw query must declare the tables it reads...

An uncached rawQuery() needs no dependsOn() — it’s only the cache that has to know.

This mandatory declaration is also the reason rawQuery() is the right home for a query whose table references live inside a subquery or CTE: the fluent API’s automatic dependency derivation deliberately doesn’t descend into those, so declaring them by hand is the correct fix.

Hydrating model objects

Section titled “Hydrating model objects”

hydrate() runs the rows through the generated Peer’s populateObjectsFromRows(), so the result is ordinary model objects, instance pool included:

$books = Propulsion::rawQuery('SELECT b.* FROM book b WHERE ...')
    ->dependsOn('book')
    ->cache()
    ->hydrate(BookPeer::class);


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

The SELECT must return that Peer’s columns in its column order — SELECT b.*, not a hand-picked list — exactly as the generated doSelect() requires. A Peer generated by an older version of Propulsion has no populateObjectsFromRows(); the exception says so and tells you to regenerate.

formatWith() accepts any formatter that can work from rows rather than a live statement. FORMAT_STATEMENT and FORMAT_ON_DEMAND cannot, and passing one throws — the same restriction that makes those two formatters never cacheable through the fluent API.

Raw writes invalidate nothing on their own

Section titled “Raw writes invalidate nothing on their own”

A write issued as raw SQL is invisible to Propulsion, so it bumps no table version tokens. Say so explicitly:

$con = Propulsion::getConnection(BookPeer::DATABASE_NAME, Propulsion::CONNECTION_WRITE);
$con->exec("UPDATE book SET view_count = view_count + 1 WHERE id = 42");


Propulsion::invalidateQueryCacheForTables(['book']);

Propulsion::invalidateQueryCacheForTables(array $tableNames, ?string $dbName = null) is the correct answer for every write Propulsion cannot see: raw SQL, another application sharing the database, a migration, a DBA at a console. Without it, affected entries stay served until their TTL lapses.

For a counter increment specifically, there’s a better option that keeps the write inside the ORM and invalidates for you — see database-computed UPDATE values.

Section titled “Related”