Query Builder

Query Builder

Every method on InitORM\QueryBuilder\QueryBuilderInterface is reachable through a Database via __call. Calls that return the builder are re-wrapped to return the Database, so fluent chains span the wrapper boundary:

$db->select(...)->where(...)->orderBy(...)->read('users');
// ^ select / where / orderBy each return $db (not the builder), so .read() works.

This page tours the builder surface. For the authoritative reference, see the QueryBuilder wiki.

SELECT projection

$db->select('id', 'name', $db->raw('NOW() AS now'));
$db->selectAs('full_name', 'name');
$db->selectCount('id', 'total');
$db->selectMax('score', 'max_score');
$db->selectMin('score', 'min_score');
$db->selectAvg('score', 'avg_score');
$db->selectSum('score', 'sum_score');
$db->selectCountDistinct('email', 'unique_emails');

// String functions
$db->selectUpper('name', 'upper_name');
$db->selectLower('email', 'lower_email');
$db->selectLength('name', 'name_length');
$db->selectMid('name', 1, 3, 'short');     // SUBSTRING(name, 1, 3) — MySQL
$db->selectLeft('name', 3, 'prefix');
$db->selectRight('name', 3, 'suffix');

// Coalesce — default is inlined verbatim when numeric, escaped as identifier otherwise
$db->selectCoalesce('score', 0, 'safe_score');

// Concat
$db->selectConcat(['first_name', $db->raw("' '"), 'last_name'], 'full_name');

// Drop everything you've added so far
$db->clearSelect();

When select() is empty, the SELECT compiles to SELECT *.

FROM / table

$db->from('users');                 // FROM users
$db->from('users', 'u');            // FROM users AS u
$db->addFrom('roles', 'r');         // FROM users AS u, roles AS r
$db->table('users');                // alias-less alternative

from() resets the table list; addFrom() appends.

JOIN

$db->join('roles', 'roles.user_id = users.id', 'LEFT');
$db->innerJoin('roles', 'roles.user_id = users.id');
$db->leftJoin('roles', 'roles.user_id = users.id');
$db->rightJoin('roles', 'roles.user_id = users.id');
$db->leftOuterJoin('roles', 'roles.user_id = users.id');
$db->rightOuterJoin('roles', 'roles.user_id = users.id');
$db->selfJoin('roles', 'roles.user_id = users.id'); // comma-FROM with ON folded into WHERE
$db->naturalJoin('roles');                          // no ON clause

$onStmt can be a string, a RawQuery, or a Closure for complex ON expressions:

$db->leftJoin('roles', function ($qb) {
    $qb->on('roles.user_id', '=', $qb->raw('users.id'))
       ->on('roles.deleted_at', 'IS', null);
});

WHERE / HAVING / ON

The base shape is where(column, operator, value, logical = 'AND'):

$db->where('id', '=', 5);
$db->where('age', '>', 18);
$db->where('email', 'LIKE', '%@example.com');

// 2-arg shortcut — operator defaults to '='
$db->where('id', 5);

// 1-arg form — column is a RawQuery
$db->where($db->raw('score > 50'));

// Logical
$db->where('a', '=', 1)->where('b', '=', 2, 'OR');
$db->andWhere('c', '=', 3);
$db->orWhere('d', '=', 4);

having() and on() mirror this exactly.

IS NULL / IS NOT NULL

$db->whereIsNull('deleted_at');
$db->whereIsNotNull('email');
$db->andWhereIsNull('locked_at');
$db->orWhereIsNotNull('verified_at');

IN / NOT IN

$db->whereIn('id', [1, 2, 3]);
$db->whereNotIn('id', [4, 5]);
$db->orWhereIn('role', ['admin', 'editor']);
$db->orWhereNotIn('role', ['banned']);

Numeric elements are inlined verbatim; strings are parameterized; a RawQuery (e.g. a sub-query) is rendered as-is.

BETWEEN / NOT BETWEEN

$db->between('age', 18, 65);
$db->between('age', [18, 65]); // array form also accepted
$db->notBetween('score', 0, 50);
$db->orBetween('age', 0, 17);
$db->andBetween('score', 90, 100);

