Appendix: Query Builder¶

The complete kinetis/query-builder contract behind Query Builder: row mapping, the clauses and writes the builder compiles, what each dialect receives, and every combination refused before a statement runs. Appendix: Satellite Packages lists the package’s classes.

Queries and links¶

new Query($link) takes a MysqlLink, a PostgresLink or a SqlTransaction, and the link’s MysqlLink or PostgresLink marker picks the dialect. A variable typed SqlTransaction is accepted because every built-in transaction also carries its link’s dialect marker; a SqlTransaction carrying neither marker throws QueryBuilderException instead of guessing a dialect.

The read terminals that add a limit, order or projection — first(), value(), paginate() and cursorPaginate() — apply it to a copy, so the builder you hold is unchanged afterwards. toSelectSql(), toUpdateSql($values) and toDeleteSql() return the SQL and bindings without running them.

Row mapping¶

get(), first(), paginate() and cursorPaginate() take an optional DTO class. Kinetis\QueryBuilder\RowMapper reflects that class once per result set and passes each constructor parameter the column of exactly its name, case-sensitively:

Columns no parameter names are ignored.
A missing column leaves its parameter to the declared default. A missing column for a parameter without a default is refused, even when the parameter is nullable.
A class without a constructor is constructed with no arguments.
Every value is checked before the constructor runs. An exception the constructor throws is the DTO’s own invariant and propagates unchanged.

A parameter’s declared type decides which row values it admits:

Declared type	Admits	Passes
none, or `mixed`	any value, `null` included	the value unchanged
`string`	a `string`	the string
`int`	an `int`, or its canonical decimal string: `"42"` and `"-7"`, but not `"042"`, `"+7"`, `" 7"`, `"7.0"`, `"7e0"` or a value beyond PHP’s `int` range	an `int`
`float`	a finite `int` or `float`, or a numeric string with a finite value	a `float`
`bool`	a `bool`, `0`, `1`, `"0"` or `"1"`	a `bool`
a backed enum	a case of that enum, or a value the `string` or `int` row above admits for its backing type that names a case	the case

?T and T|null also admit null; no other type does. Nothing else is converted — no arrays, nested DTOs, dates, other objects or JSON text — and validation attributes such as #[NotBlank] are not evaluated: a row is data from your own database, not client input.

RowMapper::for() refuses, before any row is read, a class that cannot be instantiated and a constructor parameter that is variadic, passed by reference, or declared with an intersection type, a union other than T|null, or any other builtin or class type. The mapper is public for rows from elsewhere:

use Kinetis\QueryBuilder\RowMapper;

$article = RowMapper::for(ArticleRow::class)->map(['id' => '7', 'title' => 'Hello', 'slug' => 'hello']);
// ArticleRow with id 7

Every failure is a Kinetis\QueryBuilder\Exception\RowMappingException, an InvalidArgumentException:

Factory	Thrown when
`unsupportedDefinition()`	`for()` meets a class or parameter outside the admitted types
`missingColumn()`	a row has no column for a parameter without a default
`invalidValue()`	a value is not admitted by its parameter’s type, including `null` for a non-nullable one
`unknownEnumCase()`	an admitted backing value names no case

A message names the DTO class, the parameter and the expected shape, and describes the value only by its type, never by its contents.

Selecting and filtering¶

select() takes column names only; name a computed or renamed column with selectRaw(). Once selectRaw(), selectSub() or selectExists() is used without select(), the default * is dropped.
value() and pluck() read the column from each row by its result name and leave the projection as you built it. A qualified column (articles.slug) arrives under its last segment. A name missing from the row throws QueryBuilderException.
Groups nest, and a group that adds nothing compiles to nothing.
whereRaw() refuses an empty fragment.
Only comparisons treat null specially; inserted and updated null values bind normally.

Joins¶

crossJoin('sizes') joins every row with every row. joinSub() joins a subquery under an alias:

$counts = new Query($db)
    ->table('comments')
    ->select('article_id')
    ->selectRaw('COUNT(*) AS total')
    ->groupBy('article_id');

