README

A collection of PHP traits for building immutable, fluent query builders. This library provides low-level primitives without forcing any specific implementation.

Installation

composer require rcalicdan/query-builder-primitives

Philosophy

This library provides building blocks, not a complete query builder. You compose the traits you need to create your own custom query builder implementation.

Supported Database Drivers

MySQL/MariaDB
PostgreSQL
SQLite

Architecture Overview

Dependency Map

QueryBuilderCore (foundation - required)
  ↓
SqlBuilder (depends on: properties from condition/join/grouping traits)
  ↓
QueryConditions (depends on: QueryBuilderCore)
  ↓
QueryAdvancedConditions (depends on: QueryConditions, SqlBuilder)

QueryJoin (depends on: QueryBuilderCore)
QueryGrouping (depends on: QueryBuilderCore)
QueryLocking (depends on: QueryBuilderCore, SqlBuilder)
QueryUnion (depends on: QueryBuilderCore, SqlBuilder)
QueryDebug (depends on: all traits)

Trait Descriptions

Trait	Purpose	Dependencies
`QueryBuilderCore`	Core properties, select, and `from()`	None (foundation)
`SqlBuilder`	Builds SQL query strings	QueryBuilderCore + condition/join/grouping traits
`QueryConditions`	Basic WHERE, HAVING, LIKE clauses	QueryBuilderCore
`QueryAdvancedConditions`	Nested conditions, EXISTS, subqueries	QueryConditions, SqlBuilder
`QueryJoin`	JOIN operations (INNER, LEFT, RIGHT, CROSS)	QueryBuilderCore
`QueryGrouping`	GROUP BY, ORDER BY, LIMIT, OFFSET	QueryBuilderCore
`QueryLocking`	Pessimistic locking (FOR UPDATE, FOR SHARE, NOWAIT, SKIP LOCKED)	QueryBuilderCore, SqlBuilder
`QueryUnion`	UNION and UNION ALL operations	QueryBuilderCore, SqlBuilder
`QueryDebug`	Debug utilities (toSql, dump, dd)	All traits

Interfaces

Each trait has a corresponding contract under Rcalicdan\QueryBuilderPrimitives\Interfaces\:

Interface	Covers
`CoreInterface`	`from`, `select`, `addSelect`, `selectRaw`, `selectDistinct`
`ConditionInterface`	All WHERE, HAVING, LIKE methods
`AdvancedConditionInterface`	`whereGroup`, `whereExists`, `whereSub`, etc.
`JoinInterface`	All JOIN methods
`GroupingInterface`	`groupBy`, `orderBy`, `limit`, `offset`, `forPage`
`LockingInterface`	All locking methods
`UnionInterface`	`union`, `unionAll`
`DebugInterface`	`toSql`, `getBindings`, `toRawSql`, `dump`, `dd`
`QueryBuilderPrimitiveInterface`	Extends all of the above

QueryBuilderBase implements QueryBuilderPrimitiveInterface and uses all traits, making it a ready-made full implementation you can extend.

Quick Start

Minimal Query Builder

<?php

namespace App\Database;

use Rcalicdan\QueryBuilderPrimitives\{
    QueryBuilderCore,
    QueryConditions,
    SqlBuilder
};

class QueryBuilder
{
    use QueryBuilderCore;
    use SqlBuilder;
    use QueryConditions;
}

// Usage
$qb = new QueryBuilder();
$sql = $qb->from('users')
    ->select('id', 'name', 'email')
    ->where('status', 'active')
    ->where('age', '>=', 18)
    ->toSql();

echo $sql;
// SELECT id, name, email FROM users WHERE status = ? AND age >= ?

$bindings = $qb->getBindings();
// ['active', 18]

Full-Featured Query Builder

<?php

namespace App\Database;

use Rcalicdan\QueryBuilderPrimitives\QueryBuilderBase;

// QueryBuilderBase already composes every trait and implements QueryBuilderPrimitiveInterface.
// Extend it directly, or compose your own from individual traits.
class FullQueryBuilder extends QueryBuilderBase {}

