Query Object Builder

A fluent, immutable, fully-typed SQL query builder for PHP 8.4+ with extensive support for PostgreSQL-specific features.

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.

Why Query Object Builder?

Pure PostgreSQL focus — no compromises for lowest-common-denominator multi-database support.
JSON-first design — first-class support for json_build_object and json_agg to build hierarchical data directly in the database.
Complete feature set — CTEs, window functions, arrays, grouping sets, ON CONFLICT, RETURNING, set-returning 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.

Requirements

PHP 8.4 or newer

Installation

composer require flowpack/query-object-builder

Quick start

use Flowpack\QueryObjectBuilder\PostgreSQL\Q;

$active = true;

$q = Q::select(Q::n('name'), Q::n('email'))
    ->from(Q::n('users'))
    ->where(Q::n('active')->eq(Q::arg($active)))
    ->orderBy(Q::n('name'));

[$sql, $args] = Q::build($q)->toSql();

echo $sql;             // SELECT name,email FROM users WHERE active = $1 ORDER BY name
var_dump($args);       // [true]

Q::build($q)->toSql() returns a [$sql, $args] pair: a SQL string with PostgreSQL numbered placeholders ($1, $2, …) 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 a small set of 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()
Identifiers: Q::n('table.column') for names/columns
Literals: Q::string(), Q::int(), Q::float(), Q::bool(), Q::null(), Q::default(), Q::array(), Q::interval()
Parameters: Q::arg() (positional), Q::bind() (named)
Composition: Q::and(), Q::or(), Q::not(), Q::exists(), Q::case(), Q::coalesce(), Q::func(), Q::agg()

The `Q\Func` facade

SQL functions live on the Q\Func facade: Q\Func::jsonBuildObject(), Q\Func::jsonAgg(), Q\Func::count(), Q\Func::sum(), Q\Func::upper(), Q\Func::rowNumber(), Q\Func::unnest(), and many more. It is named Func (not Fn) because fn is a reserved keyword in PHP.

Immutability

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

$base = Q::select(Q::n('*'))->from(Q::n('users'));

$active = $base->where(Q::n('active')->eq(Q::bool(true)));
$recent = $base->where(Q::n('created_at')->gt(Q::string('2024-01-01')));
// $base is unchanged

Operators on expressions

Expressions returned by Q::n(), Q::arg(), literals, and functions carry the SQL operators as fluent methods: ->eq(), ->neq(), ->lt(), ->gt(), ->like(), ->ilike(), ->in(), ->isNull(), ->isNotNull(), ->plus(), ->minus(), ->mult(), ->concat(), ->cast(), ->op('*', …), and more. Parentheses are added automatically based on operator precedence.

Examples

The builder emits compact, single-line SQL. The SQL shown below is formatted for readability — it is otherwise exactly what each query renders.

Basic queries

Simple SELECT

$q = Q::select(Q::n('*'))->from(Q::n('users'));

SELECT * FROM users

SELECT with WHERE

$q = Q::select(Q::n('name'), Q::n('email'))
    ->from(Q::n('users'))
    ->where(Q::n('active')->eq(Q::bool(true)));

SELECT name, email FROM users WHERE active = true

SELECT with multiple conditions

$q = Q::select(Q::n('*'))
    ->from(Q::n('employees'))
    ->where(Q::and(
        Q::or(
            Q::n('firstname')->ilike(Q::arg('John%')),
            Q::n('lastname')->ilike(Q::arg('John%')),
        ),
        Q::n('active')->eq(Q::bool(true)),
    ));

SELECT * FROM employees
WHERE (firstname ILIKE $1 OR lastname ILIKE $2) AND active = true

SELECT DISTINCT

$q = Q::select()->distinct()
    ->select(Q::n('department'))
    ->from(Q::n('employees'));

SELECT DISTINCT department FROM employees

SELECT with ORDER BY, LIMIT and OFFSET

$q = Q::select(Q::n('name'), Q::n('salary'))
    ->from(Q::n('employees'))
    ->orderBy(Q::n('salary'))->desc()->nullsLast()
    ->limit(Q::int(10))
    ->offset(Q::int(20));

SELECT name, salary FROM employees
ORDER BY salary DESC NULLS LAST
LIMIT 10 OFFSET 20

CRUD operations

INSERT with VALUES

$q = Q::insertInto(Q::n('users'))
    ->columnNames('name', 'email', 'active')
    ->values(Q::string('John Doe'), Q::string('john@example.com'), Q::bool(true));

