Query Builder

The query builder writes SQL for the adapter that is connected. The same PHP produces backticks and ? on MySQL, double quotes and $1 on PostgreSQL, double quotes and :name on SQLite — which is the whole point of it, and the reason a hand-written statement stops being portable the moment it needs a parameter. A record class drives this builder underneath; this page drives it directly, which is what you want for a query no findBy() shorthand expresses.

Select#

createSql() on the adapter returns a builder already bound to it. From there select() starts a statement and from() names the table:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select(['id', 'username'])
    ->from('users')
    ->where('id = :id');

On the three adapters that renders as:

-- MySQL
SELECT `id`, `username` FROM `users` WHERE (`id` = ?)

-- PostgreSQL
SELECT "id", "username" FROM "users" WHERE ("id" = $1)

-- SQLite
SELECT "id", "username" FROM "users" WHERE ("id" = :id)

You write :id in every case and the builder emits the placeholder the driver wants. Handing the builder to the adapter runs it, with the parameter array keyed by the name you wrote:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select(['id', 'username'])
    ->from('users')
    ->where('id = :id');

$users = $db->select($sql, ['id' => 1]);

Called with no argument, select() selects everything. An array of column names selects those columns; a string key in that array is an alias, and a value the builder recognizes as a SQL function is left unquoted:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select(['id', 'user' => 'username', 'total' => 'COUNT(*)'])
    ->from('users')
    ->distinct();

On MySQL that renders:

SELECT DISTINCT `id`, `username` AS `user`, COUNT(*) AS `total` FROM `users`

An array passed to from() aliases the table the same way — from(['u' => 'users']) gives FROM `users` AS `u` .

Capture the rendered SQL in a variable if you need it twice — render() clears the statement after building the string, and echo $sql; calls it through __toString().

use Pop\Db\Record;

$db = Record::getDb();

$sql    = $db->createSql();
$select = $sql->select(['id', 'username'])->from('users')->where('id = :id');

echo $select;                            // safe to read
echo $select;                            // and to read again

$users = $db->select($sql, ['id' => 1]); // still runnable

Insert, Update and Delete#

The other three verbs replace the select on the same builder object. values() takes a column => value map, and a value written as :name becomes a bound parameter:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->insert('users')->values([
    'username' => ':username',
    'email'    => ':email'
]);

$db->insert($sql, ['username' => 'testuser', 'email' => 'testuser@test.com']);

update() takes the same map plus a predicate, and delete() takes a predicate alone:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->update('users')->values(['email' => ':email'])->where('id = :id');

$db->update($sql, ['email' => 'new@test.com', 'id' => 1]);

$sql = $db->createSql();
$sql->delete('users')->where('id = :id');

$db->delete($sql, ['id' => 1]);

On MySQL those render as UPDATE ... SET ... = ? WHERE (... = ?) and DELETE FROM ... WHERE (... = ?), with $1/$2 on PostgreSQL and :email/:id on SQLite. The parameter array is keyed by name on every adapter; the positional forms are the builder's problem, not yours.

An insert can also be an upsert. onConflict() takes the columns to overwrite when the row already exists, plus the column the conflict is detected on:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->insert('users')
    ->values(['id' => ':id', 'username' => ':username', 'email' => ':email'])
    ->onConflict(['username', 'email'], 'id');

$db->insert($sql, ['id' => 1, 'username' => 'testuser', 'email' => 'testuser@test.com']);

That renders ON DUPLICATE KEY UPDATE on MySQL and ON CONFLICT ("id") DO UPDATE SET on PostgreSQL and SQLite, and inserts-or-updates correctly on all three. onDuplicateKeyUpdate() is the same call without the conflict column — it's MySQL's spelling, and it's MySQL-only in practice: on PostgreSQL and SQLite the missing column makes rendering fail with Pop\Db\Sql\AbstractSql::quoteId(): Argument #1 ($identifier) must be of type string, null given. Use onConflict() with both arguments and the statement travels.

Joins#

The join methods take the foreign table and an array mapping the two columns being matched:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select(['users.username', 'user_info.city'])
    ->from('users')
    ->leftJoin('user_info', ['user_info.user_id' => 'users.id'])
    ->where('users.id < :id');