$rows = new Query($db)
    ->table('articles')
    ->joinSub($counts, 'c', fn (Conditions $on) => $on->whereColumn('c.article_id', '=', 'articles.id'))
    ->select('articles.title', 'c.total')
    ->get();

Subqueries¶

Pass a Query wherever a subquery is accepted. It is compiled at the moment you pass it, so changing it afterwards does not change the outer query. It must be built on the same kind of connection, and it cannot carry with() or a row lock; with() belongs on the outermost query, where subqueries can select from it by name.

`EXISTS`¶

$favorited = new Query($db)
    ->table('favorites')
    ->whereColumn('favorites.article_id', '=', 'articles.id')
    ->where('favorites.user_id', '=', $userId);

$rows = new Query($db)->table('articles')->whereExists($favorited)->get();

whereExists(), orWhereExists(), whereNotExists() and orWhereNotExists() take a subquery that may refer to the outer query’s tables. The same predicates work in update() and delete():

new Query($db)
    ->table('articles')
    ->whereNotExists(new Query($db)->table('comments')->whereColumn('comments.article_id', '=', 'articles.id'))
    ->where('published_at', '<', $cutoff)
    ->delete();

MySQL 8.4 rejects an UPDATE or DELETE whose subquery reads the table being changed (error 1093); MariaDB and PostgreSQL accept it.

`IN`¶

$followed = new Query($db)
    ->table('follows')
    ->select('followee_id')
    ->where('follower_id', '=', $userId);

$feed = new Query($db)->table('articles')->whereIn('author_id', $followed)->get();

NOT IN against a subquery that returns a NULL matches no row, as SQL defines it. On MySQL and MariaDB, an IN subquery carrying limit() or offset() throws QueryBuilderException: both servers reject that statement. Select from the limited query with fromSub() instead, which all three servers accept:

$latest = new Query($db)->table('articles')->select('id')->orderBy('id', 'desc')->limit(10);

$comments = new Query($db)
    ->table('comments')
    ->whereIn('article_id', new Query($db)->fromSub($latest, 'latest')->select('latest.id'))
    ->get();

In the select list and the `FROM` clause¶

$favoriteCount = new Query($db)
    ->table('favorites')
    ->selectRaw('COUNT(*)')
    ->whereColumn('favorites.article_id', '=', 'articles.id');

$rows = new Query($db)
    ->table('articles')
    ->select('articles.id', 'articles.title')
    ->selectSub($favoriteCount, 'favorites_count')
    ->get();

$busyAuthors = new Query($db)
    ->fromSub(new Query($db)->table('articles')->select('author_id')->where('status', '=', 'published'), 'p')
    ->select('p.author_id')
    ->distinct()
    ->get();

selectExists() selects whether a subquery returns a row, as 1 or 0 on every server, so a bool DTO parameter maps from it:

$following = new Query($db)
    ->table('follows')
    ->whereColumn('follows.followee_id', '=', 'articles.author_id')
    ->where('follows.follower_id', '=', $viewerId);

$cards = new Query($db)
    ->table('articles')
    ->select('articles.id', 'articles.title')
    ->selectExists($following, 'following')
    ->get(ArticleCardRow::class); // application-owned DTO with a `bool $following` parameter

Grouping and aggregates¶

groupBy(string ...$columns) and groupByRaw($sql, $params).
having()/orHaving() compare a grouped column with the same operators and null handling as where().
havingRaw($sql, $params) compares an aggregate.

On a query with distinct(), groupBy(), a having clause or a set operation, count(), sum(), min(), max() and avg() aggregate the rows the query returns. They compile as SELECT COUNT(*) FROM (the query) AS aggregate_source (or SUM(column), and so on), keeping every binding the rows depend on. The column an aggregate names is resolved against that derived table, so pass the unqualified output name the query’s select list gives it: over a query selecting COUNT(*) AS articles per author, sum('articles') totals the per-author counts. MySQL and MariaDB reject the derived table when two selected columns share an output name (a.id and b.id): select them under distinct names.