// Usage with advanced features
$qb = new FullQueryBuilder();
$qb->from('users')
    ->select('users.*', 'orders.total')
    ->leftJoin('orders', 'orders.user_id = users.id')
    ->whereGroup(function($query) {
        return $query
            ->where('status', 'active')
            ->orWhere('status', 'pending');
    })
    ->groupBy('users.id')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->dd(); // Debug and die

Trait Details

QueryBuilderCore

Foundation trait providing core properties and select/driver management.

Properties:

$table - Table name
$select - Select columns
$bindings - Parameter bindings array

Public Methods:

from(string $table): static
select(string ...$columns): static
addSelect(string ...$columns): static
selectRaw(string $expression, array $bindings = []): static
selectDistinct(string ...$columns): static
setDriver(string $driver): static       // 'mysql' | 'pgsql' | 'sqlite'

Protected Methods:

newQuery(): static          // Returns a fresh instance for subqueries/unions — override this when your constructor takes arguments
getDriver(): string
getPlaceholder(): string
getCompiledBindings(): array

Examples:

// Basic select
$qb->from('users')
    ->select('id', 'name')
    ->addSelect('email')
    ->setDriver('pgsql');

// Select all (default)
$qb->from('users')->select();

// Raw expression in select
$qb->from('orders')
    ->select('user_id')
    ->selectRaw('SUM(total) as total_spent')
    ->selectRaw('COUNT(*) as order_count');

// Parameterised raw expression
$qb->from('products')
    ->selectRaw('CASE WHEN price > ? THEN ? ELSE ? END as tier', [100, 'premium', 'standard']);

// DISTINCT
$qb->from('users')->selectDistinct('country');

The `newQuery()` Method — Required Override for Custom Constructors

newQuery() is the internal factory used by QueryAdvancedConditions and QueryUnion whenever they need a fresh builder instance for subqueries or unions. The default implementation calls new static() with no arguments.

If your concrete class has required constructor parameters (e.g., a PDO connection, a service container, or a config object), you must override newQuery() to pass those dependencies. Without this, subquery methods (whereExists, whereGroup, whereSub, union, etc.) will throw a \LogicException at runtime.

class ExecutableQueryBuilder extends QueryBuilderBase
{
    public function __construct(private PDO $pdo) {}

    // REQUIRED: pass the PDO dependency to the fresh instance
    protected function newQuery(): static
    {
        return new static($this->pdo);
    }

