Queries and Transactions

Register DatabaseProvider and inject your concrete table into the service that owns its queries. Foundation’s fluent builder binds values and quotes structured identifiers, then executes through the shared Doctrine connection. A table can wrap an existing WordPress or third-party table without owning its migrations.

Read rows

A repository is an optional way to organize reusable application queries:

<?php declare(strict_types=1);

namespace Plugin\Report;

use Plugin\Database\Tables\Reports_Table;

final readonly class Report_Repository {

	public function __construct(
		private Reports_Table $reports,
	) {
	}

	/**
	 * Find one report, or return null when it does not exist.
	 *
	 * @return array<string, mixed>|null
	 */
	public function find( int $id ): ?array {
		return $this->reports->query()
			->select( 'id', 'title' )
			->where( 'id', $id )
			->first();
	}
}

query() returns a fresh StellarWP\Foundation\Database\Query\Query. Use query( 'report' ) for an alias. get() returns a list of associative arrays; first() returns one associative array or null. Driver-returned numeric values can be strings, so convert results according to your application contract.

$rows = $this->reports->query()
	->select( 'id', 'title' )
	->where( 'status', 'ready' )
	->orderBy( 'id', 'desc' )
	->limit( 50 )
	->get();

The initial builder is deliberately bounded. It supplies the operations described here; familiar Laravel-style names do not imply the whole Laravel API is available.

Conditions and literal substring searches

Use two arguments for equality, or three for an explicit comparison. Supported comparison operators are =, !=, <>, <, <=, >, and >=. Comparing equality to null means IS NULL; inequality means IS NOT NULL. Ordering comparisons against null are rejected.

Group alternatives with a callback receiving WhereGroup:

use StellarWP\Foundation\Database\Query\WhereGroup;

$rows = $this->reports->query()
	->where( 'status', 'ready' )
	->where( static fn ( WhereGroup $where ) => $where
		->where( 'priority', '>', 5 )
		->orWhere( 'owner_id', $ownerId ) )
	->get();

where() also accepts an associative map of equalities. whereIn() accepts a list of values, and an empty list matches no rows. whereNull() expresses a null test directly.

$rows = $this->reports->query()
	->where( [
		'status' => 'ready',
		'owner_id' => $ownerId,
	] )
	->whereContainsAny( 'title', $searchTerms )
	->limit( 50 )
	->get();

Use whereContains() for one literal substring:

$rows = $this->reports->query()
	->whereContains( 'title', $searchTerm )
	->get();

whereContainsAny() matches any of several literal substrings. Both methods escape percent signs, underscores, and the escape character before binding search terms. An empty term list matches nothing; an empty string matches every non-null value. Case sensitivity follows the column’s collation. Structured column names, aliases, and sort directions are validated; choose an application allowlist when users should be limited to particular columns.

Use a database expression in a condition

Use whereRaw() when a condition needs application-owned SQL, such as the database’s current time:

$rows = $this->reports->query()
	->where( 'status', 'ready' )
	->whereRaw( '(expires_at IS NULL OR expires_at >= NOW())' )
	->get();

Pass external values through positional ? placeholders and the bindings array. They use the same value normalization and parameter types as ordinary conditions:

use StellarWP\Foundation\Database\Query\WhereGroup;

$rows = $this->reports->query()
	->where( static fn ( WhereGroup $where ) => $where
		->whereRaw( 'amount + ? >= ?', [
			$adjustment,
			$minimum,
		] )
		->orWhereRaw( 'owner_id = ?', [
			$ownerId,
		] ) )
	->get();

whereRaw() joins with AND; orWhereRaw() joins with OR. SQL fragments are used as supplied, so include parentheses around alternatives within a fragment or use a grouped callback around multiple conditions. Blank fragments are rejected. Keep SQL text and identifiers application-owned: bindings protect values, not SQL assembled from user input. Raw conditions work with reads, updates, and deletes.

Order by a database expression

Use orderBy() for ordinary columns and orderByRaw() when ordering requires an application-owned SQL expression. For example, sort by priority, then put reports with an expiration date before those without one within each priority:

