The Query Builder

By the end of this lesson you will be able to write reports that join several tables, group and total them, insert or update many rows in one go, and protect multi-step changes with transactions. The query builder is what Eloquent uses underneath, and it is the right tool when you need numbers rather than model objects.

Eloquent or query builder?

The query builder is a PHP way to write SQL. You start with DB::table('name'), chain methods like where() and join(), and finish with get(), first() or an aggregate. Laravel builds the SQL with prepared statements, so values are always escaped.

An analogy: Eloquent is ordering from a restaurant menu, with nicely plated dishes (models with all their features). The query builder is the kitchen's direct order slip: plainer, faster, and you can ask for exactly what you want.

Basic queries

use Illuminate\Support\Facades\DB;

$rows = DB::table('students')
    ->select('id', 'name', 'city')
    ->where('city', 'Rohtak')
    ->orderBy('name')
    ->get();

foreach ($rows as $r) {
    echo $r->id, ' ', $r->name, ' (', get_class($r), ")\n";
}

echo DB::table('courses')->where('code', 'NEET-C')->value('fee');
var_dump(DB::table('students')->where('phone', '9812000004')->exists());

2 Aarav Gupta (stdClass)
1 Priya Sharma (stdClass)
42000
bool(true)

Each row is a plain stdClass object, not a model. There are no casts or accessors, so dates are strings. value() returns one column of the first row, and exists() returns a boolean.

The where family works as in Eloquent: where('fee', '>', 20000), orWhere(), whereIn('city', [...]), whereNull('email'), whereBetween('paid_on', [...]), whereDate('created_at', '2026-11-02'), and when() for optional filters.

Joins and grouping: a collection report

The accounts team wants to know how much each batch has collected. That needs three tables: payments, admissions and batches.

$report = DB::table('payments')
    ->join('admissions', 'admissions.id', '=', 'payments.admission_id')
    ->join('batches', 'batches.id', '=', 'admissions.batch_id')
    ->select(
        'batches.name as batch',
        DB::raw('COUNT(payments.id) as receipts'),
        DB::raw('SUM(payments.amount) as collected'),
    )
    ->groupBy('batches.name')
    ->orderByDesc('collected')
    ->get();

foreach ($report as $r) {
    echo "{$r->batch}: {$r->receipts} receipts, ₹{$r->collected}\n";
}

JEE Morning A: 3 receipts, ₹135000
NEET Evening B: 1 receipts, ₹20000

join() is an inner join: only payments that match an admission and a batch are counted. DB::raw() lets you put an SQL expression in the select list. Write table-qualified names like payments.id when two tables have a column with the same name.

Left joins and HAVING: who still owes fees?

A left join keeps every row from the first table, even when there is no match in the second. Kabir has no payments yet, so an inner join would hide him. That is exactly the student the office must call.

$dues = DB::table('admissions')
    ->join('students', 'students.id', '=', 'admissions.student_id')
    ->leftJoin('payments', 'payments.admission_id', '=', 'admissions.id')
    ->selectRaw('students.name, admissions.total_fee - admissions.discount - COALESCE(SUM(payments.amount), 0) as due')
    ->groupBy('admissions.id', 'students.name', 'admissions.total_fee', 'admissions.discount')
    ->havingRaw('due > ?', [0])
    ->orderByDesc('due')
    ->get();

Kabir Singh: ₹42000
Priya Sharma: ₹30000
Meera Nair: ₹20000

Aarav paid in full, so HAVING due > 0 removed him. COALESCE(SUM(...), 0) turns "no payments" (NULL) into 0.

Inserting and upserting

DB::table('courses')->insert([
    'name' => 'Olympiad Maths', 'code' => 'OLY-M', 'fee' => 12000, 'duration_months' => 3,
    'created_at' => now(), 'updated_at' => now(),
]);

The query builder does not fill timestamps for you, so set them yourself.

An upsert inserts new rows and updates existing ones in one statement. It is perfect for importing a fee list from a spreadsheet:

DB::table('courses')->upsert(
    [
        ['name' => 'Olympiad Maths', 'code' => 'OLY-M', 'fee' => 15000, 'duration_months' => 3],
        ['name' => 'Olympiad Science', 'code' => 'OLY-S', 'fee' => 15000, 'duration_months' => 3],
    ],
    uniqueBy: ['code'],
    update: ['fee'],
);

Array
(
    [OLY-M] => 15000
    [OLY-S] => 15000
)

OLY-M existed, so only its fee changed from 12000 to 15000; OLY-S was inserted. uniqueBy must match a unique index. Laravel 13 throws an error if uniqueBy is empty.

Transactions: all or nothing

Some actions need several writes that must succeed together. Recording two instalments, or creating an admission plus its first payment, should never stop halfway. A transaction groups the writes: if anything fails, every change inside is undone (rolled back).

It is like a UPI transfer. Money leaves your account and arrives in the shop's account. If the second step fails, the first is reversed. You never lose money in between.

try {
    DB::transaction(function () {
        DB::table('payments')->insert([/* receipt RCP-3001, ₹10,000 for Kabir */]);
        DB::table('payments')->insert([/* receipt RCP-3001 again, ₹5,000 */]);
    });
} catch (\Illuminate\Database\QueryException $e) {
    echo 'Rolled back: ', $e->getMessage();
}

echo DB::table('payments')->where('admission_id', 4)->count(), ' payments for Kabir';

Rolled back: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint faile...
0 payments for Kabir

The second insert broke the unique receipt number rule. Because both were inside one transaction, the first insert was undone too, and Kabir still has zero payments. DB::transaction() commits automatically when the closure finishes without an exception.

Large tables: chunking and raw SQL

To process thousands of rows (for example, sending SMS reminders), read them in pieces so memory stays low:

DB::table('students')->orderBy('id')->chunk(2, function ($chunk) {
    echo 'chunk of ', $chunk->count(), ': ', $chunk->pluck('name')->implode(', '), "\n";
});

chunk of 2: Priya Sharma, Aarav Gupta
chunk of 2: Meera Nair, Kabir Singh

Real code would use chunks of 500 or 1,000. If you update the same column you filter on while chunking, use chunkById() so no rows are skipped.

For queries that are easier in plain SQL, DB::select() runs them with bindings and returns stdClass rows: DB::select('select city, count(*) as total from students where city <> ? group by city', ['Pune']).

Common mistakes

• Expecting models from DB::table(). You get stdClass rows: no relationships, casts or accessors.
• Forgetting timestamps on insert. created_at stays NULL unless you set it.
• Grouping without all selected columns. MySQL in strict mode rejects a SELECT column that is not grouped or aggregated. Add it to groupBy().
• Concatenating input into raw SQL. Always use ? bindings.
• Doing related writes without a transaction. A crash in the middle leaves half-saved data.

Key points

• DB::table() starts a query builder; it returns plain stdClass rows and runs prepared statements.
• join, leftJoin, groupBy, having and DB::raw()/selectRaw() build reports across tables.
• insert does not set timestamps; upsert inserts or updates by a unique key in one statement.
• DB::transaction() makes several writes all-or-nothing.
• chunk() and chunkById() process large tables in small pieces; raw SQL always uses bindings.

$byMode = DB::table('payments')
    ->select('mode', DB::raw('COUNT(*) as payments'), DB::raw('SUM(amount) as total'))
    ->whereBetween('paid_on', ['2026-11-01', '2026-11-30'])
    ->groupBy('mode')
    ->orderByDesc('total')
    ->get();

DB::transaction(function () use ($studentId, $batchId) {
    $admissionId = DB::table('admissions')->insertGetId([
        'student_id' => $studentId, 'batch_id' => $batchId, 'admitted_on' => now()->toDateString(),
        'total_fee' => 42000, 'discount' => 0, 'status' => 'active',
        'created_at' => now(), 'updated_at' => now(),
    ]);

    DB::table('payments')->insert([
        'admission_id' => $admissionId, 'amount' => 10000, 'mode' => 'upi',
        'receipt_no' => 'RCP-4001', 'paid_on' => now()->toDateString(),
        'created_at' => now(), 'updated_at' => now(),
    ]);
});