INSERT INTO users (name, email, active)
VALUES ('John Doe', 'john@example.com', true)

INSERT multiple rows

$q = Q::insertInto(Q::n('products'))
    ->columnNames('name', 'price', 'category')
    ->values(Q::string('Laptop'), Q::float(999.99), Q::string('Electronics'))
    ->values(Q::string('Book'), Q::float(19.99), Q::string('Literature'));

INSERT INTO products (name, price, category) VALUES
    ('Laptop', 999.99, 'Electronics'),
    ('Book', 19.99, 'Literature')

INSERT from a map of values

$q = Q::insertInto(Q::n('films'))
    ->setMap([
        'code' => 'UA502',
        'title' => 'Bananas',
        'did' => 105,
    ]);

INSERT INTO films (code,did,title) VALUES ($1, $2, $3)
-- args: ['UA502', 105, 'Bananas']

INSERT from a SELECT

$q = Q::insertInto(Q::n('archived_users'))
    ->query(Q::select(Q::n('*'))->from(Q::n('users'))->where(Q::n('active')->eq(Q::bool(false))));

INSERT INTO archived_users SELECT * FROM users WHERE active = false

INSERT with RETURNING

$q = Q::insertInto(Q::n('users'))
    ->columnNames('name', 'email')
    ->values(Q::string('Jane Doe'), Q::string('jane@example.com'))
    ->returning(Q::n('id'), Q::n('created_at'));

INSERT INTO users (name, email) VALUES ('Jane Doe', 'jane@example.com')
RETURNING id, created_at

UPSERT (INSERT … ON CONFLICT)

$q = Q::insertInto(Q::n('distributors'))
    ->columnNames('did', 'dname')
    ->values(Q::int(5), Q::string('Gizmo Transglobal'))
    ->onConflict(Q::n('did'))->doUpdate()
    ->set('dname', Q::n('EXCLUDED.dname'));

INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname

UPDATE

$q = Q::update(Q::n('films'))
    ->set('kind', Q::string('Dramatic'))
    ->where(Q::n('kind')->eq(Q::string('Drama')));

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama'

UPDATE with FROM

$q = Q::update(Q::n('employees'))->as('e')
    ->set('department_name', Q::n('d.name'))
    ->from(Q::n('departments'))->as('d')
    ->where(Q::n('e.department_id')->eq(Q::n('d.id')));

UPDATE employees AS e SET department_name = d.name
FROM departments AS d
WHERE e.department_id = d.id

DELETE

$q = Q::deleteFrom(Q::n('films'))
    ->where(Q::n('kind')->neq(Q::string('Musical')));

DELETE FROM films WHERE kind <> 'Musical'

DELETE with USING

$q = Q::deleteFrom(Q::n('films'))
    ->using(Q::n('producers'))
    ->where(Q::and(
        Q::n('producer_id')->eq(Q::n('producers.id')),
        Q::n('producers.name')->eq(Q::string('foo')),
    ));

DELETE FROM films USING producers
WHERE producer_id = producers.id AND producers.name = 'foo'

Joins

INNER JOIN

$q = Q::select(Q::n('u.name'), Q::n('p.title'))
    ->from(Q::n('users'))->as('u')
    ->join(Q::n('posts'))->as('p')->on(Q::n('u.id')->eq(Q::n('p.user_id')));

SELECT u.name, p.title FROM users AS u
JOIN posts AS p ON u.id = p.user_id

LEFT JOIN

$q = Q::select(Q::n('u.name'), Q::n('p.title'))
    ->from(Q::n('users'))->as('u')
    ->leftJoin(Q::n('posts'))->as('p')->on(Q::n('u.id')->eq(Q::n('p.user_id')));

SELECT u.name, p.title FROM users AS u
LEFT JOIN posts AS p ON u.id = p.user_id

JOIN with USING

$q = Q::select(Q::n('u.name'), Q::n('p.title'))
    ->from(Q::n('users'))->as('u')
    ->join(Q::n('posts'))->as('p')->using('user_id');

SELECT u.name, p.title FROM users AS u
JOIN posts AS p USING (user_id)

Aggregation & grouping

GROUP BY with an aggregate

$q = Q::select(Q::n('department'))
    ->select(Q\Func::count(Q::n('*')))->as('employee_count')
    ->from(Q::n('employees'))
    ->groupBy(Q::n('department'));

SELECT department, count(*) AS employee_count
FROM employees
GROUP BY department

GROUP BY with HAVING