$rows = $this->reports->query()
	->orderBy( 'priority' )
	->orderByRaw( 'IF(expires_at IS NULL, 1, 0) ASC' )
	->orderBy( 'expires_at' )
	->get();

Bind external values using the optional second argument. This example puts reports in a preferred category first, ordered by ID, followed by all remaining reports ordered by ID:

$rows = $this->reports->query()
	->orderByRaw( 'IF(category = ?, 0, 1) ASC', [
		$preferredCategory,
	] )
	->orderBy( 'id' )
	->get();

Ordering calls append in declaration order and work with reads and supported single-table updates and deletes. Include ASC or DESC in the raw SQL when needed. Bindings use the same normalization as ordinary query values; they protect values, not SQL text or identifiers. Blank expressions are rejected. Simple aggregates omit ordering and its bindings. Aggregates over paginated, grouped, distinct, or raw selections retain them in the inner query.

Read one column

Use pluck() to retrieve a plain list of column values:

$ids = $this->reports->query()
	->where( 'status', 'ready' )
	->orderBy( 'id' )
	->limit( 50 )
	->pluck( 'id' );

pluck() returns an array with sequential numeric keys, preserving database value types, duplicates, and nulls. No matching rows returns []. Use distinct() when you want unique values.

The call selects only the requested column, replacing any existing select() or selectRaw() projection for that execution. Other clauses remain in place, and the original builder is unchanged. Use a column name or qualified column such as r.id. Any ordering, grouping, or having() must remain valid with that single-column selection; aliases from replaced projections are unavailable.

Paginate results

Use paginate() to retrieve a page of rows together with the total number of matching results:

$page = $this->reports->query()
	->where( 'status', 'ready' )
	->orderBy( 'id' )
	->paginate( perPage: 25, page: 3 );

$rows        = $page->items;
$total       = $page->total;
$currentPage = $page->currentPage;
$perPage     = $page->perPage;
$lastPage    = $page->lastPage();
$hasMore     = $page->hasMorePages();

The default is 25 rows on page one. Supply positive integers for perPage and page; the application reads and validates its request parameters and builds any navigation links. Order by a unique column, or add a unique column as the final ordering criterion, so page boundaries have a consistent order.

The returned Page contains readonly metadata and rows as associative arrays. An empty result has total zero, empty items, and lastPage() one. A page beyond the last page returns empty items and preserves the requested currentPage.

Pagination replaces any existing limit() and offset() for this operation and leaves the original builder unchanged. The total excludes paging and ordering but preserves filters, joins, distinctness, grouping, and having(). For grouped queries, it counts matching groups. The aggregate projection restrictions described below also apply to pagination totals.

Foundation runs a count query, then fetches the requested rows when the total is nonzero. These are separate reads: concurrent writes can change the data between them, and pagination does not start a transaction. With lockForUpdate(), only the page query requests locks; run it inside a managed transaction when those locks must remain held.

Grouping and aggregates

count() returns an integer, exists() returns a boolean, and max( 'column' ) returns the driver’s scalar value or null when no value exists. Terminal reads do not alter the builder, so you can count and then fetch the same query.

$query = $this->reports->query()->where( 'status', 'ready' );
$count = $query->count();
$rows  = $query->get();

Use sum() to total a numeric column:

$total = $this->reports->query()
	->where( 'status', 'ready' )
	->sum( 'amount' );

sum() returns int|float|string. It ignores null values and returns integer 0 when no values remain. Otherwise, it preserves the driver’s numeric result: exact decimal strings stay strings, and floating-point results stay floats. Like the other terminal reads, it leaves the builder unchanged.

distinct(), groupBy(), having(), limit(), and offset() shape the rows seen by aggregates. Foundation aggregates a derived table when those operations are present. In particular, limit( 10 )->count() counts at most ten rows, and a grouped count counts groups. Explicit projections and their aliases remain intact, including aliases used by ordering.

$largest = $this->reports->query()
	->select( 'score as report_score' )
	->orderBy( 'report_score', 'desc' )
	->limit( 10 )
	->max( 'report_score' );