-- MySQL
SELECT `users`.`username`, `user_info`.`city` FROM `users`
    LEFT JOIN `user_info` ON ((`user_info`.`user_id` = `users`.`id`))
    WHERE (`users`.`id` < ?)

Once a join is in play both tables are in scope, so qualify the columns in select() and in the predicate — an unqualified id is ambiguous to the database, not to the builder. Calling a join method more than once joins more than one table, in the order you called them.

Twelve join methods cover the SQL keywords:

join()'s third argument is checked against that list of keywords. A type outside it does not raise anything — join('orders', [...], 'CROSS JOIN') silently renders a plain JOIN, on all three adapters. Spell the type the way the table does, or call the named method.

The foreign "table" can be a select of its own, which is how you join against an aggregate:

use Pop\Db\Record;

$db = Record::getDb();

$totals = $db->createSql()->select(['user_id', 'order_total' => 'SUM(total)'])
    ->from('orders')
    ->groupBy('user_id');
$totals->setAlias('totals');

$sql = $db->createSql();
$sql->select(['users.username', 'totals.order_total'])
    ->from('users')
    ->leftJoin($totals, ['totals.user_id' => 'users.id']);

The alias is required, and forgetting it is not caught. A joined subquery with no alias renders as LEFT JOIN SELECT "user_id" FROM "orders" ON (...) — unbracketed and unnamed, which no database accepts — and the builder raises nothing on any of the three adapters. The same aliased object works in from(), giving SELECT * FROM (SELECT ...) AS "totals".

Predicates#

where() accepts a SQL expression as a string and parses it into a predicate. Multiple conditions in one string are split on AND and OR:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select()->from('users')->where('id > :id AND logins > :logins');

andWhere() and orWhere() add to an existing predicate with an explicit conjunction, and both take a string or an array of them:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select()
    ->from('users')
    ->where('id > :id')
    ->orWhere('email LIKE :email');

That gives WHERE ((`id` > ?) OR (`email` LIKE ?)) on MySQL. Underneath the string form there is a typed API, reached through the where property rather than the method, and it's the one to use whenever the value is not a placeholder:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select()
    ->from('users')
    ->where->greaterThan('id', 1)->and()->lessThan('logins', 5);

Each method takes the column and the value; and() and or() set the conjunction for whatever comes next. The full set is equalTo(), notEqualTo(), greaterThan(), greaterThanOrEqualTo(), lessThan(), lessThanOrEqualTo(), like(), notLike(), between(), notBetween(), in(), notIn(), isNull() and isNotNull(), plus the exists()/notExists() and json*() families the sections below cover.

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select()->from('users')->where->between('id', 1, 10);

$sql = $db->createSql();
$sql->select()->from('users')->where->in('id', [1, 2, 3]);

$sql = $db->createSql();
$sql->select()->from('users')->where->isNotNull('email');

Those render BETWEEN 1 AND 10, IN (1, 2, 3) and IS NOT NULL. A literal value goes through the adapter's own quoting rather than being bound. The two styles mix on one statement: a where() string carrying :logins alongside where->equalTo('failed', 0) produces WHERE ((`logins` > ?) AND (`failed` = 0)), and the parameter array covers only the placeholder.

having(), andHaving() and orHaving() mirror the three where methods, and having is a property in the same way where is. groupBy() takes a column, a comma-separated string of them, or an array.

Write a HAVING clause with Pop\Db\Db::executeSql() or a hand-written statement when you also need GROUP BY; the builder renders HAVING before GROUP BY.

Nested Predicates#

nest() opens a sub-group inside the current predicate, so everything added after it is bracketed together:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select()
    ->from('users')
    ->where->greaterThan('id', ':id')
        ->nest()->greaterThan('logins', ':logins')
            ->or()->lessThanOrEqualTo('failed', ':failed');

-- MySQL
SELECT * FROM `users` WHERE ((`id` > ?) AND ((`logins` > ?) OR (`failed` <= ?)))

nest() joins the group to what came before with AND. andNest() and orNest() say so explicitly, and orNest() is the one worth reaching for by name — the difference between a AND (b OR c) and a OR (b AND c) is the difference between two quite different queries, and it's decided entirely by which of the three you call.

