Query

Pair framework: Query

Pair\Orm\Query is Pair's fluent SQL SELECT query builder.

It supports:

select/from/subqueries
joins and join subqueries
where/having with nested/date/raw variants
grouping, ordering, pagination
aggregates (count, sum, avg, ...)
unions and row locks
optional hydration to ActiveRecord models (setModel)

Mental model

Query is chainable and mutable. Each call changes the current builder.

Use clone when you need multiple variants from the same base query:

use Pair\Orm\Query;

$base = Query::table('orders')->where('tenant_id', '=', $tenantId);

$paidCount = (clone $base)->where('status', '=', 'paid')->count();
$failedCount = (clone $base)->where('status', '=', 'failed')->count();

Core example

use Pair\Orm\Query;

$rows = Query::table('orders o')
    ->select('o.id', 'o.total', 'u.email')
    ->join('users u', 'u.id', '=', 'o.user_id')
    ->where('o.status', '=', 'paid')
    ->whereBetween('o.created_at', ['2026-01-01', '2026-01-31'])
    ->orderByDesc('o.created_at')
    ->forPage(1, 50)
    ->get();

Main methods (deep dive)

1) `table()`, `from()`, `fromRaw()`, `fromSub()`

These methods define the source dataset.

$q = Query::table('users')->select('id', 'email');

$lastMonthOrders = Query::table('orders')
    ->whereDate('created_at', '>=', '2026-01-01')
    ->whereDate('created_at', '<', '2026-02-01')
    ->select('id', 'user_id', 'total');

$report = Query::table('orders')
    ->fromSub($lastMonthOrders, 'o')
    ->select('o.user_id')
    ->selectRaw('SUM(o.total) as total_spent')
    ->groupBy('o.user_id')
    ->get();

fromRaw() is useful for complex derived tables:

$rows = Query::table('users')
    ->fromRaw('(SELECT * FROM users WHERE deleted_at IS NULL) active_users')
    ->select('active_users.id', 'active_users.email')
    ->get();

2) `select()`, `addSelect()`, `selectRaw()`, `selectSub()`

Use these methods to shape the final projection.

$users = Query::table('users u')
    ->select('u.id', 'u.email')
    ->addSelect('u.created_at')
    ->selectRaw('LOWER(u.email) AS email_lower')
    ->get();

selectSub() adds correlated derived columns:

$users = Query::table('users u')
    ->select('u.id', 'u.email')
    ->selectSub(
        Query::table('orders o')
            ->selectRaw('COUNT(*)')
            ->whereColumn('o.user_id', 'u.id'),
        'orders_count'
    )
    ->orderByDesc('orders_count')
    ->get();

3) `where(...)` family

where supports basic clauses, grouped callbacks, and array syntax.

$filtered = Query::table('orders')
    ->where('tenant_id', '=', $tenantId)
    ->where([
        'status' => 'paid',
        ['total', '>=', 100],
    ])
    ->where(function (Query $sub) {
        $sub->where('channel', '=', 'web')
            ->orWhere('channel', '=', 'phone');
    })
    ->get();

Null conversion is automatic:

// Converts to WHERE shipped_at IS NULL
$pendingShipments = Query::table('orders')
    ->where('shipped_at', '=', null)
    ->get();

Date helpers:

$todayLateOrders = Query::table('orders')
    ->whereDate('created_at', '=', '2026-02-26')
    ->whereTime('created_at', '>=', '18:00:00')
    ->get();

Range and set helpers:

$rows = Query::table('products')
    ->whereBetween('price', [10, 50])
    ->whereNotIn('category_id', [3, 7])
    ->get();

Column-to-column comparison:

$rows = Query::table('invoices')
    ->whereColumn('paid_at', '>=', 'issued_at')
    ->get();

4) `whereExists()`, `whereIn()` with subqueries

Use these for SQL that would otherwise need nested raw clauses.

$usersWithPaidOrders = Query::table('users u')
    ->whereExists(function (Query $q) {
        $q->from('orders o')
            ->selectRaw('1')
            ->whereColumn('o.user_id', 'u.id')
            ->where('o.status', '=', 'paid');
    })
    ->get();