$q = Q::select(Q::n('department'))
    ->select(Q\Func::avg(Q::n('salary')))->as('avg_salary')
    ->from(Q::n('employees'))
    ->groupBy(Q::n('department'))
    ->having(Q\Func::avg(Q::n('salary'))->gt(Q::int(50000)));

SELECT department, avg(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg(salary) > 50000

GROUP BY ROLLUP

$q = Q::select(Q::n('department'), Q::n('job_title'), Q\Func::sum(Q::n('salary')))
    ->from(Q::n('employees'))
    ->groupBy()
    ->rollup(
        Q::exps(Q::n('department')),
        Q::exps(Q::n('job_title')),
    );

SELECT department, job_title, sum(salary)
FROM employees
GROUP BY ROLLUP (department, job_title)

GROUP BY GROUPING SETS

$q = Q::select(Q::n('department'), Q::n('job_title'), Q\Func::sum(Q::n('salary')))
    ->from(Q::n('employees'))
    ->groupBy()
    ->groupingSets(
        Q::exps(Q::n('department')),
        Q::exps(Q::n('job_title')),
        Q::exps(),
    );

SELECT department, job_title, sum(salary)
FROM employees
GROUP BY GROUPING SETS (department, job_title, ())

Window functions

ROW_NUMBER over a partition

$q = Q::select(
    Q::n('name'),
    Q::n('salary'),
    Q\Func::rowNumber()->over()->partitionBy(Q::n('department'))->orderBy(Q::n('salary'))->desc(),
)->from(Q::n('employees'));

SELECT name, salary,
       row_number() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees

Named windows

$q = Q::select(
    Q\Func::sum(Q::n('salary'))->over('w'),
    Q\Func::avg(Q::n('salary'))->over('w'),
)
    ->from(Q::n('empsalary'))
    ->window('w')->as()->partitionBy(Q::n('depname'))->orderBy(Q::n('salary'))->desc();

SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC)

JSON operations

Build a JSON object

$q = Q::select(
    Q\Func::jsonBuildObject()
        ->prop('id', Q::n('id'))
        ->prop('name', Q::n('name'))
        ->prop('email', Q::n('email')),
)->from(Q::n('users'));

SELECT json_build_object('id', id, 'name', name, 'email', email)
FROM users

JSON aggregation

$q = Q::select(
    Q::n('department'),
    Q\Func::jsonAgg(
        Q\Func::jsonBuildObject()
            ->prop('name', Q::n('name'))
            ->prop('salary', Q::n('salary')),
    )->orderBy(Q::n('name')),
)
    ->from(Q::n('employees'))
    ->groupBy(Q::n('department'));

SELECT department,
       json_agg(json_build_object('name', name, 'salary', salary) ORDER BY name)
FROM employees
GROUP BY department

`selectJson` for a JSON-first query

When the query's primary output is a single JSON object, Q::selectJson() makes it the first selection and lets you refine it later with applySelectJson():

$q = Q::selectJson(
    Q\Func::jsonBuildObject()
        ->prop('Title', Q::n('books.title'))
        ->prop('ID', Q::n('books.book_id')),
)
    ->from(Q::n('books'))
    ->where(Q::n('books.book_id')->eq(Q::arg(2)));

SELECT json_build_object('Title', books.title, 'ID', books.book_id)
FROM books
WHERE books.book_id = $1

Array operations

Array construction

$q = Q::select(Q::array(Q::string('a'), Q::string('b'), Q::string('c')));

SELECT ARRAY['a','b','c']

Array functions

$q = Q::select(
    Q\Func::arrayAppend(Q::array(Q::int(1), Q::int(2)), Q::int(3)),
    Q\Func::arrayLength(Q::array(Q::int(1), Q::int(2), Q::int(3)), Q::int(1)),
);

SELECT array_append(ARRAY[1,2], 3), array_length(ARRAY[1,2,3], 1)

UNNEST

$q = Q::select(Q::n('*'))
    ->from(Q\Func::unnest(Q::array(Q::string('a'), Q::string('b'), Q::string('c'))))
    ->as('t')->columnAliases('value');

SELECT * FROM unnest(ARRAY['a','b','c']) AS t (value)

Array aggregation

$q = Q::select(
    Q::n('department'),
    Q\Func::arrayAgg(Q::n('name'))->orderBy(Q::n('name')),
)
    ->from(Q::n('employees'))
    ->groupBy(Q::n('department'));

SELECT department, array_agg(name ORDER BY name)
FROM employees
GROUP BY department

