Query Builder & Raw SQL

Eloquent is the everyday way to talk to the database, but underneath every model query sits the Query Builder: a fluent PHP API that writes SQL for you and binds every value safely. Reach for it directly for reports, aggregates, joins across many tables and bulk updates, anywhere you want rows rather than model objects. This lesson covers the queries you'll write most, using a real reporting endpoint in this app.

Try it: a real report#

GET /api/demo/reports runs one of three Query Builder reports over this site's Projects & Tasks data. It returns the rows and the SQL the builder generated, with its bindings. Pick a report and a priority filter and watch both change.

Notice that choosing a priority never puts the word high into the SQL string. It appears as ?, and high travels in bindings. That separation is what makes the Query Builder safe from SQL injection.

Getting rows#

DB::table() starts a query. The method that ends it decides what comes back:

use Illuminate\Support\Facades\DB;

$tasks = DB::table('tasks')->get();                 // Collection of stdClass rows
$task = DB::table('tasks')->where('id', 5)->first(); // one row or null
$title = DB::table('tasks')->where('id', 5)->value('title'); // a single column value
$titles = DB::table('tasks')->pluck('title', 'id');  // [id => title, ...]
$exists = DB::table('tasks')->where('priority', 'high')->exists(); // true / false

Rows are plain stdClass objects: $task->title works, but there are no casts, relationships or accessors. That's the main trade-off against Eloquent, and also why the builder is lighter for reports.

Where clauses#

Every where adds a condition joined with AND. Group OR conditions in a closure so they get their own brackets:

DB::table('tasks')
    ->where('priority', 'high')
    ->whereNull('completed_at')
    ->whereIn('project_id', [1, 2])
    ->whereBetween('due_on', [now(), now()->addWeek()])
    ->where(function ($query) {
        $query->where('title', 'like', '%login%')
              ->orWhere('description', 'like', '%login%');
    })
    ->get();

That compiles to ... where priority = ? and completed_at is null and project_id in (?, ?) and due_on between ? and ? and (title like ? or description like ?). Without the closure, the orWhere would apply to the whole query and return every task mentioning "login", completed or not.

Conditional filters read best with when(), which only adds the clause if the value is truthy. The report endpoint uses exactly this for its optional priority filter:

Joins, aggregates and groupBy#

Reports usually combine tables and count things. This is the real method behind the endpoint above:

app/Http/Controllers/Api/QueryBuilderReportController.php

private function query(string $report, ?string $priority): Builder
{
    return match ($report) {
        // LEFT JOIN keeps projects with no matching tasks; the filter sits in the join
        'tasks-per-project' => DB::table('projects')
            ->leftJoin('tasks', function (JoinClause $join) use ($priority): void {
                $join->on('tasks.project_id', '=', 'projects.id')
                    ->when($priority, fn (JoinClause $join, string $priority) => $join->where('tasks.priority', $priority));
            })
            ->select('projects.name', 'projects.slug')
            ->selectRaw('count(tasks.id) as total_tasks')
            ->selectRaw('sum(case when tasks.id is not null and tasks.completed_at is null then 1 else 0 end) as open_tasks')
            ->groupBy('projects.id', 'projects.name', 'projects.slug')
            ->orderByDesc('total_tasks')
            ->orderBy('projects.name'),

        'priority-breakdown' => DB::table('tasks')
            ->select('priority')
            ->selectRaw('count(*) as tasks')
            ->selectRaw('sum(case when completed_at is null then 1 else 0 end) as open_tasks')
            ->when($priority, fn (Builder $query, string $priority) => $query->where('priority', $priority))
            ->groupBy('priority')
            ->orderBy('priority'),

        'busiest-assignees' => DB::table('tasks')
            ->join('users', 'users.id', '=', 'tasks.user_id')
            ->whereNull('tasks.completed_at')
            ->when($priority, fn (Builder $query, string $priority) => $query->where('tasks.priority', $priority))
            ->select('users.name')
            ->selectRaw('count(*) as open_tasks')
            ->groupBy('users.id', 'users.name')
            ->orderByDesc('open_tasks')
            ->orderBy('users.name')
            ->limit(5),
    };
}

Points worth noticing:

• leftJoin vs join. join is an inner join: a project with no tasks would disappear. leftJoin keeps it with total_tasks = 0. Filtering inside the join closure keeps that behaviour; a normal where('tasks.priority', ...) after the join would drop empty projects again.
• selectRaw for aggregates. count(), sum() and case when are written as raw SQL fragments. They contain no user input, so they're safe.
• groupBy every selected column. MySQL's ONLY_FULL_GROUP_BY mode (on by default) rejects queries that select a column they don't group by.

For a single number, skip groupBy and call the aggregate directly:

$open = DB::table('tasks')->whereNull('completed_at')->count();
$latest = DB::table('tasks')->max('due_on');
$average = DB::table('tasks')->avg('id');

Use having() to filter on an aggregate, for example projects with more than five open tasks: ->groupBy('project_id')->havingRaw('count(*) > ?', [5]).

Raw expressions, safely#

Raw methods (selectRaw, whereRaw, orderByRaw, havingRaw, DB::raw) pass your string to the database untouched. They're fine for SQL you wrote; they're dangerous for anything a user typed.

// ✗ SQL injection: $search goes straight into the SQL string
DB::table('tasks')->whereRaw("title like '%{$search}%'")->get();

// ✓ The value is a binding, so the database never treats it as SQL
DB::table('tasks')->whereRaw('title like ?', ["%{$search}%"])->get();

// ✓ Better still, no raw SQL at all
DB::table('tasks')->whereLike('title', "%{$search}%")->get();

Inserts, updates, deletes and transactions#

The builder writes too, and for bulk changes it's much faster than looping over models, because it sends one statement:

// Illustrative — the public demo API never writes
DB::table('tags')->insert([
    ['name' => 'urgent', 'created_at' => now(), 'updated_at' => now()],
    ['name' => 'blocked', 'created_at' => now(), 'updated_at' => now()],
]);

$affected = DB::table('tasks')
    ->where('due_on', '<', now())
    ->whereNull('completed_at')
    ->update(['priority' => 'high', 'updated_at' => now()]);

DB::table('tasks')->where('completed_at', '<', now()->subYear())->delete();

DB::table('tags')->upsert(
    [['name' => 'urgent', 'updated_at' => now()]],
    uniqueBy: ['name'],
    update: ['updated_at'],
);

Builder writes skip Eloquent: no model events, no automatic timestamps, no casts. Set updated_at yourself, and remember that listeners such as LogTaskCompletion won't fire.

When several writes must succeed or fail together, wrap them in a transaction. If the closure throws, everything inside it is rolled back:

DB::transaction(function () use ($from, $to) {
    DB::table('tasks')->where('project_id', $from->id)->update(['project_id' => $to->id]);
    DB::table('projects')->where('id', $from->id)->delete();
}, attempts: 3); // retry on deadlock

Seeing the SQL#

Every builder can tell you what it will run. Use these while developing:

$query = DB::table('tasks')->where('priority', 'high');

$query->toSql();       // "select * from `tasks` where `priority` = ?"
$query->getBindings(); // ["high"]
$query->toRawSql();    // "select * from `tasks` where `priority` = 'high'" (debugging only)
$query->dd();          // dumps SQL + bindings and stops

To watch every query a request runs, listen for them. This app's demo API does that in a middleware, and it's where the X-Query-Count header in every live request on this site comes from:

app/Http/Middleware/AddQueryCountHeader.php

public function handle(Request $request, Closure $next): Response
{
    $startedAt = microtime(true);
    $queryCount = 0;

    DB::listen(function () use (&$queryCount) {
        $queryCount++;
    });

    $response = $next($request);

    $response->headers->set('X-Query-Count', (string) $queryCount);
    $response->headers->set('X-Response-Time', round((microtime(true) - $startedAt) * 1000, 1).'ms');

    return $response;
}

Query Builder or Eloquent?#

They mix freely. Task::query()->toBase() turns an Eloquent query into a plain builder, and every Eloquent builder accepts the same where, join and selectRaw methods.

Key takeaways#

• DB::table() builds SQL fluently and returns plain rows. get, first, value, pluck, count and exists decide the shape of the result.
• Values always travel as bindings, never inside the SQL string. Keep it that way in raw methods by passing ? placeholders and an array.
• Group orWhere in a closure, add optional filters with when(), and use leftJoin when rows without matches must stay.
• Aggregates use selectRaw + groupBy (+ having). Group by every selected column.
• Builder writes are fast and skip model events and timestamps. Wrap related writes in DB::transaction().
• toSql(), getBindings(), toRawSql() and DB::listen() show exactly what reaches the database.