Query Builder

Query Builder#

Introduction#

The query builder is the primary SQL composition interface. It is designed to stay explicit: you can always inspect generated SQL and bindings before execution.

When to Use QueryBuilder#

Use QueryBuilder when query shape changes often or is naturally SQL-heavy:

endpoint-specific filtering and sorting
joins across multiple tables
ad-hoc reporting queries
CTE/subquery composition

If you keep repeating the same tenant/soft-delete/default-order rules for one table, switch to repository and keep QueryBuilder as an escape hatch.

Basic Select#

$rows = DB::table('users')
    ->select('id', 'name', 'email')
    ->where('active', '=', 1)
    ->orderBy('id', 'desc')
    ->limit(20)
    ->get();

Use toSql() + getBindings() during debugging to verify generated SQL:

$query = DB::table('users')->where('active', '=', 1);
$sql = $query->toSql();
$bindings = $query->getBindings();

Writes#

$id = DB::table('users')->insertGetId([
    'name' => 'Alice',
    'email' => '[email protected]',
]);

DB::table('users')->where('id', '=', $id)->update(['name' => 'Alice Updated']);

For bulk conflict handling, use upsert(). For read-back behavior after upsert, use upsertReturning() and rely on capability-aware fallback.

Running SQL Queries#

For cases where fluent chaining is not required, use raw SQL helpers:

$rows = DB::select('select * from users where active = ?', [1]);
$count = DB::scalar('select count(*) from users');

Dynamic Filtering Pattern#

$rows = DB::table('users')
    ->select('id', 'email', 'role', 'active')
    ->when($onlyActive, fn ($q) => $q->where('active', '=', 1))
    ->when($role !== null, fn ($q) => $q->where('role', '=', $role))
    ->when($search !== null && $search !== '', function ($q) use ($search) {
        $term = '%' . $search . '%';

        return $q->whereNested(function ($inner) use ($term) {
            $inner->where('email', 'like', $term)
                ->orWhere('name', 'like', $term);
        });
    })
    ->orderBy('id', 'desc')
    ->forPage($page, 20)
    ->get();

Advanced SQL#

CTE: with(), withRecursive()
Subquery source: fromSub()
Window helper: selectWindow()
Upsert returning: upsertReturning()

Example CTE:

$rows = DB::table('orders')
    ->with('big_orders', function ($q): void {
        $q->from('orders')->select('id', 'amount')->where('amount', '>', 1000);
    })
    ->from('big_orders')
    ->selectRaw('count(*) as c')
    ->get();

Reporting Scenario#

$rows = DB::table('orders as o')
    ->join('users as u', 'o.user_id', '=', 'u.id')
    ->select('u.email')
    ->selectRaw('sum(o.amount) as total_amount')
    ->selectRaw('count(*) as order_count')
    ->groupBy('u.email')
    ->having('order_count', '>=', 3)
    ->orderBy('total_amount', 'desc')
    ->get();

Pagination and Streaming#

paginate()
simplePaginate()
cursorPaginate()
chunk() / chunkById()
cursor()

Prefer chunkById() over offset-based chunking for large or changing tables. It is more stable when rows are inserted/deleted during iteration.

Branching Queries Safely#

QueryBuilder is mutable. Use cloneBuilder() when you branch from the same base query.

$base = DB::table('orders')->where('status', '=', 'paid');

$today = $base->cloneBuilder()
    ->where('created_at', '>=', $todayStart)
    ->count();

$thisMonth = $base->cloneBuilder()
    ->where('created_at', '>=', $monthStart)
    ->count();

Locks#

lockForUpdate()
sharedLock()

Lock syntax is compiled per driver. You can inspect SQL via toSql() if you need to verify emitted dialect-specific lock clauses.

Note

Use chunkById() + deterministic ordering for long-running jobs. It is safer than offset pagination under concurrent writes.