For grouped reports, use application-owned SQL expressions through selectRaw() and pass external values in its bindings array:

$groups = $this->reports->query()
	->selectRaw( 'status, COUNT(*) AS total' )
	->groupBy( 'status' )
	->having( 'total', '>', 1 )
	->get();

Raw projections are opaque: Foundation preserves every selectRaw() projection when counting, summing, finding a maximum, or checking existence. For example, selectRaw( 'COUNT(*) AS total' )->exists() returns true even when the source table is empty, because that aggregate produces one result row. Calling select() replaces the projection and clears this raw-projection behavior.

A shaped column aggregate must name an output column or alias of the inner query. For example, select( 'name' )->limit( 10 )->max( 'amount' ) throws InvalidArgumentException because amount is not selected. Include amount in the projection, or use its selected alias. Foundation leaves the projection, grouping, and distinctness unchanged. Foundation does not parse raw expressions to discover their outputs; invalid raw output references fail through the database. Shaped joins require an explicit projection to avoid duplicate column names in a derived table.

Join application and WordPress tables

Pass injected application tables to join() or leftJoin(). Use alias: to give the joined table a query-local name:

$rows = $this->reports->query( 'r' )
	->join(
		$this->categories,
		'c.id',
		'=',
		'r.category_id',
		alias: 'c',
	)
	->select( 'r.id', 'r.title', 'c.name as category' )
	->get();

For grouped ON conditions or bound values, pass a callback receiving JoinClause. An unmatched leftJoin() preserves the source row:

use StellarWP\Foundation\Database\Query\JoinClause;

$rows = $this->reports->query( 'r' )
	->leftJoin(
		$this->categories,
		static fn ( JoinClause $join ) => $join
			->on( 'c.id', '=', 'r.category_id' )
			->where( 'c.active', true ),
		alias: 'c',
	)
	->select( 'r.id', 'r.title', 'c.name as category' )
	->get();

join() and leftJoin() also accept unprefixed application names and resolved table references. The optional alias: argument replaces any alias already supplied with the table; omitting it preserves that alias. Pass application table objects directly rather than their physical name(), which would be treated as an unprefixed string and prefixed again.

Inject StellarWP\Foundation\Database\Query\Database when a query needs a table entry point independent of a concrete application table. DatabaseProvider supplies its dependencies automatically.

table() accepts a Table, an unprefixed application name, or a Query\ValueObjects\TableReference, with an optional alias argument. wordpress() returns a resolved reference to a known WordPress core table. It honors WordPress’s site and network table properties, including custom users tables. References are resolved when created; build a fresh query after switching sites between operations.

$rows = $this->db->table( $this->reports, 'r' )
	->join( $this->db->wordpress( 'users' ), 'u.ID', '=', 'r.owner_id', alias: 'u' )
	->select( 'r.id', 'r.title', 'u.user_email' )
	->where( 'r.status', 'ready' )
	->get();

Write rows

Insert one row and retrieve its generated identifier:

$id = $this->reports->insertGetId( [
	'title' => 'Weekly',
	'status' => 'draft',
] );

$this->reports->update( [
	'status' => 'ready',
], [
	'id' => $id,
] );

insert() accepts one associative row or a list of rows and returns the total affected-row count as int. The table convenience and fluent query use the same implementation, including value normalization and bulk insertion:

$this->reports->insert( [
	[
		'title' => 'Weekly',
		'status' => 'draft',
	],
	[
		'title' => 'Monthly',
		'status' => 'draft',
	],
] );

$this->reports->query()
	->where( 'id', $id )
	->update( [
		'status' => 'ready',
	] );

$this->reports->query()->where( 'id', $id )->delete();

You can also call $this->reports->query()->insert( $rows ); it has the same behavior as $this->reports->insert( $rows ). Empty input returns zero without inserting a row.

insertGetId() accepts exactly one associative row and returns its generated ID as int|string. Use it on the table or a fresh query. Passing [] explicitly inserts one row using database defaults and returns that new ID. A list of rows is rejected, and insertion or ID retrieval failures throw.