    public function get(): array
    {
        $stmt = $this->pdo->prepare($this->buildSelectQuery());
        $stmt->execute($this->getCompiledBindings());
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // ... other execution methods
}

If you forget to override newQuery() and call a subquery method, you will get:

LogicException: Cannot instantiate subquery builder for class "App\Database\ExecutableQueryBuilder".
Because your constructor requires arguments, you must override the protected `newQuery(): static`
method in your class to manually pass your dependencies.

QueryConditions

Basic WHERE and HAVING clauses.

Methods:

where(string $column, mixed $operator, mixed $value): static
orWhere(string $column, mixed $operator, mixed $value): static
whereIn(string $column, array $values): static
whereNotIn(string $column, array $values): static
whereBetween(string $column, array $values): static
whereNull(string $column): static
whereNotNull(string $column): static
whereColumn(string $first, ?string $operator, ?string $second, string $boolean = 'AND'): static
orWhereColumn(string $first, ?string $operator, ?string $second): static
like(string $column, string $value, string $side = 'both'): static
having(string $column, mixed $operator, mixed $value, string $boolean = 'AND'): static
orHaving(string $column, mixed $operator, mixed $value): static
havingRaw(string $condition, array $bindings = [], string $boolean = 'AND'): static
orHavingRaw(string $condition, array $bindings = []): static
whereRaw(string $condition, array $bindings = [], string $operator = 'AND'): static
orWhereRaw(string $condition, array $bindings = []): static
resetWhere(): static

Examples:

// Basic WHERE
$qb->where('status', 'active')
   ->where('age', '>=', 18);

// Two-argument shorthand (defaults to '=')
$qb->where('status', 'active');

// WHERE IN / NOT IN
$qb->whereIn('id', [1, 2, 3]);
$qb->whereNotIn('role', ['guest', 'banned']);

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

// NULL checks
$qb->whereNull('deleted_at')
   ->whereNotNull('email');

// Column-to-column comparison (no binding, no injection risk)
$qb->whereColumn('created_at', 'updated_at');          // created_at = updated_at
$qb->whereColumn('price', '>', 'discounted_price');
$qb->orWhereColumn('verified_at', 'created_at');

// LIKE clauses
$qb->like('name', 'John', 'both');          // %John%
$qb->like('email', '@gmail.com', 'before'); // %@gmail.com
$qb->like('username', 'admin', 'after');    // admin%

// Raw WHERE
$qb->whereRaw('DATE(created_at) = CURDATE()');
$qb->whereRaw('age > ? AND status = ?', [18, 'active']);

// OR WHERE
$qb->where('status', 'active')
   ->orWhere('status', 'pending');

// HAVING — AND (default)
$qb->from('orders')
   ->select('user_id')
   ->selectRaw('COUNT(*) as total')
   ->groupBy('user_id')
   ->having('total', '>', 5);

// HAVING — OR
$qb->groupBy('user_id')
   ->having('total_orders', '>', 10)
   ->orHaving('total_spent', '>', 1000);
// HAVING total_orders > ? OR total_spent > ?

// Raw HAVING
$qb->groupBy('department')
   ->havingRaw('SUM(salary) > ?', [50000])
   ->orHavingRaw('COUNT(*) > ?', [20]);
// HAVING SUM(salary) > ? OR COUNT(*) > ?

QueryAdvancedConditions

Advanced nested conditions and subqueries.

Dependencies: Requires QueryConditions and SqlBuilder

Note: These methods use newQuery() internally. If your builder has constructor arguments, override newQuery() — see the QueryBuilderCore section above.

Methods:

whereGroup(callable $callback, string $logicalOperator = 'AND'): static
whereNested(callable $callback, string $operator = 'AND'): static
orWhereNested(callable $callback): static
whereExists(callable $callback, string $operator = 'AND'): static
whereNotExists(callable $callback, string $operator = 'AND'): static
orWhereExists(callable $callback): static
orWhereNotExists(callable $callback): static
whereSub(string $column, string $operator, callable $callback): static

Examples:

// Nested conditions with grouping
$qb->from('users')
    ->where('role', 'admin')
    ->whereGroup(function($query) {
        return $query
            ->where('status', 'active')
            ->orWhere('status', 'pending');
    });
// WHERE role = ? AND (status = ? OR status = ?)

// OR nested groups
$qb->where('type', 'premium')
    ->orWhereNested(function($query) {
        return $query
            ->where('trial_active', true)
            ->where('trial_ends_at', '>', date('Y-m-d'));
    });

// EXISTS subquery
$qb->from('users')
    ->whereExists(function($query) {
        return $query
            ->from('orders')
            ->whereRaw('orders.user_id = users.id')
            ->where('orders.total', '>', 1000);
    });
// WHERE EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id AND orders.total > ?)

// NOT EXISTS
$qb->from('users')
    ->whereNotExists(function($query) {
        return $query
            ->from('orders')
            ->whereRaw('orders.user_id = users.id');
    });

// Subquery in WHERE
$qb->from('users')
    ->whereSub('total_orders', '>', function($query) {
        return $query
            ->from('orders')
            ->selectRaw('COUNT(*)')
            ->whereRaw('orders.user_id = users.id');
    });

QueryJoin

JOIN operations.

Dependencies: Requires QueryBuilderCore

Methods:

join(string $table, string $condition, string $type = 'INNER'): static
leftJoin(string $table, string $condition): static
rightJoin(string $table, string $condition): static
innerJoin(string $table, string $condition): static
crossJoin(string $table): static

Examples:

// INNER JOIN
$qb->from('users')
    ->innerJoin('profiles', 'profiles.user_id = users.id');

// LEFT JOIN
$qb->from('users')
    ->leftJoin('orders', 'orders.user_id = users.id');

// Multiple joins
$qb->from('users')
    ->leftJoin('profiles', 'profiles.user_id = users.id')
    ->leftJoin('orders', 'orders.user_id = users.id')
    ->leftJoin('payments', 'payments.order_id = orders.id');

// CROSS JOIN
$qb->from('colors')
    ->crossJoin('sizes');

QueryGrouping

Grouping, ordering, and pagination.

Dependencies: Requires QueryBuilderCore

Methods:

groupBy(string|array $columns): static
orderBy(string $column, string $direction = 'ASC'): static
orderByAsc(string $column): static
orderByDesc(string $column): static
limit(int $limit, ?int $offset = null): static
offset(int $offset): static
forPage(int $page, int $perPage = 15): static

Examples:

// GROUP BY
$qb->select('user_id')
    ->selectRaw('COUNT(*) as total')
    ->groupBy('user_id');

// Multiple GROUP BY
$qb->groupBy(['user_id', 'status']);

// ORDER BY
$qb->orderBy('created_at', 'DESC')
    ->orderBy('name', 'ASC');

// Shorthand
$qb->orderByDesc('created_at')
    ->orderByAsc('name');

// LIMIT and OFFSET
$qb->limit(10)->offset(20);

// Or combined
$qb->limit(10, 20); // LIMIT 10 OFFSET 20

// Pagination helper
$qb->forPage(2, 25); // Page 2, 25 per page = LIMIT 25 OFFSET 25

QueryLocking

Pessimistic locking for concurrency control within database transactions.

Dependencies: Requires QueryBuilderCore and SqlBuilder

Important: Lock clauses are only meaningful inside a database transaction. Always wrap locking queries in BEGIN / COMMIT.

Methods:

lockForUpdate(): static
lockForShare(): static
noWait(): static
skipLocked(): static
lockOf(string|array $tables): static    // PostgreSQL only
withoutLock(): static

Driver support matrix

Feature	MySQL	PostgreSQL	SQLite
`lockForUpdate()`	✅ `FOR UPDATE`	✅ `FOR UPDATE`	❌ ignored
`lockForShare()`	✅ `LOCK IN SHARE MODE`	✅ `FOR SHARE`	❌ ignored
`noWait()` on `FOR UPDATE`	✅	✅	❌ ignored
`noWait()` on `FOR SHARE`	❌ silently ignored	✅	❌ ignored
`skipLocked()` on `FOR UPDATE`	✅	✅	❌ ignored
`skipLocked()` on `FOR SHARE`	❌ silently ignored	✅	❌ ignored
`lockOf()`	❌ throws	✅	❌ ignored

SQLite note: SQLite has no row-level locking. Use BEGIN EXCLUSIVE or BEGIN IMMEDIATE at the connection level instead.

Examples:

// Exclusive lock
$qb->from('orders')
    ->where('id', 1)
    ->lockForUpdate()
    ->toSql();
// MySQL/PgSQL: SELECT * FROM orders WHERE id = ? FOR UPDATE

// Shared lock
$qb->from('inventory')
    ->where('product_id', 42)
    ->lockForShare()
    ->toSql();
// MySQL: SELECT * FROM inventory WHERE product_id = ? LOCK IN SHARE MODE
// PgSQL: SELECT * FROM inventory WHERE product_id = ? FOR SHARE

// Fail immediately if rows are locked
$qb->from('orders')
    ->where('status', 'pending')
    ->lockForUpdate()
    ->noWait()
    ->toSql();
// SELECT * FROM orders WHERE status = ? FOR UPDATE NOWAIT

// Queue worker pattern — skip rows locked by other workers
$qb->from('jobs')
    ->where('status', 'pending')
    ->orderBy('created_at')
    ->limit(1)
    ->lockForUpdate()
    ->skipLocked()
    ->toSql();
// SELECT * FROM jobs WHERE status = ? ORDER BY created_at ASC LIMIT 1 FOR UPDATE SKIP LOCKED

// PostgreSQL OF clause
$qb->from('orders')
    ->setDriver('pgsql')
    ->join('users', 'orders.user_id = users.id')
    ->lockForUpdate()
    ->lockOf('orders')
    ->toSql();
// SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id FOR UPDATE OF orders

// Remove lock from a reused base query
$base   = $qb->from('orders')->lockForUpdate();
$unlocked = $base->withoutLock();

Clause ordering

SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ... OFFSET ... <LOCK>

QueryUnion

UNION and UNION ALL operations.

Dependencies: Requires QueryBuilderCore and SqlBuilder

Note: This trait uses newQuery() internally. If your builder has constructor arguments, override newQuery() — see the QueryBuilderCore section above.

Methods:

union(callable $callback, bool $all = false): static
unionAll(callable $callback): static

Examples:

// Basic UNION (deduplicates rows)
$qb->from('active_users')
    ->select('id', 'name', 'email')
    ->union(function($query) {
        return $query
            ->from('archived_users')
            ->select('id', 'name', 'email');
    })
    ->toSql();
// SELECT id, name, email FROM active_users
// UNION SELECT id, name, email FROM archived_users

// UNION ALL (keeps duplicates)
$qb->from('orders_2023')
    ->select('id', 'total', 'created_at')
    ->unionAll(function($query) {
        return $query
            ->from('orders_2024')
            ->select('id', 'total', 'created_at');
    })
    ->orderBy('created_at', 'DESC')
    ->toSql();
// SELECT id, total, created_at FROM orders_2023
// UNION ALL SELECT id, total, created_at FROM orders_2024
// ORDER BY created_at DESC

// Chaining multiple UNIONs
$qb->from('employees')
    ->select('id', 'name', 'department')
    ->where('active', true)
    ->union(function($query) {
        return $query->from('contractors')
                     ->select('id', 'name', 'department')
                     ->where('active', true);
    })
    ->union(function($query) {
        return $query->from('interns')
                     ->select('id', 'name', 'department');
    })
    ->orderBy('name');

// Bindings are correctly propagated across all union branches
$qb->from('products')
    ->select('id', 'name', 'price')
    ->where('category', 'electronics')
    ->unionAll(function($query) {
        return $query->from('products')
                     ->select('id', 'name', 'price')
                     ->where('category', 'accessories')
                     ->where('price', '<', 50);
    });

$bindings = $qb->getBindings();
// ['electronics', 'accessories', 50]

ORDER BY, LIMIT, and OFFSET placed on the outer query apply to the full union result set. Column counts and types must match across all unioned queries.

QueryDebug

Debugging utilities.

Dependencies: Requires all other traits

Methods:

toSql(): string
getBindings(): array
toRawSql(): string
dump(): static
dd(): never

Examples:

// Get SQL query
$sql = $qb->from('users')
    ->where('status', 'active')
    ->toSql();
echo $sql; // SELECT * FROM users WHERE status = ?

// Get bindings
$bindings = $qb->getBindings();
var_dump($bindings); // ['active']

// Get interpolated SQL (DEBUG ONLY — never use for execution!)
$rawSql = $qb->toRawSql();
echo $rawSql; // SELECT * FROM users WHERE status = 'active'

// Dump and continue
$qb->from('users')
    ->where('status', 'active')
    ->dump()
    ->where('age', '>=', 18)
    ->dump();

// Dump and die (stops execution)
$qb->from('users')
    ->where('status', 'active')
    ->dd();

SqlBuilder

Builds SQL query strings from accumulated state.

Dependencies: Requires QueryBuilderCore and properties from condition/join/grouping traits

Protected Methods (used internally or for extension):

buildSelectQuery(): string
buildCountQuery(string $column = '*'): string
buildInsertQuery(array $data): string
buildInsertBatchQuery(array $data): string
buildUpdateQuery(array $data): string
buildDeleteQuery(): string
buildWhereClause(): string
buildHavingClause(): string
buildAggregateQuery(string $function, string $column): string
buildUpsertQuery(array $data, string|array $uniqueColumns, ?array $updateColumns = null): string

buildHavingClause() handles both AND and OR conditions, driven by the $boolean parameter on having() and havingRaw().

Immutability

All methods return a new instance of the query builder, ensuring immutability:

$base = $qb->from('users')->where('status', 'active');

$query1 = $base->where('age', '>=', 18);
$query2 = $base->where('country', 'US');

// $base remains unchanged; $query1 and $query2 are independent

// Same applies to locks and unions
$plain  = $qb->from('orders')->where('status', 'pending');
$locked = $plain->lockForUpdate();
$union  = $plain->union(fn($q) => $q->from('archived_orders'));

// $plain has no lock and no union; $locked and $union are independent forks

Extending with Execution

<?php

namespace App\Database;

use PDO;
use Rcalicdan\QueryBuilderPrimitives\QueryBuilderBase;

class ExecutableQueryBuilder extends QueryBuilderBase
{
    public function __construct(private PDO $pdo) {}