LIKE family

$db->like('name', 'john', 'both');    // LIKE '%john%' (default)
$db->like('name', 'john', 'start');   // LIKE 'john%'
$db->like('name', 'john', 'end');     // LIKE '%john'

$db->startLike('email', 'admin');     // shortcut: LIKE 'admin%'
$db->endLike('email', '.com');        // shortcut: LIKE '%.com'

$db->notLike('name', 'spam');         // NOT LIKE '%spam%'
$db->orLike('name', 'eve');
$db->andLike('name', 'al');

All forms have or* / and* siblings (orLike, andStartLike, …).

REGEXP / SOUNDEX / FIND_IN_SET

MySQL-specific helpers:

$db->regexp('name', '^[A-C]');
$db->soundex('name', 'Robert');             // approximate phonetic match
$db->findInSet('tags', 'admin');            // FIND_IN_SET('admin', tags)
$db->notFindInSet('tags', 'banned');

For PostgreSQL/SQLite, drop down to where($db->raw(...)).

Sub-queries and grouping

// Sub-query inside IN
$db->whereIn('user_id', $db->subQuery(function ($qb) {
    $qb->select('id')->from('users')->where('active', '=', 1);
}));
// WHERE user_id IN (SELECT id FROM users WHERE active = :active)

// Grouped conditions
$db->group(function ($qb) {
    $qb->where('a', '=', 1)->orWhere('b', '=', 2);
});
// WHERE (a = :a OR b = :b)

Inside a group() closure, where / having / on buckets are each independently wrapped and folded back into the outer query.

GROUP BY / HAVING / ORDER BY / LIMIT / OFFSET

$db->select('role', $db->raw('COUNT(*) AS n'))
    ->from('users')
    ->groupBy('role')
    ->having('n', '>', 10)
    ->orderBy('n', 'DESC')
    ->limit(5)
    ->offset(10)
    ->read();

groupBy() accepts variadic args or arrays; orderBy() takes 'ASC' or 'DESC' as the second argument (case-insensitive).

INSERT / UPDATE shape (set())

set() appends to the SET bucket. Multiple calls = multi-row batch shape.

// Single row, array form
$db->from('users')->set(['name' => 'Eve', 'active' => 1])->create();

// Single row, per-column form
$db->from('users')
    ->set('name', 'Eve')
    ->set('active', 1)
    ->create();

// Multi-row batch
$db->from('users')
    ->set(['name' => 'A', 'email' => 'a@x.com'])
    ->set(['name' => 'B', 'email' => 'b@x.com'])
    ->createBatch();

Inspecting the compiled SQL

When debugging, compile without executing:

$db->select('id')->from('users')->where('active', '=', 1);

$sql    = $db->generateSelectQuery();
$params = $db->getParameter()->all();

echo $sql;
// SELECT id FROM users WHERE active = :active
print_r($params);
// [':active' => 1]

⚠ generate*Query() does not reset the builder — only CRUD execution does. After inspecting, call $db->resetStructure() or a CRUD method to clear state.

All five compile entry points are reachable through __call:

Method	Compiles to
generateSelectQuery()	SELECT … FROM … WHERE …
generateInsertQuery()	Single-row INSERT
generateBatchInsertQuery()	Multi-row INSERT
generateUpdateQuery()	UPDATE … SET … WHERE …
generateUpdateBatchQuery($referenceColumn)	UPDATE … SET … CASE/WHEN …
generateDeleteQuery()	DELETE FROM … WHERE …

Raw fragments

raw() wraps a string in a RawQuery — the builder treats it as opaque and skips escaping:

$db->select($db->raw("CONCAT(name, ' ', surname) AS fullname"))
    ->where($db->raw('score > 50'))
    ->read('users');

Use sparingly; never embed unsanitized user input. See Raw Queries for the safety rules.

See also

• CRUD Operations — the helpers that wrap this builder
• Raw Queries — when the builder isn't expressive enough
• QueryBuilder wiki — the authoritative reference