Table::update( $values, $criteria ) and delete( $criteria ) delegate to filtered queries and return affected-row counts as int|string. Criteria are column-to-value equality comparisons; null matches IS NULL. Both methods reject empty criteria. Table and fluent writes share value normalization.

To write a PHP array to a JSON column through a table or fluent query, encode it first. JSON_THROW_ON_ERROR raises JsonException if encoding fails:

$this->reports->insert( [
	'payload' => json_encode( [
		'enabled' => true,
	], JSON_THROW_ON_ERROR ),
] );

For writes that require explicit Doctrine conversions, such as a JSON type or a custom type, use the shared connection’s insert(), update(), or delete(). For example, pass the PHP array with Types::JSON and let Doctrine encode it:

use Doctrine\DBAL\Types\Types;

$this->connection->insert( $this->reports->quotedName(), [
	'payload' => [
		'enabled' => true,
	],
], [
	'payload' => Types::JSON,
] );

Insert, insert-or-ignore, upsert, and delete queries use an unaliased target table. Start them with $table->query() without an alias.

Query insert() and upsert() return total affected rows as int; update() and delete() preserve Doctrine’s int|string affected-row counts. Rows in a multi-row write must have identical column sets, although their key order may differ. Empty insert and upsert inputs return zero. Boolean values are bound as integers; use decimal strings for exact amounts. Date/time values are formatted with microseconds, and the target column and database determine retained precision.

MySQL normally rounds fractional seconds when writing to a lower-precision column, which can advance the stored date near midnight. Use DATETIME(6) to preserve microseconds, or pass $date->format('Y-m-d H:i:s') to deliberately discard them before writing to a second-precision column.

Large writes split into multiple statements within the parameter limit. Each statement is atomic, but the complete write needs a caller-owned transaction to roll back earlier chunks if a later chunk fails.

update() and delete() require conditions, retain supported ordering and limits, and reject joins, projections, distinct, grouping, having, or offsets. insert(), insertOrIgnore(), insertGetId(), and upsert() reject previously accumulated query conditions, projections, ordering, limits, and other shaping state, even for empty input. Start from a fresh query for those operations.

Insert rows and skip duplicates

Use insertOrIgnore() when existing rows should remain unchanged on a primary or unique-key conflict:

$inserted = $this->reports->query()->insertOrIgnore( [
	[
		'id' => 10,
		'title' => 'Weekly',
		'status' => 'draft',
	],
	[
		'id' => 11,
		'title' => 'Monthly',
		'status' => 'draft',
	],
] );

The method accepts one associative row or a list of rows and returns the number actually inserted as int. If report 10 already exists and 11 is new, $inserted is 1. Empty input or a write in which every row is skipped returns 0.

Foundation still rejects invalid row shapes, unsupported PHP values, and accumulated query options. Unignored database failures throw. The method shares insert()’s binding and chunking behavior and participates in the caller’s transaction; wrap multi-chunk writes in transactional() when they must roll back together.

Increment or decrement a column

Use increment() and decrement() to change a numeric column in one database statement:

$affected = $this->reports->query()
	->where( 'id', $id )
	->increment( 'views' );

$affected = $this->credits->query()
	->where( 'id', $id )
	->decrement( 'credits_remaining', 5 );

The amount defaults to 1. Both methods return affected-row counts as int|string, require conditions, and support the same ordering and limits as update(). The database applies the arithmetic to the current column value, avoiding a separate read and write in PHP. They participate in an existing transaction without starting one themselves.

Amounts accept integers and finite floats. Negative amounts reverse the direction; zero leaves values unchanged. SQL arithmetic leaves a NULL column as NULL. The column’s type and database SQL mode govern rounding and overflow. Integer amounts retain integer binding; floating-point amounts use approximate arithmetic. For exact decimal arithmetic, use the native connection with an explicit SQL decimal cast appropriate to your column instead of converting a decimal string to a PHP float.

Upsert using existing keys

upsert( $rows, $update ) uses the table’s existing primary and unique keys. The required update list names the columns to change on conflict. There is no conflict-target argument: MySQL and MariaDB choose conflicts from all existing primary and unique keys.