On any other query the aggregates run over the filtered and joined rows directly, and the select list plays no part.

Set operations and CTEs¶

`union()`, `intersect()` and `except()`¶

$pinned = new Query($db)->table('articles')->select('id', 'title')->where('pinned', '=', true);
$recent = new Query($db)->table('articles')->select('id', 'title')->orderBy('published_at', 'desc')->limit(5);

$front = $pinned
    ->union($recent)
    ->orderBy('title')
    ->limit(10)
    ->get();
// (SELECT ... WHERE `pinned` = ?) UNION (SELECT ... ORDER BY `published_at` DESC LIMIT 5) ORDER BY `title` ASC LIMIT 10

Each method takes bool $all = false for UNION ALL, INTERSECT ALL and EXCEPT ALL. On the query you call them on, orderBy(), limit() and offset() apply to the combined result; an operand’s own ordering and limit stay inside its parentheses. Operations apply in call order: $a->union($b)->intersect($c) is (a ∪ b) ∩ c. A combined query can itself be an operand.

`with()`¶

$popular = new Query($db)->table('favorites')->select('article_id')->groupBy('article_id')->havingRaw('COUNT(*) > ?', [100]);

$rows = new Query($db)
    ->with('popular', $popular)
    ->table('articles')
    ->whereIn('id', new Query($db)->table('popular')->select('article_id'))
    ->get();
// WITH `popular` AS (SELECT ...) SELECT * FROM `articles` WHERE `id` IN (SELECT `article_id` FROM `popular`)

with(string $name, Query $query, array $columns = []) defines a common table expression that the query, and its subqueries, select from by name. $columns names its result columns.

`withRecursive()`¶

$root = new Query($db)->table('categories')->select('id', 'parent_id')->where('id', '=', $categoryId);
$children = new Query($db)
    ->table('categories')
    ->select('categories.id', 'categories.parent_id')
    ->join('tree', 'tree.id', '=', 'categories.parent_id');

$descendantIds = new Query($db)
    ->withRecursive('tree', $root->union($children, all: true), ['id', 'parent_id'])
    ->table('tree')
    ->pluck('id');

The recursive query is a union() of a starting query and a query that joins the CTE’s own name. One recursive CTE makes the whole clause WITH RECURSIVE.

Pagination¶

cursorPaginate(int $perPage, ?string $cursor, string $cursorColumn = 'id', ?string $dtoClass = null, ?string $cursorAlias = null) orders by $cursorColumn, filters $cursorColumn > $cursor once a cursor is given, and reads nextCursor from the last delivered row in the same query. The cursor filter wraps your own predicates as (existing predicate) AND id > ?, so an orWhere() cannot escape it. A projection that omits the cursor column still works: the column is selected, read, and removed from every row before mapping.

On a joined query, pass a qualified cursor column and name an alias for it. Both server families return orders.id under the bare key id, which a joined table’s id would overwrite:

return new Query($this->db)
    ->table('orders')
    ->join('customers', 'orders.customer_id', '=', 'customers.id')
    ->select('orders.total', 'customers.name')
    ->cursorPaginate(perPage: 20, cursor: $cursor, cursorColumn: 'orders.id', cursorAlias: 'order_cursor');

A qualified column without an alias, or an alias equal to a column you listed in select(), throws InvalidPaginationException. An alias that a wildcard’s columns already use cannot be detected: that column is replaced in the returned rows, so pick a name nothing in the projection uses.

InvalidPaginationException is an InvalidArgumentException naming the argument it refuses.

Writes¶

insert() runs one statement for a row or a batch. A batch binding more than 65,535 values throws InvalidArgumentException; split it yourself, knowing each call is its own statement.
insertGetId(array $values, string $primaryKey = 'id') inserts one row and returns its generated key.
upsert(array $values, array $uniqueBy, array $update) takes a row or a batch, and returns the server’s affected-row count.
increment() and decrement() take an int|float amount (1 by default) and an optional map of further column => value assignments: decrement('stock', 2, ['sold_at' => $now]).