Subqueries

EXISTS

$q = Q::select(Q::n('name'))
    ->from(Q::n('users'))
    ->where(Q::exists(
        Q::select(Q::int(1))
            ->from(Q::n('posts'))
            ->where(Q::n('posts.user_id')->eq(Q::n('users.id'))),
    ));

SELECT name FROM users
WHERE EXISTS (SELECT 1 FROM posts WHERE posts.user_id = users.id)

IN with a subquery

$q = Q::select(Q::n('name'))
    ->from(Q::n('users'))
    ->where(Q::n('id')->in(
        Q::select(Q::n('user_id'))
            ->from(Q::n('posts'))
            ->where(Q::n('published')->eq(Q::bool(true))),
    ));

SELECT name FROM users
WHERE id IN (SELECT user_id FROM posts WHERE published = true)

IN with bound arguments

$ids = [1, 2, 3];

$q = Q::select(Q::n('username'))
    ->from(Q::n('accounts'))
    ->where(Q::n('id')->in(Q::args(...$ids)));

SELECT username FROM accounts WHERE id IN ($1, $2, $3)
-- args: [1, 2, 3]

Correlated subquery

$q = Q::select(Q::n('name'), Q::n('salary'))
    ->from(Q::n('employees'))->as('e1')
    ->where(Q::n('salary')->gt(
        Q::select(Q\Func::avg(Q::n('salary')))
            ->from(Q::n('employees'))->as('e2')
            ->where(Q::n('e1.department')->eq(Q::n('e2.department'))),
    ));

SELECT name, salary FROM employees AS e1
WHERE salary > (
    SELECT avg(salary) FROM employees AS e2
    WHERE e1.department = e2.department
)

Subquery in FROM

$q = Q::select(Q::n('avg_quantity'))
    ->from(
        Q::select(Q\Func::avg(Q::n('quantity')))->as('avg_quantity')
            ->from(Q::n('sales'))
            ->groupBy(Q::n('brand')),
    )->as('t');

SELECT avg_quantity FROM (
    SELECT avg(quantity) AS avg_quantity FROM sales GROUP BY brand
) AS t

Common Table Expressions (WITH)

Simple CTE

$q = Q::with('recent_orders')->as(
    Q::select(Q::n('*'))
        ->from(Q::n('orders'))
        ->where(Q::n('created_at')->gt(Q::string('2023-01-01'))),
)
    ->select(Q::n('customer_name'), Q\Func::count(Q::n('*')))
    ->from(Q::n('recent_orders'))
    ->groupBy(Q::n('customer_name'));

WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > '2023-01-01'
)
SELECT customer_name, count(*) FROM recent_orders GROUP BY customer_name

Recursive CTE

$q = Q::withRecursive('employee_recursive')
    ->columnNames('distance', 'employee_name', 'manager_name')->as(
        Q::select(Q::int(1), Q::n('employee_name'), Q::n('manager_name'))
            ->from(Q::n('employee'))
            ->where(Q::n('manager_name')->eq(Q::string('Mary')))
            ->union()->all()
            ->select(Q::n('er.distance')->op('+', Q::int(1)), Q::n('e.employee_name'), Q::n('e.manager_name'))
            ->from(Q::n('employee_recursive'))->as('er')
            ->from(Q::n('employee'))->as('e')
            ->where(Q::n('er.employee_name')->eq(Q::n('e.manager_name'))),
    )
    ->select(Q::n('distance'), Q::n('employee_name'))->from(Q::n('employee_recursive'));

WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
    SELECT 1, employee_name, manager_name
    FROM employee
    WHERE manager_name = 'Mary'
  UNION ALL
    SELECT er.distance + 1, e.employee_name, e.manager_name
    FROM employee_recursive AS er, employee AS e
    WHERE er.employee_name = e.manager_name
)
SELECT distance, employee_name FROM employee_recursive

Functions & operators

String functions

$q = Q::select(
    Q\Func::upper(Q::n('name')),
    Q\Func::lower(Q::n('email')),
    Q\Func::initcap(Q::n('title')),
)->from(Q::n('users'));

SELECT upper(name), lower(email), initcap(title)
FROM users

Date/time functions

$q = Q::select(
    Q\Func::extract('year', Q::n('created_at')),
    Q::n('created_at')->plus(Q::interval('1 day')),
)->from(Q::n('orders'));

SELECT EXTRACT(year FROM created_at), created_at + INTERVAL '1 day'
FROM orders

Mathematical operators