For a table whose schema already defines external_id as unique:

$this->reports->query()->upsert( [
	[
		'external_id' => 'weekly-2026-09-25',
		'title' => 'Weekly report',
		'status' => 'ready',
	],
], update: [
	'title',
	'status',
] );

A normal nonunique index does not create upsert conflict semantics. Upsert does not alter your schema, and a multi-statement upsert has the same caller-owned transaction boundary as insert.

Count or clear a table

$this->reports->count() counts all rows; use query()->where( ... )->count() for a filtered count.

deleteAll() explicitly deletes every row, preserves the auto-increment sequence, and follows foreign-key rules and delete triggers. With InnoDB, deletion participates in the caller’s transaction:

$this->connection->transactional( function () use ( $rows ): void {
	$this->reports->deleteAll();
	$this->reports->query()->insert( $rows );
} );

An insertion failure rolls back the deletion and preserves the previous rows.

truncate() removes all rows and resets auto-increment. It returns no value. MySQL and MariaDB truncation implicitly commits and cannot be rolled back, so Foundation rejects it with DatabaseException inside an active shared transaction. Truncation does not run delete triggers, and references from other tables can prevent it. Both clearing methods keep foreign-key checks enabled and fail if the table is missing.

Native SQL and inspection

toSql() and getBindings() inspect the compiled read query without executing it. Bindings remain separate from SQL.

Inject Doctrine\DBAL\Connection for specialized SQL or native Doctrine builders and results. Resolve application table names through quotedName() and bind external values:

$connection->executeStatement(
	'UPDATE ' . $reports->quotedName() . '
	SET status = ?
	WHERE id = ?',
	[
		'ready',
		$id,
	],
);

Native Doctrine execution exceptions remain unchanged. Foundation validation failures reject invalid query construction before execution. Keep raw SQL and expressions application-owned; raw query methods do not make untrusted SQL safe.

Choose a transaction boundary

Inject the shared connection into the service that owns the complete unit of work. Collaborating repositories can continue to use their injected tables.

use Doctrine\DBAL\Connection;
use Plugin\Database\Tables\Reports_Table;

final readonly class Create_Report {

	public function __construct(
		private Connection $connection,
		private Reports_Table $reports,
	) {
	}

	public function create( string $title ): int|string {
		return $this->connection->transactional( function () use ( $title ): int|string {
			return $this->reports->insertGetId( [
				'title' => $title,
				'status' => 'draft',
			] );
		} );
	}
}

transactional() returns the callback’s value only after commit is acknowledged. false and null are ordinary callback results, not rollback signals. Throw an exception to cancel work. The original escaping exception is preserved if cleanup also fails.

Nested transactions use savepoints. An inner success is provisional until the outer transaction commits. An inner business exception can be caught after its savepoint is rolled back. A database statement failure prevents further work until Foundation confirms rollback to the original nested savepoint. The application can then catch the failure and decide whether to continue. Foundation does not select recoverable error codes or retry the work.

Lock rows while making a decision

Use lockForUpdate() inside a transaction when you need to read a row before deciding how to change it:

$this->connection->transactional( function () use ( $id, $amount ): void {
	$credit = $this->credits->query()
		->where( 'id', $id )
		->lockForUpdate()
		->first();

	if ( $credit === null || $credit['credits_remaining'] < $amount ) {
		throw new \RuntimeException( 'Insufficient credits.' );
	}

	$this->credits->query()
		->where( 'id', $id )
		->decrement( 'credits_remaining', $amount );
} );

lockForUpdate() adds FOR UPDATE to the SELECT and works with first(), get(), and pluck(). For InnoDB tables, conflicting writes and locking reads wait until the owning transaction commits or rolls back. Ordinary nonlocking reads can still read a committed snapshot. Lock scope depends on the query, indexes, and isolation level and may include scanned records or gaps beyond the returned rows.

The method does not begin a transaction. With autocommit enabled and no active transaction, it does not protect a later update. Keep the read and related writes inside the same transactional() callback, and perform decisions there before it returns. A nested transaction’s successful return does not release the outer transaction’s locks.

