Week 12: Eloquent ORM II — Query Builder, Scopes & Eager Loading

1. The Query Builder

Task::where(...) chains build up a query lazily — nothing hits the database until a terminal method like ->get(), ->first() or ->count() is called:

query-builder.php

<?php

use App\Models\Task;

$tasks = Task::where('is_done', false)
    ->where('due_date', '<=', now()->addDays(7))
    ->orderBy('due_date', 'asc')
    ->limit(20)
    ->get();

$overdueCount = Task::where('is_done', false)
    ->where('due_date', '<', now())
    ->count();

$first = Task::where('user_id', 1)->first(); // one row, or null
$exists = Task::where('title', 'Ship the PR')->exists(); // bool, no rows loaded

// orWhere, whereIn, whereBetween all compose the same way
$urgent = Task::whereIn('priority', ['high', 'urgent'])
    ->whereBetween('due_date', [now(), now()->addWeek()])
    ->get();

This is a fluent wrapper over the exact same SQL structure from Week 6 — every chained method still compiles down to prepared statements with bound parameters, same as raw PDO.

2. Scopes

A repeated where() condition — like "not done" — is worth naming. A local scope is a model method prefixed scope that becomes chainable without the prefix:

app/Models/Task.php — with scopes

<?php

use Illuminate\Database\Eloquent\Builder;

class Task extends Model
{
    // ...

    public function scopePending(Builder $query): void
    {
        $query->where('is_done', false);
    }

    public function scopeOverdue(Builder $query): void
    {
        $query->where('is_done', false)->where('due_date', '<', now());
    }
}

using scopes

<?php

$pending = Task::pending()->get();
$overdueForUser = Task::overdue()->where('user_id', $userId)->get(); // scopes compose with regular where()

Scopes read like part of the model's own vocabulary — Task::overdue() documents intent far better than repeating the same two-condition where() chain at every call site, and a change to what "overdue" means only needs to happen in one place.

3. Accessors & Mutators

An accessor transforms a value when it's read; a mutator transforms it before it's saved. Both use PHP 8.1's Attribute class in modern Eloquent:

app/Models/Task.php — accessor/mutator

<?php

use Illuminate\Database\Eloquent\Casts\Attribute;

class Task extends Model
{
    protected function title(): Attribute
    {
        return Attribute::make(
            get: fn (string $value) => ucfirst($value),
            set: fn (string $value) => trim($value),
        );
    }
}

effect

<?php

$task = Task::create(['title' => '  fix login bug  ']); // stored as "fix login bug" -- trimmed
echo $task->title; // "Fix login bug" -- capitalized on read, every time it's accessed

This keeps formatting rules attached to the model itself — every place that reads or writes title gets consistent behavior automatically, instead of relying on every caller to remember to trim() and capitalize manually.

4. The N+1 Query Problem

This is the most common Eloquent performance bug, and it's easy to write by accident:

the problem

<?php

$tasks = Task::all(); // 1 query

foreach ($tasks as $task) {
    echo $task->user->name; // 1 additional query -- PER task
}
// 100 tasks = 1 + 100 = 101 queries, to render one page

Each access to $task->user inside the loop lazily fires its own query, because the relationship was never loaded up front. Eager loading fixes this by fetching all related models in one additional query, regardless of how many rows are in the loop:

the fix

<?php

$tasks = Task::with('user')->get(); // 2 queries total, no matter how many tasks

foreach ($tasks as $task) {
    echo $task->user->name; // already loaded -- no additional query
}

// Multiple relationships, and nested ones, in one call:
$tasks = Task::with(['user', 'tags'])->get();
$users = User::with('tasks.tags')->get(); // tasks, and each task's tags

Catch it before it ships

Laravel Debugbar or Model::preventLazyLoading() (set in a service provider during local development) will throw when code lazy-loads a relationship instead of silently running the extra query — turning a hard-to-notice performance bug into an immediate, loud error while you're still writing the code.