    /**
     * REQUIRED when your constructor takes arguments.
     * Called internally by whereExists(), whereGroup(), union(), etc.
     */
    protected function newQuery(): static
    {
        return new static($this->pdo);
    }

    public function get(): array
    {
        $stmt = $this->pdo->prepare($this->buildSelectQuery());
        $stmt->execute($this->getCompiledBindings());
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function first(): ?array
    {
        return $this->limit(1)->get()[0] ?? null;
    }

    public function count(string $column = '*'): int
    {
        $stmt = $this->pdo->prepare($this->buildCountQuery($column));
        $stmt->execute($this->getCompiledBindings());
        return (int) $stmt->fetchColumn();
    }

    public function insert(array $data): bool
    {
        $stmt = $this->pdo->prepare($this->buildInsertQuery($data));
        return $stmt->execute(array_values($data));
    }

    public function update(array $data): int
    {
        $stmt = $this->pdo->prepare($this->buildUpdateQuery($data));
        $stmt->execute(array_merge(array_values($data), $this->getCompiledBindings()));
        return $stmt->rowCount();
    }

    public function delete(): int
    {
        $stmt = $this->pdo->prepare($this->buildDeleteQuery());
        $stmt->execute($this->getCompiledBindings());
        return $stmt->rowCount();
    }
}

// Usage
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'pass');
$qb  = new ExecutableQueryBuilder($pdo);

$users = $qb->from('users')
    ->where('status', 'active')
    ->orderByDesc('created_at')
    ->limit(10)
    ->get();

// EXISTS subquery — works because newQuery() is overridden
$highValue = $qb->from('users')
    ->whereExists(function($q) {
        return $q->from('orders')
                 ->whereRaw('orders.user_id = users.id')
                 ->where('total', '>', 1000);
    })
    ->get();

// Locking inside a transaction
$pdo->beginTransaction();
$job = $qb->from('jobs')
    ->where('status', 'pending')
    ->orderBy('created_at')
    ->limit(1)
    ->lockForUpdate()
    ->skipLocked()
    ->first();
// process $job ...
$pdo->commit();

Recommended Compositions

1. Read-Only Query Builder

class ReadOnlyQueryBuilder
{
    use QueryBuilderCore;
    use SqlBuilder;
    use QueryConditions;
    use QueryJoin;
    use QueryGrouping;
    use QueryUnion;
    use QueryDebug;
}

2. Simple Query Builder (No Advanced Features)

class SimpleQueryBuilder
{
    use QueryBuilderCore;
    use SqlBuilder;
    use QueryConditions;
    use QueryGrouping;
}

3. Reporting Query Builder (Heavy on Joins/Grouping)

class ReportingQueryBuilder
{
    use QueryBuilderCore;
    use SqlBuilder;
    use QueryConditions;
    use QueryJoin;
    use QueryGrouping;
    use QueryUnion;
    use QueryDebug;
}

4. Full-Featured (All Traits)

// Use QueryBuilderBase directly — it already composes everything.
use Rcalicdan\QueryBuilderPrimitives\QueryBuilderBase;

class MyQueryBuilder extends QueryBuilderBase
{
    public function __construct(private PDO $pdo) {}

