Skip to content

Pramnos_QueryBuilder_Guide.md

Latest commit

 

History

History
913 lines (677 loc) · 26.4 KB

Pramnos_QueryBuilder_Guide.md

File metadata and controls

913 lines (677 loc) · 26.4 KB
Raw
use_cases
Building a SELECT, INSERT, UPDATE or DELETE with the fluent builder
Converting existing raw SQL to query-builder calls
Joining, grouping or paginating a query
Processing a large result set in batches
Inspecting the SQL a builder chain produces

Pramnos QueryBuilder Guide

The QueryBuilder provides a fluent, dialect-aware interface for constructing SQL queries programmatically. It automatically handles dialect differences between MySQL, PostgreSQL, and TimescaleDB, and supports advanced features like window functions, subqueries, and set operations.

Class: Pramnos\Database\QueryBuilder
Entry point: $db->queryBuilder() — returns a fresh builder bound to the current database connection.

Foundational Concepts

Read/Write Replicas

Applications that scale horizontally typically run one primary database for writes and one or more read replicas for SELECT queries. The Database class maintains separate read and write connections, automatically routing queries based on their type.

Configuration

Add read and write blocks to your settings.php:

'database' => [
    'type'      => 'mysql',
    'write' => [
        'hostname' => 'db-primary.example.com',
        'user'     => 'app_rw',
        'password' => 'secret',
        'database' => 'myapp',
    ],
    'read' => [
        'hostname' => 'db-replica.example.com',
        'user'     => 'app_ro',
        'password' => 'secret',
        'database' => 'myapp',
    ],
    'port'      => 3306,
    'prefix'    => 'pramnos_',
    'collation' => 'utf8mb4_unicode_ci',
]

PostgreSQL / TimescaleDB works identically:

'database' => [
    'type'   => 'postgresql',
    'write'  => ['hostname' => 'pg-primary', 'user' => 'app', 'password' => '...', 'database' => 'myapp'],
    'read'   => ['hostname' => 'pg-replica', 'user' => 'app', 'password' => '...', 'database' => 'myapp'],
    'schema' => 'public',
]

How Routing Works

Database::isWriteQuery(string $sql): bool checks the first SQL keyword. Queries beginning with SELECT, SHOW, EXPLAIN, DESC, or DESCRIBE are treated as reads; everything else as a write.

$db = \Pramnos\Database\Database::getInstance();

// Automatically uses the READ connection
$result = $db->query("SELECT * FROM #PREFIX#users WHERE active = 1");

// Automatically uses the WRITE connection
$db->query("UPDATE #PREFIX#users SET last_login = NOW() WHERE userid = %i", 42);

API Reference

Method Description
getConnection(bool $isWrite = false) Returns the appropriate live connection, reconnecting if needed
isConnectionAlive(mixed $connection): bool Checks if connection handle is open
isWriteQuery(string $sql): bool Returns true if the query's first keyword implies a write operation

BC Note: If read/write config keys are absent, the database behaves as before — a single connection for all queries.

Connection Health & Auto-reconnect