$vipIds = Query::table('vip_users')->select('user_id');

$orders = Query::table('orders')
    ->whereIn('user_id', $vipIds)
    ->where('status', '=', 'paid')
    ->get();

5) `join()`, `leftJoin()`, `rightJoin()`, `crossJoin()`, `joinWhere()`

Basic joins:

$rows = Query::table('orders o')
    ->join('users u', 'u.id', '=', 'o.user_id')
    ->leftJoin('countries c', 'c.id', '=', 'u.country_id')
    ->select('o.id', 'u.email', 'c.name AS country')
    ->get();

Join with literal value binding (joinWhere) when the join compares against a constant:

$rows = Query::table('users u')
    ->joinWhere('app_settings s', 's.key', '=', 'timezone')
    ->select('u.id', 'u.email', 's.value AS timezone')
    ->get();

Cross join for matrix-like output:

$rows = Query::table('sizes s')
    ->crossJoin('colors c')
    ->select('s.name AS size', 'c.name AS color')
    ->get();

6) `joinSub()`, `leftJoinSub()`, `rightJoinSub()`

Subquery joins are ideal for pre-aggregated datasets.

$totals = Query::table('orders')
    ->select('user_id')
    ->selectRaw('SUM(total) AS total_spent')
    ->groupBy('user_id');

$users = Query::table('users u')
    ->leftJoinSub($totals, 't', 't.user_id', '=', 'u.id')
    ->select('u.id', 'u.email', 't.total_spent')
    ->orderByDesc('t.total_spent')
    ->get();

7) `groupBy()`, `groupByRaw()`, `having()`, `havingRaw()`, nested having

$report = Query::table('orders')
    ->select('channel')
    ->selectRaw('COUNT(*) AS orders_count')
    ->selectRaw('SUM(total) AS gross_total')
    ->groupBy('channel')
    ->having('orders_count', '>=', 20)
    ->havingNested(function (Query $q) {
        $q->having('gross_total', '>=', 5000)
          ->orHavingRaw('SUM(total) >= ?', [10000]);
    })
    ->orderByDesc('gross_total')
    ->get();

8) `get()`, `first()`, `firstOrFail()`, `firstWhere()`, `value()`, `pluck()`

$user = Query::table('users')->where('id', '=', 10)->first();

$mustExist = Query::table('users')->where('id', '=', 10)->firstOrFail();

$byEmail = Query::table('users')->firstWhere('email', '=', 'john@example.com');

$email = Query::table('users')->where('id', '=', 10)->value('email');

$emailsById = Query::table('users')
    ->where('active', '=', 1)
    ->pluck('email', 'id');

9) `setModel()` hydration to ActiveRecord

When model class is set, retrieval methods hydrate objects.

use App\Orm\User;

$users = Query::table('users')
    ->setModel(User::class)
    ->where('active', '=', 1)
    ->latest('created_at')
    ->get();

$first = Query::table('users')
    ->setModel(User::class)
    ->firstWhere('id', '=', 5);

10) Aggregates and existence checks

$stats = [
    'count' => Query::table('orders')->count(),
    'paid_count' => Query::table('orders')->where('status', '=', 'paid')->count(),
    'sum_paid' => Query::table('orders')->where('status', '=', 'paid')->sum('total'),
    'avg_paid' => Query::table('orders')->where('status', '=', 'paid')->avg('total'),
    'max_paid' => Query::table('orders')->where('status', '=', 'paid')->max('total'),
    'min_paid' => Query::table('orders')->where('status', '=', 'paid')->min('total'),
    'has_pending' => Query::table('orders')->where('status', '=', 'pending')->exists(),
    'all_processed' => Query::table('orders')->where('status', '=', 'pending')->doesntExist(),
];

11) Ordering and paging: `orderBy*`, `latest`, `oldest`, `limit/offset`, `forPage`, `paginate`

$rows = Query::table('orders')
    ->where('tenant_id', '=', $tenantId)
    ->latest('created_at')
    ->limit(20)
    ->get();