    protected function newQuery(): static
    {
        return new static($this->pdo);
    }
}

Common Patterns

Complex WHERE Logic

// (status = 'active' AND role = 'admin') OR (status = 'pending' AND invited = true)
$qb->from('users')
    ->whereGroup(function($q) {
        return $q->where('status', 'active')
                 ->where('role', 'admin');
    })
    ->whereGroup(function($q) {
        return $q->where('status', 'pending')
                 ->where('invited', true);
    });

OR HAVING

// Reports where activity is high by either metric
$qb->from('orders')
    ->select('user_id')
    ->selectRaw('COUNT(*) as order_count')
    ->selectRaw('SUM(total) as total_spent')
    ->groupBy('user_id')
    ->having('order_count', '>', 10)
    ->orHaving('total_spent', '>', 5000);
// HAVING order_count > ? OR total_spent > ?

// Mix of raw and structured conditions
$qb->from('stats')
    ->groupBy('team_id')
    ->havingRaw('SUM(points) > ?', [100])
    ->orHavingRaw('COUNT(wins) >= ?', [20]);

Subquery Patterns

// Users who have placed orders over $1000
$qb->from('users')
    ->whereExists(function($q) {
        return $q->from('orders')
                 ->whereRaw('orders.user_id = users.id')
                 ->where('total', '>', 1000);
    });

// Column-to-column comparison
$qb->from('audit_log')
    ->whereColumn('expected_hash', 'actual_hash')
    ->orWhereColumn('verified_at', '>', 'created_at');

Pessimistic Locking Patterns

// Payment processing
$pdo->beginTransaction();
$order = $qb->from('orders')
    ->where('id', $orderId)
    ->where('status', 'pending')
    ->lockForUpdate()
    ->first();
$pdo->commit();

// Job queue with SKIP LOCKED
$pdo->beginTransaction();
$job = $qb->from('jobs')
    ->where('status', 'available')
    ->orderByDesc('priority')
    ->orderBy('created_at')
    ->limit(1)
    ->lockForUpdate()
    ->skipLocked()
    ->first();
$pdo->commit();

UNION Patterns

// Combine partitioned tables
$qb->from('logs_2024')
    ->select('id', 'user_id', 'action', 'created_at')
    ->unionAll(function($q) {
        return $q->from('logs_2025')
                 ->select('id', 'user_id', 'action', 'created_at');
    })
    ->orderByDesc('created_at')
    ->limit(100);

// Merge different record types into a single feed
$qb->from('posts')
    ->select('id', 'title', 'created_at')
    ->selectRaw("'post' as type")
    ->union(function($q) {
        return $q->from('comments')
                 ->select('id', 'body as title', 'created_at')
                 ->selectRaw("'comment' as type");
    })
    ->orderByDesc('created_at');

Reporting Queries

$qb->from('orders')
    ->select('users.name')
    ->selectRaw('COUNT(orders.id) as total_orders')
    ->selectRaw('SUM(orders.total) as total_spent')
    ->selectRaw('AVG(orders.total) as avg_order')
    ->leftJoin('users', 'users.id = orders.user_id')
    ->where('orders.status', 'completed')
    ->whereBetween('orders.created_at', ['2024-01-01', '2024-12-31'])
    ->groupBy('users.id')
    ->having('total_orders', '>', 5)
    ->orHaving('total_spent', '>', 10000)
    ->orderByDesc('total_spent')
    ->limit(100);

Requirements

PHP 8.2 or higher

License

MIT

Contributing

This is a primitive library, keep it simple and focused on building blocks, not opinions.

rcalicdan / query-builder-primitives

Maintainers

Package info

Statistics

Security

README

Installation

Philosophy

Supported Database Drivers

Architecture Overview

Dependency Map

Trait Descriptions

Interfaces

Quick Start

Minimal Query Builder

Full-Featured Query Builder

Trait Details

QueryBuilderCore

The newQuery() Method — Required Override for Custom Constructors

QueryConditions

QueryAdvancedConditions

QueryJoin

QueryGrouping

QueryLocking

Driver support matrix

Clause ordering

QueryUnion

QueryDebug

SqlBuilder

Immutability

Extending with Execution

Recommended Compositions

1. Read-Only Query Builder

2. Simple Query Builder (No Advanced Features)

3. Reporting Query Builder (Heavy on Joins/Grouping)

4. Full-Featured (All Traits)

Common Patterns

Complex WHERE Logic

OR HAVING

Subquery Patterns

Pessimistic Locking Patterns

UNION Patterns

Reporting Queries

Requirements

License

Contributing

The `newQuery()` Method — Required Override for Custom Constructors