Long-running workers and daemon processes lose database connections when the server closes idle sockets (e.g., MySQL's wait_timeout). Previously this caused silent failures; now Database::query() detects a lost connection and transparently reconnects once before executing.

How It Works

On each query, if the connection is dead, the framework calls tryReconnect() before executing SQL. If reconnect succeeds, the query runs normally. If it fails, the original exception propagates.

API Reference

Method Description
tryReconnect(): bool Non-fatal reconnect. Returns true on success, false on failure
refresh(bool $throwOnFailure = true): bool Full reconnect. Throws RuntimeException on failure if $throwOnFailure is true
isConnectionAlive(mixed $connection): bool Low-level check used internally

Usage

For most applications, reconnect is fully automatic — no code changes needed:

// Normal query — transparently reconnects if connection dropped
$result = $db->query('SELECT * FROM users WHERE active = 1');

For long-running daemons that want to pro-actively verify before a critical operation:

// Non-fatal check (returns bool)
if (!$db->tryReconnect()) {
    $logger->warning('Database unavailable, skipping this cycle');
    sleep(5);
    continue;
}

For workers that should abort on connection failure:

// Throws RuntimeException on failure
$db->refresh(throwOnFailure: true);

DatabaseCapabilities — Runtime Detection

Features like JSONB, TimescaleDB hypertables, and spatial indexes are not available on every backend. DatabaseCapabilities detects the connected server's actual capabilities at runtime and provides a clean API to branch on them.

Class: Pramnos\Database\DatabaseCapabilities

Getting Started

$db   = \Pramnos\Database\Database::getInstance();
$caps = new \Pramnos\Database\DatabaseCapabilities($db);

if ($caps->hasTimescaleDB()) {
    // use time_bucket(), hypertable APIs
} elseif ($caps->isPostgreSQL()) {
    // plain PostgreSQL fallback
} else {
    // MySQL fallback
}

Conditional Execution with ifCapable()

$caps->ifCapable(
    \Pramnos\Database\DatabaseCapabilities::FEATURE_TIMESCALEDB,
    function () use ($db, $table) {
        // runs only on TimescaleDB
        $db->query("SELECT create_hypertable('%s', 'time')", $table);
    },
    function () {
        // runs on all other backends
    }
);

Feature Constants

Constant Value Detected via
FEATURE_TIMESCALEDB 'timescaledb' pg_extension catalog query
FEATURE_JSON 'json' Always true (MySQL 5.7+, all PG versions)
FEATURE_JSONB 'jsonb' PostgreSQL only
FEATURE_FULLTEXT 'fulltext' MySQL only
FEATURE_SPATIAL 'spatial' MySQL with spatial extensions

API Reference

  • has(string $capability): bool — Returns true if capability is supported
  • isMySQL(): bool — Returns true for MySQL
  • isPostgreSQL(): bool — Returns true for PostgreSQL and TimescaleDB
  • hasTimescaleDB(): bool — Returns true only if TimescaleDB extension is loaded
  • ifCapable(string $capability, callable $ifTrue, ?callable $ifFalse = null): mixed — Executes callback based on capability
  • supports(string $capability): bool — Fluent alias for has()

Getting Started

Basic Patterns

$db = \Pramnos\Database\Database::getInstance();

// SELECT with conditions
$activeUsers = $db->queryBuilder()
    ->from('users')
    ->where('active', 1)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

while ($activeUsers->fetch()) {
    echo $activeUsers->fields['username'] . "\n";
}

// INSERT
$db->queryBuilder()
    ->table('users')
    ->insert(['username' => 'jane', 'email' => 'jane@example.com']);

// UPDATE
$db->queryBuilder()
    ->table('users')
    ->where('userid', 5)
    ->update(['active' => 0]);

// DELETE
$db->queryBuilder()
    ->from('users')
    ->where('active', 0)
    ->delete();

SELECT Queries

Column Selection

select(array|string $columns = ['*']): static

Sets the SELECT column list. Accepts individual strings, comma-separated strings, or an array.

// Select specific columns
$qb->select('userid', 'username', 'email');

// Array format with aliases
$qb->select(['u.userid', 'u.username', 'g.groupname']);

// SQL expressions
$qb->select('COUNT(*) as total');

// Raw expressions
$qb->select($qb->raw("TO_CHAR(created_at, 'YYYY-MM') as month"));

distinct(): static

Adds DISTINCT to the SELECT.

$qb->select('country')->distinct()->from('users');
// → SELECT DISTINCT country FROM users

Table & Aliasing

from(string $table): static / table(string $table): static

Sets the FROM table with optional alias.

$qb->from('users');
$qb->from('users u');           // with alias
$qb->from('users AS u');        // explicit AS

// INSERT/UPDATE/DELETE prefer table()
$qb->table('users')->insert([...]);

Table prefixes — write #PREFIX# yourself

The builder does not add the installation's table prefix for you. It substitutes the #PREFIX# token, and only that:

$qb->table('#PREFIX#settings')      // → prefix_settings
$qb->table('settings')              // → settings  (no prefix, ever)

Both forms appear in the framework, because a table that no installation prefixes reads better without the token. But if the table is prefixed — and every table a migration creates through the schema builder is — omitting #PREFIX# produces a query against a name that exists only where the prefix is empty. It works on the developer's machine and finds nothing on the installation that has one.

Rule of thumb: if the raw SQL you are replacing had #PREFIX#, keep it.

Schema-qualified tables

authserver.roles is resolved per driver: a PostgreSQL schema, and a prefix-flattened prefix_authserver_roles on MySQL, which has no schemas. This is one of the reasons hand-written SQL against those tables silently matches nothing — see rule 12 in the project rules.

WHERE Conditions

where(string $column, mixed $operator = null, mixed $value = null): static

Adds a WHERE condition. Supports multiple calling patterns:

// Two-argument: column = value (shorthand)
$qb->where('active', 1);
$qb->where('status', 'pending');

// Three-argument: column operator value
$qb->where('age', '>=', 18);
$qb->where('name', 'ILIKE', '%john%');

// Nested closure (parenthesized group)
$qb->where(function ($q) {
    $q->where('status', 'active')->orWhere('role', 'admin');
});
// → WHERE (status = 'active' OR role = 'admin')

orWhere(...): static`

OR variant. Same calling conventions as where().

$qb->where('role', 'admin')->orWhere('role', 'superuser');
// → WHERE role = 'admin' OR role = 'superuser'

whereIn(string $column, array $values): static

$qb->whereIn('userid', [1, 2, 3]);
// → WHERE userid IN (1, 2, 3)

// Negation
$qb->whereIn('status', ['active', 'pending'], 'and', true);
// → WHERE status NOT IN ('active', 'pending')

whereNull(string $column): static / whereNotNull(string $column): static

$qb->whereNotNull('email');
// → WHERE email IS NOT NULL

whereBetween(string $column, array $values): static

$qb->whereBetween('age', [18, 65]);
// → WHERE age BETWEEN 18 AND 65

whereRaw(string $sql, array $bindings = []): static

Raw WHERE clause for dialect-specific expressions.

$qb->whereRaw("LOWER(username) = %s", ['johndoe']);
$qb->whereRaw("ST_DWithin(geom, ST_MakePoint(%s, %s)::geography, 1000)", [23.72, 37.98]);
$qb->whereRaw("created_at > NOW() - INTERVAL '7 days'");

Placeholders: ? or %s, one per binding. This builder's own placeholders are typed — %s string, %i integer, %d float, %b boolean — and a raw fragment may use them directly. A ? is also accepted and is replaced with the placeholder its binding's type calls for, at the position it was written:

$qb->whereRaw('channel_id IN (SELECT id FROM channels WHERE station_id = ?)', [$id]);
// compiles to … station_id = %i, bound in this clause's own position

A ? inside a quoted string (label = 'why?') is left alone, and a fragment with no bindings is never rewritten — whereRaw('enabled = TRUE') and PostgreSQL's jsonb ? key operator both mean what they say.

A count mismatch throws immediately, from the whereRaw() call itself:

$qb->whereRaw('a = ?', [1, 2]);
// InvalidArgumentException: whereRaw() was given 2 binding(s) for 1 placeholder(s) in: a = ?

That is deliberate, and it replaces a silent failure worth knowing about: a mismatch used to leave a literal ? in the statement with one binding too many, the server rejected it, get()/first() returned false, and the only symptom was Attempt to read property "fields" on false in the calling code, several lines from the cause. A statement that cannot be prepared is now also written to the application error log with its SQL, so the false always has a trail.

orWhereRaw() and orHavingRaw() exist for the OR forms; havingRaw() behaves identically for HAVING.

whereExists(Closure $callback): static

EXISTS subquery condition.

$result = $db->queryBuilder()
    ->from('products')
    ->whereExists(function (\Pramnos\Database\QueryBuilder $sub) {
        $sub->select(['1'])
            ->from('order_items')
            ->whereRaw('order_items.product_id = products.id')
            ->whereRaw("order_items.status = 'pending'");
    })
    ->get();

Joins

join(string $table, string $first, string $operator, string $second, string $type = 'inner'): static

$qb->join('orders o', 'o.userid', '=', 'u.userid');
// → INNER JOIN orders o ON o.userid = u.userid

$qb->join('roles r', 'r.roleid', '=', 'u.roleid', 'left');
// → LEFT JOIN roles r ON r.roleid = u.roleid

leftJoin(...), rightJoin(...), crossJoin(...)

Convenience methods:

$qb->leftJoin('profiles p', 'p.userid', '=', 'u.userid');
$qb->rightJoin('categories c', 'c.id', '=', 'p.category_id');
$qb->crossJoin('colors');  // CROSS JOIN (no ON clause)

joinRaw(string $sql): static

$qb->joinRaw("LEFT JOIN permissions p ON p.userid = u.userid AND p.active = 1");

Ordering & Grouping

orderBy(string $column, string $direction = 'asc'): static

$qb->orderBy('created_at', 'desc');
$qb->orderBy('username');           // defaults to 'asc'
$qb->orderBy('id', 'asc')->orderBy('name', 'asc');  // multiple columns

latest(string $column = 'created_at'): static / oldest(...)

Shortcuts for orderBy(..., 'desc') and orderBy(..., 'asc').

$qb->from('posts')->latest()->limit(10)->get();
// → ORDER BY created_at DESC

groupBy(string|array $columns): static

$qb->groupBy('country');
$qb->groupBy(['country', 'city']);

having(string $column, mixed $operator = null, mixed $value = null): static

Same calling convention as where().

$qb->groupBy('country')->having('count', '>', 100);
// → GROUP BY country HAVING count > 100

Pagination

limit(int $value) / offset(int $value): static

$qb->limit(25);
$qb->offset(50);  // page 3 with limit(25)

forPage(int $page, int $perPage = 15): static

Shorthand for offset(($page - 1) * $perPage)->limit($perPage).

$result = $db->queryBuilder()
    ->from('products')
    ->orderBy('name')
    ->forPage(3, 20)   // page 3, 20 per page
    ->get();

Execution & Results

get(): Result

Compiles and executes the query.

$result = $qb->from('users')->where('active', 1)->get();

first(): Result

Adds LIMIT 1 and executes.

$result = $qb->from('users')->where('username', 'jane')->first();
if ($result->numRows > 0) {
    echo $result->fields['email'];
}

count(): int

Executes a COUNT(*) aggregate.

$total = $qb->from('users')->where('active', 1)->count();

// Pagination example
$qb = $db->queryBuilder()->from('orders')
    ->where('status', 1)
    ->orderBy('created_at', 'desc')
    ->limit(20)
    ->offset(40);

$total = $qb->count();  // Clones internally, strips ORDER BY/LIMIT/OFFSET
$rows  = $qb->get();

Aggregates: sum(), avg(), min(), max()

$total   = $qb->from('orders')->sum('amount');
$average = $qb->from('products')->avg('price');
$cheapest = $qb->from('products')->min('price');
$priciest = $qb->from('products')->max('price');

exists(): bool / doesntExist(): bool

if ($db->queryBuilder()->from('users')->where('email', $email)->exists()) {
    throw new \RuntimeException('Email already registered');
}

if ($db->queryBuilder()->from('roles')->where('name', 'admin')->doesntExist()) {
    // seed admin role
}

value(string $column): mixed / pluck(string $column): array

$email = $db->queryBuilder()->from('users')->where('userid', 42)->value('email');

$emails = $db->queryBuilder()->from('users')->where('active', 1)->pluck('email');
// → ['alice@example.com', 'bob@example.com', ...]

getAll() and pluck() answer [] for a failed query too — and only on PostgreSQL

get() keeps the distinction: false when the query failed, a Result when it succeeded including when it matched nothing. getAll() and pluck() collapse both into [], which is the convenience they exist for.

Which engine you are on decides whether that can hide anything. With throwOnError off — the default — a failed prepare returns false on PostgreSQL and throws on MySQL:

A missing table, via getAll()
PostgreSQL [] — indistinguishable from an empty table
MySQL throws mysqli_sql_exception

So an application developed against one and deployed against the other gets a different failure mode for free.

Where this bites is not the method, it is where the method gets reached for. getAll() is the obvious way to read a list, and the lists whose empty answer is most plausible are the ones where it is most consequential — settings, permissions, bans, allowlists. A ban list that failed to read is an empty ban list, and one cache call later it is a cached empty ban list, outliving the failure that caused it.

That is not hypothetical. A consumer renamed their settings table away and their getGlobalSettings() returned array() without throwing; the answer was cached as the installation's configuration, so every feature toggle sat at its compiled-in default for the whole TTL, with nothing in the logs.

Three ways to keep the distinction, cheapest first:

// 1. get() and check — no new API, works everywhere
$result = $db->queryBuilder()->from('url_blacklist')->get();
if ($result === false) {
    throw new \RuntimeException('blacklist unreadable — refusing to treat it as empty');
}
$patterns = $result->fetchAll();

// 2. getAllOrFail() — the same as getAll(), except a failed query throws QueryException.
//    One exception type on both drivers, so the per-driver split above stops mattering.
$patterns = $db->queryBuilder()->from('url_blacklist')->getAllOrFail();

// 3. connection-wide, when everything in a process should be loud
$db->throwOnError = true;

Reach for (2) on a read whose empty answer would be a decision, and especially when the answer is about to be cached. See How database failures surface for the driver detail.

A try/catch around a call that does not throw is a comment

Worth naming, because it is what this looks like in code somebody already worried about:

try {
    $patterns = $db->queryBuilder()->from('url_blacklist')->getAll();
} catch (\Throwable $e) {
    // a membership failing open grants somebody another station's tools, so this denies
    return self::DENY;
}

return $this->cache($patterns);   // ← an unreadable table arrives here, as []

getAll() does not throw on PostgreSQL, so the catch is unreachable and the failure walks straight into the branch that caches a miss. The author had decided the right thing and written it down; the decision simply could not run.

A consumer found eight reads of this class in their application and reported that six of them already had a catch written for exactly this failure — one of them arguing the fail-closed direction explicitly, in a comment. Their summary is the useful part: the week's work was less about deciding correct behaviour than about making decisions somebody had already taken actually run.

So when you find a try/catch around a convenience read, treat it as a signal rather than a guard: somebody knew this could fail and which way it should go. Check what the call actually does on failure, and if the answer is "returns []", the fix is getAllOrFail() or get() — the catch was already the right intention.

Advanced Features

Window Functions

$qb = $db->queryBuilder();
$result = $qb
    ->select([
        'id', 'name', 'category', 'price',
        $qb->over('RANK()', alias: 'price_rank',
            partition: ['category'],
            order: ['price' => 'asc']
        ),
    ])
    ->from('products')
    ->orderBy('category')
    ->get();

Supported functions: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(), SUM(), AVG(), MIN(), MAX(), COUNT(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Subqueries

// As SELECT column (correlated)
$result = $db->queryBuilder()
    ->select(['userid', 'username'])
    ->selectSub(function ($sub) {
        $sub->select('COUNT(*)')->from('orders')
            ->whereRaw('orders.userid = users.userid');
    }, 'order_count')
    ->from('users')
    ->get();

// As FROM source (derived table)
$result = $db->queryBuilder()
    ->select(['category', 'avg_price'])
    ->fromSub(function ($sub) {
        $sub->select(['category', $sub->raw('AVG(price) AS avg_price')])
            ->from('products')
            ->groupBy('category');
    }, 'cat_avgs')
    ->where('avg_price', '>', 5.00)
    ->get();

Set Operations

// UNION (removes duplicates)
$active = $db->queryBuilder()->select('userid', 'email')->from('users')->where('active', 1);
$admins = $db->queryBuilder()->select('userid', 'email')->from('admin_users');
$result = $active->union($admins)->get();

// UNION ALL (keeps duplicates)
$q1 = $db->queryBuilder()->select('name')->from('buyers');
$q2 = $db->queryBuilder()->select('name')->from('sellers');
$result = $q1->unionAll($q2)->get();

Raw Expressions

$qb->select('userid', $qb->raw("TO_CHAR(created_at, 'YYYY-MM') as month"));
$qb->orderBy($qb->raw('COALESCE(last_login, created_at)'), 'desc');
$qb->update(['last_login' => $qb->raw('NOW()')]);

Conditional Building

$qb = $db->queryBuilder()->from('products');

// Adds WHERE only when $categoryId is set
$result = $qb->when($categoryId, fn($q) => $q->where('category_id', $categoryId))->get();

// With fallback
$result = $qb->when($sortField, 
    fn($q) => $q->orderBy($sortField),
    fn($q) => $q->orderBy('created_at', 'desc')
)->get();

INSERT/UPDATE/DELETE

INSERT

$result = $db->queryBuilder()
    ->table('logs')
    ->insert([
        'message'    => 'User logged in',
        'userid'     => 42,
        'created_at' => $qb->raw('NOW()'),
    ]);

UPDATE

$db->queryBuilder()
    ->table('users')
    ->where('userid', 42)
    ->update(['last_login' => $qb->raw('NOW()')]);

DELETE

$db->queryBuilder()
    ->from('sessions')
    ->where('expires_at', '<', $qb->raw('NOW()'))
    ->delete();

TRUNCATE

$db->queryBuilder()->from('tmp_imports')->truncate();

Atomic Operations

// Increment
$db->queryBuilder()->from('posts')->where('postid', 123)->increment('views');

// Decrement
$db->queryBuilder()->from('wallets')->where('userid', 42)->decrement('balance', 9.99);

PostgreSQL RETURNING

// INSERT and get the new ID
$result = $db->queryBuilder()
    ->table('users')
    ->returning('userid')
    ->insert(['username' => 'jane', 'email' => 'jane@example.com']);

$newId = $result->fields['userid'];

// UPDATE and retrieve modified row
$result = $db->queryBuilder()
    ->table('users')
    ->where('userid', 5)
    ->returning(['userid', 'updated_at'])
    ->update(['active' => 0]);

Insert Variants

insertOrIgnore(array $values): Result

$db->queryBuilder()
    ->table('user_subscriptions')
    ->insertOrIgnore(['userid' => 42, 'topic' => 'alerts']);
// Second call with same keys does nothing — no exception

upsert(array $values, array $conflictColumns, array $updateValues = []): Result

$db->queryBuilder()
    ->table('user_settings')
    ->upsert(
        ['userid' => 5, 'setting_key' => 'theme', 'setting_value' => 'dark'],
        ['userid', 'setting_key'],   // conflict target
        ['setting_value']            // columns to update on conflict
    );

Batch Processing

Chunked Iteration

$db->queryBuilder()
    ->from('users')
    ->where('active', 1)
    ->orderBy('userid')
    ->chunk(500, function (array $rows, int $page) {
        foreach ($rows as $user) {
            sendWelcomeEmail($user['email']);
        }
        // return false here to stop early
    });

Important: Always include ORDER BY with chunk(). Without deterministic ordering, rows may be skipped or duplicated.

Result Objects

get(), first(), and write operations return a Pramnos\Database\Result instance.

Cursor-based Iteration

$result = $qb->from('logs')->orderBy('logid', 'desc')->get();

while ($result->fetch()) {
    echo $result->fields['message'] . "\n";
}

Fetch All At Once

$rows = $qb->from('users')->get()->fetchAll();
// $rows is a plain PHP array of associative arrays

Properties & Methods

Property / Method Description
$result->fields Associative array of current row
$result->numRows Total rows in result set
$result->eof true once all rows read
$result->getNumRows() Rows count (method form)
$result->getAffectedRows() Rows affected by UPDATE/DELETE
$result->getInsertId() Auto-increment ID from INSERT (MySQL)
$result->fetchAll() All rows as array
$result->fetch() Advance cursor
$result->free() Free resource

Debugging

toSql(): string

Returns compiled SQL without executing:

echo $qb->from('users')->where('active', 1)->toSql();
// → SELECT * FROM "users" WHERE "active" = '...'

getBindings(): array

Returns bound parameter values:

$bindings = $qb->from('users')->where('active', 1)->getBindings();
// → ['where' => [1], 'join' => [], ...]

Complete Example — Paginated List

$db = \Pramnos\Database\Database::getInstance();

$page    = max(1, (int)($_GET['page'] ?? 1));
$perPage = 20;

$qb = $db->queryBuilder()
    ->select('u.userid', 'u.username', 'u.email', 'g.groupname')
    ->from('users u')
    ->leftJoin('usergroups g', 'g.groupid', '=', 'u.groupid')
    ->where('u.active', 1)
    ->orderBy('u.username')
    ->forPage($page, $perPage);

// count() clones internally — ORDER BY/LIMIT/OFFSET stripped automatically
$total = $qb->count();
$users = $qb->get()->fetchAll();

// Use $users and $total for rendering

Backward Compatibility

QueryBuilder is new and purely additive. The existing Database::query(), Database::prepareQuery(), and Database::execute() methods are unchanged and continue to work exactly as before. No migration required for existing code.