$q = Q::select(Q::n('price')->op('*', Q::n('quantity')))->as('total')
    ->from(Q::n('order_items'));

SELECT price * quantity AS total FROM order_items

CASE expressions

$q = Q::select(
    Q::n('name'),
    Q::case()
        ->when(Q::n('salary')->lt(Q::int(30000)))->then(Q::string('Low'))
        ->when(Q::n('salary')->lt(Q::int(70000)))->then(Q::string('Medium'))
        ->else(Q::string('High'))
        ->end(),
)->from(Q::n('employees'));

SELECT name,
       CASE
           WHEN salary < 30000 THEN 'Low'
           WHEN salary < 70000 THEN 'Medium'
           ELSE 'High'
       END
FROM employees

Casts

$q = Q::select(Q::n('articles.content')->cast('text'))
    ->from(Q::n('articles'))
    ->where(Q::n('articles.content')->cast('text')->ilike(Q::arg('%foo%')));

SELECT articles.content::text FROM articles WHERE articles.content::text ILIKE $1
-- args: ['%foo%']

Parameters

Positional parameters

Each Q::arg() becomes a numbered placeholder in order of appearance:

$q = Q::select(Q::n('*'))
    ->from(Q::n('users'))
    ->where(Q::and(
        Q::n('name')->like(Q::arg('John%')),
        Q::n('active')->eq(Q::arg(true)),
    ));

[$sql, $args] = Q::build($q)->toSql();

SELECT * FROM users WHERE name LIKE $1 AND active = $2
-- args: ['John%', true]

Named parameters

Q::bind() declares a named placeholder; bind the values with withNamedArgs(). Reusing the same name reuses its placeholder:

$q = Q::select(Q::n('*'))
    ->from(Q::n('users'))
    ->where(Q::and(
        Q::n('name')->like(Q::bind('search')),
        Q::n('active')->eq(Q::bind('is_active')),
    ));

[$sql, $args] = Q::build($q)
    ->withNamedArgs(['search' => 'John%', 'is_active' => true])
    ->toSql();

SELECT * FROM users WHERE name LIKE $1 AND active = $2
-- args: ['John%', true]

Named and positional parameters can be mixed in the same query.

Executing queries

The builder is driver-agnostic: it produces a SQL string with PostgreSQL numbered placeholders ($1, $2, …) and a positional argument list. Feed both to any layer that speaks PostgreSQL's native placeholders, for example the pgsql extension:

use Flowpack\QueryObjectBuilder\PostgreSQL\Q;

$conn = pg_connect('host=localhost dbname=app user=app');

$q = Q::select(Q::n('name'), Q::n('email'))
    ->from(Q::n('users'))
    ->where(Q::n('active')->eq(Q::arg(true)));

[$sql, $args] = Q::build($q)->toSql();

$result = pg_query_params($conn, $sql, $args);
while ($row = pg_fetch_assoc($result)) {
    printf("Name: %s, Email: %s\n", $row['name'], $row['email']);
}

By default, identifiers are validated while building and an invalid name throws a QueryBuilderException. Skip validation with Q::build($q)->withoutValidation()->toSql() when you trust the input.

Best practices

Reuse expressions and base queries

$userName  = Q::n('users.name');
$userEmail = Q::n('users.email');

$q = Q::select($userName, $userEmail)->from(Q::n('users'));

Build queries conditionally with `applyIf`

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

$q = Q::update(Q::n('films'))
    ->set('kind', Q::string('Dramatic'))
    ->where(Q::n('kind')->eq(Q::string('Drama')))
    ->applyIf($onlyActive, fn ($q) => $q->where(Q::n('archived')->eq(Q::bool(false))));

Organise complex reports with CTEs

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

Development

composer install

composer test       # run the Pest test suite
composer analyse    # run PHPStan (level max)

Both must pass for any change.

License

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

Name		Name	Last commit message	Last commit date
Latest commit History 15 Commits
.github/workflows		.github/workflows
devbox.d/php		devbox.d/php
src/PostgreSQL		src/PostgreSQL
tests		tests
.envrc		.envrc
.gitignore		.gitignore
AGENTS.md		AGENTS.md
CLAUDE.md		CLAUDE.md
LICENSE		LICENSE
README.md		README.md
composer.json		composer.json
composer.lock		composer.lock
devbox.json		devbox.json
devbox.lock		devbox.lock
phpstan.neon		phpstan.neon
phpunit.xml		phpunit.xml

Uh oh!

Folders and files

Latest commit

History

Repository files navigation