Inside the group, and() and or() behave as they do at the top level: they set the conjunction for the next predicate added, not for the group as a whole. A group is closed by the end of the chain rather than by a method, so to add another top-level condition after one, build the top-level conditions first and nest last.

Subqueries#

New in v7: the IN/NOT IN predicates and the six scalar comparisons take a Pop\Db\Sql\Select where they would otherwise take an array or a value. The subquery renders inline, in the dialect of the same adapter:

use Pop\Db\Record;

$db = Record::getDb();

$subquery = $db->createSql()->select('user_id')->from('orders');
$subquery->where->greaterThanOrEqualTo('total', 100);

$sql = $db->createSql();
$sql->select()
    ->from('users')
    ->where->in('id', $subquery);

-- MySQL
SELECT * FROM `users` WHERE (`id` IN (SELECT `user_id` FROM `orders` WHERE (`total` >= 100)))

notIn() gives NOT IN (SELECT ...). The scalar predicates — equalTo(), notEqualTo(), greaterThan(), greaterThanOrEqualTo(), lessThan(), lessThanOrEqualTo() — take one the same way, producing col = (SELECT ...), so the subquery has to return a single value:

use Pop\Db\Record;

$db = Record::getDb();

$subquery = $db->createSql()->select('MAX(user_id)')->from('orders');

$sql = $db->createSql();
$sql->select()
    ->from('users')
    ->where->equalTo('id', $subquery);

The shorthand array syntax a record class takes accepts the same objects, so a subquery reaches findBy() without dropping to the builder for the outer query:

use App\Table\Users;
use Pop\Db\Record;

$db = Record::getDb();

$subquery = $db->createSql()->select('user_id')->from('orders');
$subquery->where->greaterThanOrEqualTo('total', 100);

$buyers = Users::findBy(['id' => ['IN', $subquery]]);

Two constraints decide whether a subquery is usable at all, and both were confirmed by running them.

Its conditions must be literal values, not placeholders. A subquery is rendered to a string before the outer statement is prepared, so its placeholders are not part of the outer statement's parameter binding. Write $subquery->where->greaterThanOrEqualTo('total', 100), not $subquery->where('total >= :total'). MySQL and SQLite survive the mistake — the inner placeholder ends up in the outer statement's list and the parameter array happens to line up — but PostgreSQL does not: inner and outer both number from $1, the two collide, and the query fails with pg_fetch_array(): Argument #1 ($result) must be of type PgSql\Result, false given.

It cannot be correlated to the outer query. There is no way to express "this column of the outer row" — every value handed to a predicate is treated as a literal, so $subquery->where->equalTo('orders.user_id', 'users.id') renders `orders`.`user_id` = 'users.id', comparing the column to the string. MySQL and SQLite return zero rows without complaint; PostgreSQL raises invalid input syntax for type integer: "users.id". Write a join instead, which is what a correlated subquery usually wants to be anyway.

A Select used as a subquery value carries no alias — setAlias() renders (SELECT ...) AS alias, which is valid only in a FROM or JOIN. The alias is what separates the two uses of the same object.

EXISTS Predicates#

Also new in v7, exists() and notExists() test whether a subquery returns any rows at all. They take the Select and nothing else — there's no column argument, because EXISTS compares nothing:

use Pop\Db\Record;

$db = Record::getDb();

$subquery = $db->createSql()->select('id')->from('orders');
$subquery->where->greaterThanOrEqualTo('total', 300);

$sql = $db->createSql();
$sql->select()->from('users')->where->exists($subquery);

-- MySQL
SELECT * FROM `users` WHERE (EXISTS (SELECT `id` FROM `orders` WHERE (`total` >= 300)))

notExists() renders NOT EXISTS (...). Both compose with the rest of the predicate API, so an EXISTS sits inside a nest or beside an ordinary comparison like anything else.

The shorthand array syntax reserves two top-level keys for them, in the same way it reserves OR and AND. The value is the Select:

use App\Table\Users;
use Pop\Db\Record;

$db = Record::getDb();

$subquery = $db->createSql()->select('id')->from('orders');
$subquery->where->greaterThanOrEqualTo('total', 300);

$users = Users::findBy(['EXISTS' => $subquery]);
$none  = Users::findBy(['NOT EXISTS' => $subquery]);

