Download the PHP package flowpack/query-object-builder without Composer

Query Object Builder

A fluent, immutable, fully-typed SQL query builder for PHP 8.4+.

You compose a query from small, type-safe expression objects and render it to a parameterized SQL string with bound arguments — never by concatenating strings. The package ships two dialect families, each modelling its own SQL rather than a lowest-common-denominator subset:

• PostgreSQL\Q — the PostgreSQL builder.
• MySQL\Q — a single MySQL-family builder covering both MySQL and MariaDB. Every construct is buildable regardless of engine or version; where the two engines diverge you build the engine's own form, and an opt-in target-validation pass reports any construct the engine (and version) you are targeting cannot express.

Both families share the same design — fluent, immutable, type-state builders and the Q / Q\Func facade split — so once you know one, you know the other.

Contents

• Why Query Object Builder?
• Requirements
• Installation
• The two dialect families
• Quick start
• Core concepts
• MySQL & MariaDB: one builder, two engines
• Examples
• Parameters
• Validation & errors
• Executing queries
• Best practices
• Development
• License

Why Query Object Builder?

• Dialect-native, not lowest-common-denominator — each family's facade and builders model that engine's own SQL (PostgreSQL arrays and ON CONFLICT; MySQL/MariaDB JSON_TABLE, ON DUPLICATE KEY UPDATE, backtick quoting). No feature is dropped to fit a shared subset.
• JSON-first — first-class support for building hierarchical data directly in the database (json_build_object / json_agg on PostgreSQL, JSON_OBJECT / JSON_ARRAYAGG on MySQL/MariaDB).
• Complete feature set — CTEs, window functions and frames, grouping, subqueries, upserts, RETURNING, set-returning / table functions, and more.
• Type-safe — builder methods only expose what is valid in the current context, so invalid queries are hard to express.
• Immutable — every builder method returns a new instance, so base queries can be shared and specialised without surprises.
• Runtime target validation — the MySQL family renders both engines from one builder and can report (never silently rewrite) any construct a specific engine or version cannot express.
• Zero runtime dependencies — requires only PHP 8.4+. Everything else is a dev-only dependency (PHPUnit, Pest, PHPStan).

Requirements

• PHP 8.4 or newer

Installation

The two dialect families

Pick the facade for your database; the fluent API is the same shape on both.

PostgreSQL\Q and MySQL\Q do not share types — a query built with one is rendered by its own QueryBuilder.

Quick start

PostgreSQL:

MySQL / MariaDB:

Q::build($q)->toSql() returns a [$sql, $args] pair: a SQL string with the dialect's placeholders and the positional argument list to bind. See Executing queries for how to run it.

Core concepts

The Q facade

Q is the single entry point for building queries. It exposes the builder package as static factory methods so you never reference the underlying builder types directly:

• Statements: Q::select(), Q::insertInto(), Q::update(), Q::deleteFrom(), Q::with(), Q::withRecursive() (MySQL family adds Q::replaceInto()).
• Identifiers: Q::n('table.column') for names/columns.
• Literals: Q::string(), Q::int(), Q::float(), Q::bool(), Q::null(), Q::default() (PostgreSQL adds Q::array(), Q::interval()).
• Parameters: Q::arg() (positional), Q::bind() (named).
• Composition: Q::and(), Q::or(), Q::not(), Q::exists(), Q::any(), Q::all(), Q::case(), Q::coalesce(), Q::func().

The Q\Func facade

SQL functions live on Q\Func. On PostgreSQL: Q\Func::jsonBuildObject(), Q\Func::jsonAgg(), Q\Func::count(), Q\Func::rowNumber(), Q\Func::unnest(), … On MySQL/MariaDB: Q\Func::jsonObject(), Q\Func::jsonArrayAgg(), Q\Func::count(), Q\Func::groupConcat(), Q\Func::rank(), … It is named Func (not Fn) because fn is a reserved keyword in PHP.