Queries retain the locking option for subsequent reads and clones. Start a fresh query for writes; insert, update, and delete operations reject this read-only option. Aggregates and exists() pass the locking clause to their underlying SELECT; use a row read when the application needs to inspect and lock particular records. Database lock timeouts and deadlocks propagate through the existing transaction failure handling.

Recover from an expected duplicate

Wrap an insert that may conflict in a nested transactional() call and catch UniqueConstraintViolationException outside that call. Foundation attempts to roll back the nested writes before returning control to the catch block. After successful rollback, earlier outer work remains provisional and the outer transaction can continue. If cleanup fails, the original exception still reaches the catch block, but further work and commit are rejected. Savepoint rollback does not necessarily release every lock acquired during the nested work.

For a reports table with a unique slug, this operation creates a report or returns the existing report’s ID:

use Doctrine\DBAL\Exception\UniqueConstraintViolationException;

return $this->connection->transactional( function () use ( $slug, $title ): int|string {
	try {
		return $this->connection->transactional(
			fn (): int|string => $this->reports->insertGetId( [
				'slug'  => $slug,
				'title' => $title,
			] ),
		);
	} catch ( UniqueConstraintViolationException $failure ) {
		$report = $this->reports->query()
			->select( 'id' )
			->where( 'slug', $slug )
			->lockForUpdate()
			->first();

		if ( $report === null ) {
			throw $failure;
		}

		return $report['id'];
	}
} );

Under REPEATABLE READ, an ordinary read can retain an earlier snapshot even after another transaction commits the conflicting row; rolling back a savepoint does not refresh that snapshot. FOR UPDATE requests a locking read. MariaDB with innodb_snapshot_isolation enabled can reject an insert or locking read against a row outside that snapshot with error 1020 and roll back the entire transaction. Let that failure escape and handle a fresh operation at the application boundary; it is not a recoverable duplicate.

The same boundary supports other statement failures when the original savepoint survives and rollback succeeds. A lock timeout, for example, can affect only the statement or the entire transaction depending on server configuration. Foundation confirms the rollback rather than assuming the error is recoverable. Catch only failures your application knows how to handle.

Catching a database failure inside the nested callback and returning normally still raises TransactionFailed; the nested call cannot report success. Explicit nested beginTransaction() / rollBack() boundaries support the same recovery, but transactional() handles cleanup for you.

Without a nested boundary, a database execution failure prevents the outer transaction from committing. Transaction-wide rollbacks, including InnoDB deadlocks, destroy the savepoints needed for recovery. Connection loss, detected scope changes, transaction-control failures, and failed savepoint cleanup remain terminal. Foundation does not retry statements. Operations running under an advisory lock, including migrations, retain their stricter failure behavior and must abort after an execution failure.

Handle failures deliberately

Ordinary SQL failures use Doctrine exceptions, such as Doctrine\DBAL\Exception\UniqueConstraintViolationException.
TransactionFailed means a caught failure prevented an operation from completing successfully. Let it escape so cleanup rolls back the operation. After an unrecovered or terminal failure, start fresh work at an application boundary.
CommitOutcomeUnknown means the server did not confirm commit. The data may already be committed. Inspect durable application state or use an idempotency key before retrying.
A detected site or connection change interrupts managed work. Foundation never replays failed SQL or reconnects in the middle of a transaction.

The Foundation exceptions above live under StellarWP\Foundation\Database\Exceptions and extend DatabaseException. Native Doctrine SQL exceptions retain their own hierarchy.

Between completed operations, WordPress may replace its mysqli connection. New queries and transactions use the replacement automatically. Prepared statements from the old connection are rejected; prepare them again for the new operation.

Test application behavior

Unit-test application decisions against your own repository contracts when useful. Use integration tests with real InnoDB tables for transaction boundaries, SQL behavior, and recovery. Test that an observer on a second connection cannot see uncommitted rows, that a failed operation leaves prior rows intact, and that caught database failures cannot produce a successful result without the supported savepoint recovery.