5. Factories & Seeders

Manually inserting rows to test with — as you likely did in Weeks 6 and 11 — gets tedious fast. A factory defines how to generate one fake, realistic model instance:

terminal

php artisan make:factory TaskFactory --model=Task

database/factories/TaskFactory.php

<?php

namespace Database\Factories;

use Illuminate\Database\Eloquent\Factories\Factory;

class TaskFactory extends Factory
{
    public function definition(): array
    {
        return [
            'title' => fake()->sentence(4),
            'is_done' => fake()->boolean(30), // 30% chance of true
            'due_date' => fake()->dateTimeBetween('now', '+2 weeks'),
            'user_id' => \App\Models\User::factory(), // creates a related user too, if needed
        ];
    }
}

database/seeders/DatabaseSeeder.php

<?php

namespace Database\Seeders;

use App\Models\Task;
use App\Models\User;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    public function run(): void
    {
        User::factory()
            ->count(5)
            ->has(Task::factory()->count(10))
            ->create();
    }
}

terminal

php artisan migrate:fresh --seed
# Drops all tables, re-runs every migration, then runs DatabaseSeeder
# Result: 5 users, each with 10 tasks -- 50 realistic rows, in one command

fake() (Faker under the hood) generates realistic-looking data — names, sentences, dates — so your development database looks close to production scale and shape, instead of three rows you typed in by hand.

6. Hands-on Exercise

Hands-on

Optimize the task list, then seed it with real data

Find and fix an N+1 query, add scopes, and generate realistic test data.

Requirements:

Write TaskFactory and UserFactory (or extend Laravel's default), and a seeder producing at least 10 users with 5–15 tasks and 2–4 tags each.
Add scopePending and scopeOverdue to Task, and use them in TaskController@index instead of inline where() calls.
Load the tasks index page and use DB::enableQueryLog()/DB::getQueryLog() (or Laravel Debugbar) to count how many queries it runs.
Add ->with(['user', 'tags']) to the index query and confirm the query count drops significantly, regardless of how many tasks are seeded.
Add a title accessor/mutator on Task that trims whitespace on save and capitalizes the first letter on read.

Hint

Before adding with(), the query count should scale with the number of tasks on the page (N+1). After adding it, the count should stay flat no matter how many tasks are seeded — that flatness is the actual proof the fix worked, not just "it feels faster."

7. Knowledge Check

Four quick questions. Expand each to check your answer.

Why does `Task::where('is_done', false)` not hit the database until `->get()` is called?

The query builder is lazy — each chained method (where, orderBy, a scope) just records another instruction onto the builder object without running anything. Only a terminal method (get(), first(), count(), exists()) actually compiles the accumulated instructions into SQL and executes it. This is what lets conditions be composed and reused across multiple calls before the query finally runs.

Why does looping over `Task::all()` and accessing `$task->user` inside the loop run so many queries?

Because the user relationship was never loaded up front — each access to $task->user lazily fires its own separate query the moment it's read. One query fetches the tasks, then one more query fires per task inside the loop to fetch that task's user: N+1 total queries for N tasks, which scales linearly (and badly) with how much data is on the page.

How does `Task::with('user')->get()` fix the N+1 problem?

with('user') tells Eloquent to fetch every related user for the entire result set in one additional query (typically using WHERE id IN (...) against every collected user_id), rather than one query per row. The total becomes 2 queries regardless of whether there are 10 tasks or 10,000 — flat scaling instead of linear.

Why generate test data with a factory and seeder instead of a handful of manually inserted rows?

Realistic scale exposes problems a handful of rows never would — an N+1 query might be invisible with 3 tasks but obviously slow with 500. Factories also make it trivial to regenerate a fresh, consistent dataset on demand (migrate:fresh --seed), which matters constantly during development and is exactly the kind of realistic fixture data automated tests need too, starting Week 15.