$page = Query::table('orders')
    ->where('tenant_id', '=', $tenantId)
    ->orderBy('created_at', 'desc')
    ->paginate(perPage: 25, page: 2, columns: ['id', 'status', 'total', 'created_at']);

// $page = [
//   'items' => Collection,
//   'total' => int,
//   'perPage' => int,
//   'currentPage' => int,
//   'lastPage' => int,
//   'from' => ?int,
//   'to' => ?int,
// ]

12) Unions: `union()`, `unionAll()`

$orders = Query::table('orders')
    ->selectRaw("id, 'order' AS type, created_at")
    ->where('tenant_id', '=', $tenantId);

$tickets = Query::table('tickets')
    ->selectRaw("id, 'ticket' AS type, created_at")
    ->where('tenant_id', '=', $tenantId);

$feed = $orders
    ->unionAll($tickets)
    ->orderByDesc('created_at')
    ->limit(100)
    ->get();

13) SQL debugging: `toSql()`, `getBindings()`, `__toString()`

$q = Query::table('users')
    ->where('email', 'like', '%@example.com')
    ->where('active', '=', 1)
    ->orderByDesc('id');

$sql = $q->toSql();
$bindings = $q->getBindings();
$stringified = (string)$q;

14) Locks: `lock()`, `lockForUpdate()`, `sharedLock()`

Use in transactions for consistency-sensitive flows.

use Pair\Orm\Database;

Database::start();

try {
    $account = Query::table('accounts')
        ->where('id', '=', $accountId)
        ->lockForUpdate()
        ->firstOrFail();

    // perform balance update...

    Database::commit();
} catch (\Throwable $e) {
    Database::rollback();
    throw $e;
}

Read lock:

$inventory = Query::table('stock')
    ->where('sku', '=', $sku)
    ->sharedLock()
    ->first();

15) Identifier wrapping: `wrapIdentifiers(false)`

By default, identifiers are wrapped when safe. Disable it when you need fully manual SQL style.

$rows = Query::table('events e')
    ->wrapIdentifiers(false)
    ->selectRaw('DATE_FORMAT(e.created_at, "%Y-%m") AS ym')
    ->selectRaw('COUNT(*) AS c')
    ->groupByRaw('DATE_FORMAT(e.created_at, "%Y-%m")')
    ->orderByRaw('ym DESC')
    ->get();

Practical cookbook (additional examples)

Dynamic search form with optional filters

$q = Query::table('orders o')
    ->select('o.id', 'o.status', 'o.total', 'o.created_at', 'u.email')
    ->join('users u', 'u.id', '=', 'o.user_id')
    ->where('o.tenant_id', '=', $tenantId);

if (!empty($filters['status'])) {
    $q->where('o.status', '=', $filters['status']);
}

if (!empty($filters['q'])) {
    $q->where(function (Query $sub) use ($filters) {
        $term = '%' . $filters['q'] . '%';
        $sub->where('u.email', 'like', $term)
            ->orWhere('o.id', 'like', $term);
    });
}

if (!empty($filters['from']) && !empty($filters['to'])) {
    $q->whereBetween('o.created_at', [$filters['from'], $filters['to']]);
}

$results = $q->paginate(20, (int)($filters['page'] ?? 1));

Monthly KPI report (grouping + having + ordering)

$kpi = Query::table('orders')
    ->selectRaw('YEAR(created_at) AS y')
    ->selectRaw('MONTH(created_at) AS m')
    ->selectRaw('COUNT(*) AS orders_count')
    ->selectRaw('SUM(total) AS gross')
    ->where('tenant_id', '=', $tenantId)
    ->whereYear('created_at', '=', 2026)
    ->groupByRaw('YEAR(created_at), MONTH(created_at)')
    ->having('orders_count', '>=', 50)
    ->orderByRaw('y ASC, m ASC')
    ->get();

Top customers with fallback to `0` using subquery join

$totals = Query::table('orders')
    ->select('user_id')
    ->selectRaw('SUM(total) AS total_spent')
    ->where('status', '=', 'paid')
    ->groupBy('user_id');