Because 'EXISTS' and 'NOT EXISTS' are reserved keys, a column genuinely named EXISTS cannot be addressed through the shorthand array at all — build that predicate on the query builder instead.

exists() subqueries are non-correlated, so write a correlated EXISTS — "users who have an order" — as a join, or as a hand-written statement.

Querying JSON Columns#

jsonExtract() on the builder reads a value out of a JSON column by path, and returns an expression object rather than a string. That object can be a select column, an orderBy() or a groupBy():

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select(['id', 'extracted_name' => $sql->jsonExtract('data', '$.name')])
    ->from('users');

The path is always written in MySQL's JSONPath spelling — '$.name', '$.address.city', '$.tags[0]' — and each adapter renders its own equivalent:

-- MySQL
SELECT `id`, JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.name')) AS `extracted_name` FROM `users`

-- PostgreSQL
SELECT "id", "data"->>'name' AS "extracted_name" FROM "users"

-- SQLite
SELECT "id", json_extract("data", '$.name') AS "extracted_name" FROM "users"

PostgreSQL parses the path into its own segment form, so a nested path comes out as "data"#>>'{address,city}'. All three return the same value for the same row.

Filtering is jsonEqualTo() and jsonNotEqualTo(), which take the column, the path and a scalar:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select()
    ->from('users')
    ->where->jsonEqualTo('data', '$.role', 'admin');

-- MySQL
SELECT * FROM `users` WHERE (JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.role')) = 'admin')

-- PostgreSQL
SELECT * FROM "users" WHERE ("data"->>'role' = 'admin')

-- SQLite
SELECT * FROM "users" WHERE (json_extract("data", '$.role') = 'admin')

jsonContains() asks whether the array at a path holds a given value. It renders JSON_CONTAINS() on MySQL and the #> / @> jsonb operators on PostgreSQL:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select()
    ->from('users')
    ->where->jsonContains('data', '$.roles', 'admin');

jsonContains() is MySQL and PostgreSQL only; neither SQLite nor SQL Server has a native JSON containment operator.

All three predicates are reachable from the record shorthand through a 'column->$.path' key, with =, != and CONTAINS as the operators:

use App\Table\Users;

$admins  = Users::findBy(['data->$.role'  => 'admin']);
$others  = Users::findBy(['data->$.role'  => ['!=', 'admin']]);
$editors = Users::findBy(['data->$.roles' => ['CONTAINS', 'editor']]);

A bare value with no operator is an equality match, the same as any other shorthand column.

PostgreSQL's JSON extraction yields text and will not compare it to a number implicitly, so jsonEqualTo() renders a quoted literal there: "data"->>'n' = '5'. That makes 5 and 5.0 equal on MySQL and SQLite and unequal on PostgreSQL. jsonContains() encodes its candidate into the query verbatim rather than binding it.

Sorting, Limits and Offsets#

orderBy() takes a column and a direction, groupBy() takes a column, and limit() and offset() take integers:

use Pop\Db\Record;

$db = Record::getDb();

$sql = $db->createSql();
$sql->select()
    ->from('users')
    ->orderBy('id', 'DESC')
    ->limit(10)
    ->offset(20);

That renders ORDER BY `id` DESC LIMIT 10 OFFSET 20 on MySQL, and the same with double quotes on PostgreSQL and SQLite. orderBy() also takes an array of columns or a comma-separated string of them, but the direction is a single argument appended once at the end — orderBy(['logins', 'id'], 'DESC') gives ORDER BY `logins`, `id` DESC, which in SQL means logins ASC, id DESC. Per-column directions need one call per column.

limit() and offset() are typed int, so a string like '10, 20' is a TypeError rather than a MySQL-style limit clause.

Pair offset() with a limit() — only PostgreSQL accepts a bare OFFSET.

orderBy() quotes what it is given as an identifier, so pass a column name. For a SQL function, order in a hand-written statement instead.

See Also#

• Querying — running a built statement, and what the adapter gives back
• Records & the ORM — the shorthand array syntax the subquery and JSON sections hook into
• Schema Builder — the same idea applied to DDL: CREATE TABLE and ALTER TABLE
• Connecting & Adapters — the adapter whose dialect the builder renders
• pop-db README — the full builder API surface