insertUsing() inserts the rows a select returns, and the select may carry its own with():

$inserted = new Query($db)->table('notifications')->insertUsing(
    ['user_id', 'article_id'],
    new Query($db)->table('follows')
        ->join('articles', 'articles.author_id', '=', 'follows.followee_id')
        ->select('follows.follower_id', 'articles.id')
        ->where('articles.id', '=', $articleId),
);
// int: rows inserted

What each write refuses¶

Write	Refuses
`update()`, `increment()`, `decrement()`, `delete()`	no where predicate; `with()`, a table alias, `fromSub()`, `distinct()`, `select()`/`selectRaw()`/`selectSub()`/`selectExists()`, any join, grouping, `having`, set operations, ordering, `limit()`, `offset()`, a lock
`insert()`, `insertGetId()`, `insertUsing()`, `insertOrIgnore()`, `upsert()`	all of the above, and any where predicate

increment() also refuses assigning its own column through its extra assignments. upsert() refuses an empty $uniqueBy or $update and a column in either that is not inserted.

Upsert on MySQL and MariaDB¶

The shared spelling is VALUES(col). MariaDB has no INSERT ... AS row alias, and MySQL 8.4 runs VALUES(col) with deprecation warning 1287.
A conflict on any unique key of the table triggers the update; $uniqueBy is validated but does not reach the SQL. PostgreSQL updates only on a conflict with exactly $uniqueBy’s constraint.
The affected-row count is 1 per inserted row, 2 per updated row and 0 per row left unchanged. PostgreSQL counts 1 per row.
insertOrIgnore() assigns a column to itself instead of using INSERT IGNORE, which also turns other errors into warnings and writes the row.

Locks¶

A lock is admitted on a select from one table or inner joins, with where predicates, ordering, limit and offset. A locked query is refused with:

distinct(), groupBy(), a having clause, a set operation, or a LEFT/RIGHT join, which PostgreSQL rejects with a lock;
fromSub(), joinSub(), crossJoin() or with();
count(), the aggregates, exists(), paginate() and cursorPaginate();
use as a subquery, operand or insertUsing() source, and on any write.

Run any other locking read as raw SQL inside the transaction. A NoWait conflict throws the server’s error as a QueryException; on MariaDB it is error 1205, which the MySQL-family drivers treat as ending the transaction (see Appendix: Databases).

Dialect spellings¶

Feature	MySQL 8.4 and MariaDB 11.4	PostgreSQL 16
Identifier quoting	`name`	`"name"`
`offset()` without `limit()`	`LIMIT 18446744073709551615 OFFSET n`	`OFFSET n`
`lockForShare()`	`LOCK IN SHARE MODE`	`FOR SHARE`
`insertOrIgnore()`	`ON DUPLICATE KEY UPDATE first_column = first_column`	`ON CONFLICT DO NOTHING`
`upsert()`	`ON DUPLICATE KEY UPDATE col = VALUES(col)`	`ON CONFLICT (unique columns) DO UPDATE SET col = EXCLUDED.col`
`insertGetId()`	the driver’s last insert id	`RETURNING` the key
`IN` subquery with `limit()`/`offset()`	refused	compiled

Everything else compiles identically.

How values reach the database¶

On the native MySQL and PostgreSQL drivers, a statement whose every value is an int or bool is sent with those values written as literals; any string, float or null makes the whole statement bind. On the PDO drivers, which carry Kinetis\Persistence\Contract\PrefersPreparedStatements, every value binds. A raw fragment whose text contains a ? — including one inside a subquery, CTE, operand or join — makes the whole statement bind, since that ? may not be a placeholder. Raw text without a ? leaves the literals in place.

Allow-listed keywords¶

Operators (=, !=, <>, <, <=, >, >=, LIKE, NOT LIKE), order directions (ASC, DESC), join types (INNER, LEFT, RIGHT) and the $boolean argument (AND, OR) are checked when set and throw InvalidArgumentException for anything else. They cannot be bound as parameters, so a sortable or filterable API that passes request values into them relies on this check.