Q\Func is the expression facade: every method returns something usable anywhere an expression is valid. Constructs that are not general expressions — a statement, or a FROM-only producer like JSON_TABLE — live on Q instead (Q::jsonTable(), PostgreSQL's Q::rowsFrom()).

Immutability

Every builder method returns a new builder — the original is never mutated. This makes base queries safe to reuse:

Operators on expressions

Expressions returned by Q::n(), Q::arg(), literals and functions carry the SQL operators as fluent methods; what reads as a function is built through the facade. Each family models the operator set its dialect actually has:

• Shared: ->eq(), ->neq(), ->lt(), ->lte(), ->gt(), ->gte(), ->like(), ->in(), ->isNull(), ->isNotNull(), ->plus(), ->minus(), ->mult(), ->and()/->or(), …
• PostgreSQL-specific: ->ilike(), ->cast('text') (::), ->concat() (||), array/JSON operators.
• MySQL/MariaDB-specific: ->nullSafeEq() (<=>), ->regexp(), ->memberOf() (MEMBER OF), ->jsonExtract() / ->jsonExtractText() (-> / ->>, MySQL), the bitwise operators (->bitAnd(), ->bitOr(), ->shiftLeft(), …).

Parentheses are added automatically based on each dialect's operator precedence.

Building and rendering

A finished query is handed to Q::build($q) to configure rendering, then ->toSql() produces the [$sql, $args] pair:

See Validation & errors for what is checked and how to opt out, and the MySQL-only target validation pass.

MySQL & MariaDB: one builder, two engines

The MySQL family is a single builder for both MySQL and MariaDB. The two engines share ~95% of their grammar and all of their rendering conventions (backtick identifiers, ? placeholders, string escaping), so one builder models both. This section is the design goal that makes that safe.

Nothing is gated at build time. Every construct is buildable regardless of engine or version — the builder never refuses a feature because your engine is the wrong flavour or too old. Engine and version are inputs to the opt-in validation pass below, which reports (never rewrites or blocks) what a target cannot express. This is also why the PostgreSQL builder carries no version: it models a single engine, so it needs no target at all.

Rendering is determined by construction

Rendering never branches on a dialect flag. What you build is exactly what is rendered — so the engine-divergent constructs are reached by building the engine's own form, not by toggling a mode:

Building without a target renders precisely what you constructed and never fails on dialect grounds.

Opt-in target validation

To check a query against a specific engine (and, optionally, version), opt in with withValidateTarget(). Each divergent construct reports itself while rendering, so you get a QueryBuilderException naming what the target cannot express:

Target::mysql($version) / Target::mariaDb($version) carry an optional version. Version-gated features are checked only when a version is supplied — a leading WITH on UPDATE/DELETE is valid on MySQL and on MariaDB 12.3+, so it passes against Target::mariaDb('12.3') but fails against Target::mariaDb('11.4'). A target with no version only checks the dialect.

Worked per-engine variants for each divergent construct are in the Examples below.

Examples

How to read these examples. Unless a snippet is labelled for a specific engine, the PHP builds identically on both facades — import PostgreSQL\Q or MySQL\Q as Q. Only the rendered SQL differs by dialect ($1 vs ?, identifier quoting, true vs TRUE, lower- vs upper-case function names). Divergent constructs show a snippet per engine. The builder emits compact, single-line SQL; the SQL below is formatted for readability.

Jump to:

• Basic queries · Joins · Aggregation & grouping · Window functions · JSON · Arrays (PostgreSQL) · Subqueries · CTEs (WITH) · INSERT & upsert · UPDATE · DELETE · Functions & operators · Locking

Basic queries

SELECT with WHERE

Multiple conditions

PostgreSQL also has ->ilike() for case-insensitive matching; MySQL/MariaDB use ->like() (case-insensitivity follows the column collation) or ->regexp().

DISTINCT

ORDER BY, LIMIT and OFFSET

NULLS FIRST / NULLS LAST (->nullsLast()) is PostgreSQL-only.

Joins

join(), leftJoin(), rightJoin(), crossJoin() are shared; alias with ->as() and constrain with ->on(...) or ->using('col').

LATERAL join

Supported by PostgreSQL and MySQL — MariaDB has no LATERAL.

fromLateral(), leftJoinLateral() and crossJoinLateral() are also available. Within the MySQL family, LATERAL is MySQL-only — validating against Target::mariaDb() reports "LATERAL requires MySQL".

Aggregation & grouping

ROLLUP

The engines spell super-aggregate grouping differently:

PostgreSQL also supports ->groupingSets(...) and ->cube(...).

Window functions

Aggregate and window functions carry ->over() (inline) or ->over('w') (named), refined with ->partitionBy(...), ->orderBy(...) and frame clauses.

Named windows

Frames

MariaDB additionally offers distribution aggregates — Q\Func::median(), Q\Func::percentileCont() / percentileDisc() with ->withinGroup() — which validate against Target::mariaDb() only.

JSON

Both families build hierarchical data in the database, but with each engine's own function set.

Build a JSON object

Both families build objects from key/value properties with a ->prop() builder, under each dialect's own function name:

The builder keeps insertion order, and ->propIf($cond, 'key', $value) / ->applyIf(...) / ->unset('key') let you shape the object incrementally:

Property keys are string literals; for a computed key, drop to the Q::func('json_build_object'|'JSON_OBJECT', ...) escape hatch.

JSON-first query (selectJson)

When a query's primary output is a single JSON object, Q::selectJson($obj) makes it the first select element; refine it later with applySelectJson() and name it with ->as(). Both families support it — pass the family's own object builder.

Aggregate rows into a JSON array

JSON path access — MySQL family

The -> / ->> operators validate against Target::mysql() only; the JSON_EXTRACT / JSON_UNQUOTE function form is portable across both engines.

JSON_TABLE — MySQL family

Q::jsonTable(doc, path) is a FROM-clause table function; define its columns with ->columns(closure). ->column() opens a value column and ->path() gives its JSON path; ->forOrdinality() / ->existsPath() pick the other leaf forms, the miss handlers (->defaultOnEmpty() / ->nullOnError() / …) attach, and ->nested()->path()->columns() recurses.

Arrays (PostgreSQL)

Native arrays are a PostgreSQL feature.

Subqueries

EXISTS and IN

IN with bound arguments

ANY / ALL

CTEs (WITH)

Q::withRecursive('t')->columnNames(...)->as(...) builds recursive CTEs, and ->appendWith(...) chains several. On PostgreSQL a WITH precedes any statement; on the MySQL family a leading WITH before UPDATE/DELETE is MySQL-only (and MariaDB 12.3+):

INSERT & upsert

The basic INSERT surface is shared: ->columnNames(...), ->values(...) (repeat for multiple rows), ->setMap([...]), and ->query(...) to insert from a SELECT.

Upsert

The engines model conflict handling differently:

->as('new') is MySQL-only (reported against MariaDB). The MySQL family also has Q::insertInto(...)->ignore() (INSERT IGNORE) and Q::replaceInto(...) (a REPLACE statement with the same surface).

RETURNING

UPDATE

Joining another table is spelled per family — PostgreSQL uses UPDATE ... FROM, the MySQL family uses a multi-table JOIN:

On the MySQL family, ->orderBy() / ->limit() are available on single-table UPDATE only; combining them with a join raises a QueryBuilderException when the query is built.

DELETE

Joining is DELETE ... USING on PostgreSQL and a multi-table JOIN on the MySQL family:

Functions & operators

CASE

Casts

Scalar functions

Each family exposes its own curated function set via Q\Func:

Anything not on Q\Func is reachable through the raw escape hatch Q::func('NAME', ...args).

Locking

->forUpdate() (optionally ->nowait() / ->skipLocked()) is shared. The shared lock diverges within the MySQL family:

->of(...) is MySQL-only even on FOR UPDATE; validating a query that uses it against Target::mariaDb() reports it.

Parameters

Positional parameters

Each Q::arg() becomes a placeholder in order of appearance — $1, $2, … on PostgreSQL, ? on the MySQL family:

Named parameters

Q::bind() declares a named placeholder; bind the values with withNamedArgs():

On PostgreSQL a reused name reuses its $n placeholder. On the MySQL family a ? placeholder is not reusable, so each occurrence of a name emits its own ?, each bound to the same value. Named and positional parameters can be mixed.

Validation & errors

By default the builder validates while rendering; problems are collected and thrown together as one QueryBuilderException from toSql(). There are three mechanisms:

• Advisory value checks — a suspect value or modifier in an otherwise well-formed statement: an invalid identifier or cast type, an empty CASE, a DISTINCT on an aggregate whose grammar rejects it. These throw when built but still render under Q::build($q)->withoutValidation()->toSql() — the escape hatch for callers who know better.

• Mutually-exclusive builder state — two options that cannot coexist in one statement (e.g. setting both values and a query on an INSERT, or ORDER BY/LIMIT on a multi-table UPDATE/DELETE). This is builder-API misuse, so it always throws, even with validation disabled.

• Target validation (MySQL family only, opt-in) — Q::build($q)->withValidateTarget(Target::mysql() | mariaDb($version)) reports constructs the target engine/version cannot express. See MySQL & MariaDB.

Executing queries

The builder is driver-agnostic: it produces a SQL string with the dialect's placeholders and a positional argument list. Feed both to any layer that speaks that dialect's placeholders.

PostgreSQL (e.g. the pgsql extension):

MySQL / MariaDB (e.g. PDO):

Best practices

Reuse expressions and base queries

Build queries conditionally with applyIf

Builders expose applyIf() so optional clauses read top-to-bottom without breaking the fluent chain:

Organise complex reports with CTEs

Break a large query into named, readable parts with Q::with() and chain several CTEs with appendWith().

Development

Both must pass for any change.

License

Licensed under the GNU General Public License v3.0 or later.