$top = Query::table('users u')
    ->leftJoinSub($totals, 't', 't.user_id', '=', 'u.id')
    ->select('u.id', 'u.email')
    ->selectRaw('COALESCE(t.total_spent, 0) AS total_spent')
    ->orderByDesc('total_spent')
    ->limit(10)
    ->get();

Compliance check with `whereNotExists`

$usersWithoutConsent = Query::table('users u')
    ->whereNotExists(function (Query $q) {
        $q->from('consents c')
            ->selectRaw('1')
            ->whereColumn('c.user_id', 'u.id')
            ->where('c.type', '=', 'privacy')
            ->where('c.revoked_at', '=', null);
    })
    ->get();

Lightweight endpoints: `pluck()` and `value()`

$activeIds = Query::table('users')
    ->where('active', '=', 1)
    ->pluck('id');

$systemCurrency = Query::table('settings')
    ->where('key', '=', 'currency')
    ->value('value');

Secondary methods (short reference)

The following section intentionally keeps secondary/internal methods concise.

Public Static Methods

Method	Brief
`public static table(string $table)`	Start a new query for the given table.

Public Instance Methods

Method	Brief
`public __construct(?string $table = null)`	Simple query builder for SQL SELECT statements.
`public __toString()`	Cast the query to string.
`public addSelect(string	array ...$columns)`
`public aggregate(string $function, string $column = '*')`	Execute an aggregate function on the query.
`public avg(string $column)`	Get the average of the given column.
`public count(string $column = '*')`	Get the count of the results.
`public crossJoin(string $table)`	Add a cross join clause.
`public distinct(bool $value = true)`	Force the query to return distinct results.
`public doesntExist()`	Determine if no rows exist for the current query.
`public exists()`	Determine if any rows exist for the current query.
`public first()`	Get the first result (model or `stdClass`).
`public firstOrFail()`	Get the first result or throw if not found.
`public firstWhere(string	callable
`public forPage(int $page, int $perPage)`	Set current page and limit for pagination.
`public from(string $table)`	Set the table to select from.
`public fromRaw(string $sql, array $bindings = [])`	Set source table using raw SQL.
`public fromSub(Query	callable
`public get()`	Execute and get results as `Collection`.
`public getBindings()`	Get the flattened binding list for SQL execution.
`public getModelClass()`	Get currently configured model class.
`public groupBy(string	array ...$columns)`
`public groupByRaw(string $sql, array $bindings = [])`	Add raw group-by expression.
`public hasModel()`	Check if hydration model is configured.
`public having(string $column, mixed $operator = null, mixed $value = null, string $boolean = 'and')`	Add a having clause.
`public havingNested(callable $callback, string $boolean = 'and')`	Add nested having conditions.
`public havingRaw(string $sql, array $bindings = [], string $boolean = 'and')`	Add raw having SQL.
`public join(string $table, string	callable $first, ?string $operator = null, ?string $second = null, bool $where = false)`
`public joinRaw(string $sql, array $bindings = [])`	Add a raw join clause.
`public joinSub(Query	callable
`public joinWhere(string $table, string $first, string $operator, mixed $second)`	Join using value comparison with binding.
`public latest(string $column = 'created_at')`	Shortcut for descending order by column.
`public leftJoin(string $table, string	callable $first, ?string $operator = null, ?string $second = null, bool $where = false)`
`public leftJoinSub(Query	callable
`public leftJoinWhere(string $table, string $first, string $operator, mixed $second)`	Left join with value comparison.
`public limit(int $limit)`	Set max number of rows.
`public lock(bool	string $value = true)`
`public lockForUpdate()`	Apply `FOR UPDATE` lock.
`public max(string $column)`	Get maximum value of a column.
`public min(string $column)`	Get minimum value of a column.
`public offset(int $offset)`	Set row offset.
`public oldest(string $column = 'created_at')`	Shortcut for ascending order by column.
`public orHaving(string $column, mixed $operator = null, mixed $value = null)`	Add OR having clause.
`public orHavingNested(callable $callback)`	Add OR nested having clauses.
`public orHavingRaw(string $sql, array $bindings = [])`	Add OR raw having SQL.
`public orWhere(string	callable
`public orWhereBetween(string $column, array $values)`	Add OR between condition.
`public orWhereColumn(string $first, mixed $operator = null, ?string $second = null)`	Add OR column-to-column comparison.
`public orWhereDate(string $column, mixed $operator = null, mixed $value = null)`	Add OR date-based condition.
`public orWhereDay(string $column, mixed $operator = null, mixed $value = null)`	Add OR day-based condition.
`public orWhereExists(Query	callable
`public orWhereIn(string $column, array	Query
`public orWhereMonth(string $column, mixed $operator = null, mixed $value = null)`	Add OR month-based condition.
`public orWhereNested(callable $callback)`	Add OR nested where clause.
`public orWhereNotBetween(string $column, array $values)`	Add OR not-between condition.
`public orWhereNotExists(Query	callable
`public orWhereNotIn(string $column, array	Query
`public orWhereNotNull(string $column)`	Add OR is-not-null condition.
`public orWhereNull(string $column)`	Add OR is-null condition.
`public orWhereRaw(string $sql, array $bindings = [])`	Add OR raw where SQL.
`public orWhereTime(string $column, mixed $operator = null, mixed $value = null)`	Add OR time-based condition.
`public orWhereYear(string $column, mixed $operator = null, mixed $value = null)`	Add OR year-based condition.
`public orderBy(string $column, string $direction = 'asc')`	Add order-by clause.
`public orderByDesc(string $column)`	Add descending order-by clause.
`public orderByRaw(string $sql, array $bindings = [])`	Add raw order-by expression.
`public paginate(int $perPage = 15, int $page = 1, string	array $columns = ['*'])`
`public pluck(string $column, ?string $key = null)`	Extract a single column (optionally keyed).
`public rightJoin(string $table, string	callable $first, ?string $operator = null, ?string $second = null, bool $where = false)`
`public rightJoinSub(Query	callable
`public rightJoinWhere(string $table, string $first, string $operator, mixed $second)`	Right join with value comparison.
`public select(string	array ...$columns)`
`public selectRaw(string $sql, array $bindings = [])`	Add raw select expression.
`public selectSub(Query	callable
`public setModel(string $modelClass)`	Set model class for hydration.
`public sharedLock()`	Apply shared/read lock.
`public skip(int $offset)`	Alias of `offset()`.
`public sum(string $column)`	Get sum of a column.
`public take(int $limit)`	Alias of `limit()`.
`public toSql()`	Compile query to SQL string.
`public union(Query	callable
`public unionAll(Query	callable
`public value(string $column)`	Get one scalar from first row.
`public where(string	callable
`public whereBetween(string $column, array $values, string $boolean = 'and', bool $not = false)`	Add between / not-between where clause.
`public whereColumn(string $first, mixed $operator = null, ?string $second = null, string $boolean = 'and')`	Add column comparison where clause.
`public whereDate(string $column, mixed $operator = null, mixed $value = null, string $boolean = 'and')`	Add date-based where clause.
`public whereDay(string $column, mixed $operator = null, mixed $value = null, string $boolean = 'and')`	Add day-based where clause.
`public whereExists(Query	callable
`public whereIn(string $column, array	Query
`public whereMonth(string $column, mixed $operator = null, mixed $value = null, string $boolean = 'and')`	Add month-based where clause.
`public whereNested(callable $callback, string $boolean = 'and')`	Add grouped nested where conditions.
`public whereNotBetween(string $column, array $values, string $boolean = 'and')`	Add not-between where clause.
`public whereNotExists(Query	callable
`public whereNotIn(string $column, array	Query
`public whereNotNull(string $column, string $boolean = 'and')`	Add is-not-null where clause.
`public whereNull(string $column, string $boolean = 'and')`	Add is-null where clause.
`public whereRaw(string $sql, array $bindings = [], string $boolean = 'and')`	Add raw where SQL.
`public whereTime(string $column, mixed $operator = null, mixed $value = null, string $boolean = 'and')`	Add time-based where clause.
`public whereYear(string $column, mixed $operator = null, mixed $value = null, string $boolean = 'and')`	Add year-based where clause.
`public wrapIdentifiers(bool $value = true)`	Enable/disable identifier wrapping.

Protected Methods

Method	Brief
`protected addDateBasedWhere(string $function, string $column, mixed $operator = null, mixed $value = null, string $boolean = 'and')`	Internal helper for date-based where clauses.
`protected createSubquery(Query	callable
`protected isOperator(mixed $operator)`	Check if operator is supported.
`protected joinWithType(string $type, string $table, string	callable $first, ?string $operator = null, mixed $second = null, bool $where = false)`
`protected wrapAliasedIdentifier(string $value)`	Wrap identifier that may include alias.
`protected wrapIdentifier(string $value)`	Wrap single identifier when safe.
`protected wrapTable(string $table)`	Wrap table name when safe.

Private Methods

No private methods.

Practical notes

Favor fluent methods over raw clauses whenever possible to keep binding order safe.
get() and first() return stdClass by default, or model objects after setModel(...).
toSql() includes unions and lock clauses; use getBindings() to inspect bound values.
For complex reports, combine fromSub/joinSub with groupBy/having before resorting to raw SQL.

See also: ActiveRecord, Collection, QueryGrammar, JoinClause, Database, QueryFilter.

Query

Pair framework: Query

Mental model

Core example

Main methods (deep dive)

1) table(), from(), fromRaw(), fromSub()

2) select(), addSelect(), selectRaw(), selectSub()

3) where(...) family

4) whereExists(), whereIn() with subqueries

5) join(), leftJoin(), rightJoin(), crossJoin(), joinWhere()

6) joinSub(), leftJoinSub(), rightJoinSub()

7) groupBy(), groupByRaw(), having(), havingRaw(), nested having

8) get(), first(), firstOrFail(), firstWhere(), value(), pluck()

9) setModel() hydration to ActiveRecord

10) Aggregates and existence checks

11) Ordering and paging: orderBy*, latest, oldest, limit/offset, forPage, paginate

12) Unions: union(), unionAll()

13) SQL debugging: toSql(), getBindings(), __toString()

14) Locks: lock(), lockForUpdate(), sharedLock()

15) Identifier wrapping: wrapIdentifiers(false)

Practical cookbook (additional examples)

Dynamic search form with optional filters

Monthly KPI report (grouping + having + ordering)

Top customers with fallback to 0 using subquery join

Compliance check with whereNotExists

Lightweight endpoints: pluck() and value()

Secondary methods (short reference)

Public Static Methods

Public Instance Methods

Protected Methods

Private Methods

Practical notes

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

1) `table()`, `from()`, `fromRaw()`, `fromSub()`

2) `select()`, `addSelect()`, `selectRaw()`, `selectSub()`

3) `where(...)` family

4) `whereExists()`, `whereIn()` with subqueries

5) `join()`, `leftJoin()`, `rightJoin()`, `crossJoin()`, `joinWhere()`

6) `joinSub()`, `leftJoinSub()`, `rightJoinSub()`

7) `groupBy()`, `groupByRaw()`, `having()`, `havingRaw()`, nested having

8) `get()`, `first()`, `firstOrFail()`, `firstWhere()`, `value()`, `pluck()`

9) `setModel()` hydration to ActiveRecord

11) Ordering and paging: `orderBy*`, `latest`, `oldest`, `limit/offset`, `forPage`, `paginate`

12) Unions: `union()`, `unionAll()`

13) SQL debugging: `toSql()`, `getBindings()`, `__toString()`

14) Locks: `lock()`, `lockForUpdate()`, `sharedLock()`

15) Identifier wrapping: `wrapIdentifiers(false)`

Top customers with fallback to `0` using subquery join

Compliance check with `whereNotExists`

Lightweight endpoints: `pluck()` and `value()`