Eloquent relationships, eager loading and query evidence

What you will learn

• Define a one-to-many Eloquent relationship with an explicit foreign key.
• Compare eager and lazy loading while preserving identical owned report data.
• Measure query counts without pretending fewer queries proves lower latency.

Theory: relationships and query timing

A project has many tasks linked by project_id. The tasks() method builds a relationship query. Accessing the tasks property retrieves its collection, possibly lazily if it was not loaded. These are different operations.

A loop over N projects can issue one parent query plus one child query per project: the N+1 pattern. Eager loading with tasks fetches parents and their children in batches. In this fixture with two selected parents, eager loading makes two SELECTs; lazy property access makes three. That count is verified, not assumed from method names.

This report filters parents by owner_id and then traverses their children. The owner argument must come from a trusted authenticated/authorized caller, not a public user-supplied query parameter. A positive integer check is not authorization. The connection argument is trusted test plumbing only.

Worked example: real related tables

Required: Laravel 13, PHP 8.4+ and isolated MySQL 8.4 testing database ending in _test. Verify the exact connection before running; the site’s own checker additionally pins its dedicated database. Use a personal practice project, not a company or learner database. Laragon/XAMPP are optional.

Copy the first three files into content/labs and StudyEloquentReportTest.php into tests/Feature. The test loads the teaching classes explicitly; it creates only uniquely prefixed tables on a cloned connection and cleans those tables afterward. No production route is registered and no real user data is needed.

StudyProject.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class StudyProject extends Model
{
    protected $table = 'practice_projects';

    public $timestamps = false;

    public function tasks(): HasMany
    {
        return $this->hasMany(StudyProjectTask::class, 'project_id')->orderBy('id');
    }
}

StudyProjectTask.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class StudyProjectTask extends Model
{
    protected $table = 'practice_project_tasks';

    public $timestamps = false;
}

StudyProjectReport.php

<?php

namespace StudyLabs;

use App\Models\StudyProject;
use InvalidArgumentException;

class StudyProjectReport
{
    public function forOwner(int $ownerId, ?string $connection = null): array
    {
        if ($ownerId < 1) {
            throw new InvalidArgumentException('Use a positive owner ID.');
        }

        return StudyProject::on($connection)->where('owner_id', $ownerId)
            ->with('tasks')->orderBy('id')->get()
            ->map(fn (StudyProject $project): array => [
                'id' => $project->id,
                'title' => $project->title,
                'tasks' => $project->tasks->pluck('title')->all(),
            ])->all();
    }
}

StudyEloquentReportTest.php

<?php

namespace Tests\Feature;

use App\Models\StudyProject;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use StudyLabs\StudyProjectReport;
use Tests\TestCase;

require_once dirname(__DIR__, 2).'/content/labs/StudyProject.php';
require_once dirname(__DIR__, 2).'/content/labs/StudyProjectTask.php';
require_once dirname(__DIR__, 2).'/content/labs/StudyProjectReport.php';

class StudyEloquentReportTest extends TestCase
{
    private string $connectionName;

    private string $prefix;

    private array $ownedTables = [];

    protected function setUp(): void
    {
        parent::setUp();
        $this->assertTrue(app()->environment('testing'));
        $this->assertSame('mysql', DB::connection()->getDriverName());
        $this->assertStringEndsWith('_test', DB::connection()->getDatabaseName());
        $this->prefix = 'labrel_'.bin2hex(random_bytes(5)).'_';
        $this->connectionName = $this->prefix.'connection';
        $config = config('database.connections.mysql');
        $config['prefix'] = $this->prefix;
        config(['database.connections.'.$this->connectionName => $config]);
        $schema = Schema::connection($this->connectionName);
        $this->assertFalse($schema->hasTable('practice_projects'));
        $this->assertFalse($schema->hasTable('practice_project_tasks'));
        $schema->create('practice_projects', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('owner_id');
            $table->string('title');
        });
        $this->ownedTables[] = 'practice_projects';
        $schema->create('practice_project_tasks', function (Blueprint $table) {
            $table->id();
            $table->foreignId('project_id')->constrained('practice_projects')->cascadeOnDelete();
            $table->string('title');
        });
        $this->ownedTables[] = 'practice_project_tasks';
        $connection = DB::connection($this->connectionName);
        $connection->table('practice_projects')->insert([
            ['id' => 1, 'owner_id' => 101, 'title' => 'First'],
            ['id' => 2, 'owner_id' => 101, 'title' => 'Empty'],
            ['id' => 3, 'owner_id' => 202, 'title' => 'Other owner'],
        ]);
        $connection->table('practice_project_tasks')->insert([
            ['id' => 1, 'project_id' => 1, 'title' => 'Read'],
            ['id' => 2, 'project_id' => 1, 'title' => 'Test'],
            ['id' => 3, 'project_id' => 3, 'title' => 'Private'],
        ]);
    }

    protected function tearDown(): void
    {
        try {
            if (isset($this->connectionName)) {
                $connection = DB::connection($this->connectionName);
                $this->assertStringEndsWith('_test', $connection->getDatabaseName());
                $connection->disableQueryLog();
                $this->assertMatchesRegularExpression('/^labrel_[a-f0-9]{10}_$/', $this->prefix);
                foreach (array_reverse($this->ownedTables) as $table) {
                    Schema::connection($this->connectionName)->drop($table);
                    $this->assertFalse(Schema::connection($this->connectionName)->hasTable($table));
                }
                DB::purge($this->connectionName);
                config(['database.connections.'.$this->connectionName => null]);
            }
        } finally {
            parent::tearDown();
        }
    }

    public function test_eager_and_lazy_reports_match_but_query_counts_differ(): void
    {
        $connection = DB::connection($this->connectionName);
        $connection->flushQueryLog();
        $connection->enableQueryLog();
        $eager = (new StudyProjectReport)->forOwner(101, $this->connectionName);
        $this->assertCount(2, $connection->getQueryLog());
        $connection->flushQueryLog();
        $lazy = StudyProject::on($this->connectionName)->where('owner_id', 101)->orderBy('id')->get()
            ->map(function (StudyProject $project): array {
                $project->preventsLazyLoading = false;

                return ['id' => $project->id, 'title' => $project->title, 'tasks' => $project->tasks->pluck('title')->all()];
            })->all();
        $this->assertCount(3, $connection->getQueryLog());
        $connection->disableQueryLog();
        $this->assertSame($eager, $lazy);
        $this->assertSame([
            ['id' => 1, 'title' => 'First', 'tasks' => ['Read', 'Test']],
            ['id' => 2, 'title' => 'Empty', 'tasks' => []],
        ], $eager);
        $this->assertSame([['id' => 3, 'title' => 'Other owner', 'tasks' => ['Private']]], (new StudyProjectReport)->forOwner(202, $this->connectionName));
    }

    public function test_empty_owner_uses_one_query_and_invalid_owner_is_rejected(): void
    {
        $connection = DB::connection($this->connectionName);
        $connection->flushQueryLog();
        $connection->enableQueryLog();
        $this->assertSame([], (new StudyProjectReport)->forOwner(999, $this->connectionName));
        $this->assertCount(1, $connection->getQueryLog());
        $connection->disableQueryLog();
        $this->expectException(\InvalidArgumentException::class);
        (new StudyProjectReport)->forOwner(0, $this->connectionName);
    }
}

Run and inspect the assertions

Windows PowerShell, inside your practice Laravel project:

php artisan test --compact --filter=StudyEloquentReportTest

Ubuntu Bash, inside your practice Laravel project:

php artisan test --compact --filter=StudyEloquentReportTest

The owned result for dummy owner101 is First with Read/Test, followed by Empty with no tasks. Dummy owner202 receives only Other owner with Private. Owner999 returns an empty list. Invalid owner0 is rejected. Dummy IDs here are fixture labels, not actual authenticated accounts.

For owner101 the test measures two eager queries and three lazy queries while asserting identical arrays. No-parent input uses one query because there are no child IDs to load. The relation’s connection inherits the selected parent connection. Query logging is enabled only around measured operations and disabled before cleanup.

Windows/MySQL8.4.3 was executed. Ubuntu execution, real endpoint authorization and performance/latency benchmarks are not claimed. This is an integration fixture, not a production report endpoint.

Guided lab

1. Draw the parent/child keys and predict each owner’s exact ordered result.
2. Run the tests and inspect query count separately from returned data. Zero-task parents must remain in the report.
3. In a disposable copy, replace with(tasks) with lazy access and observe the query-count regression. The fixture intentionally permits lazy access per model only for that comparison; it does not turn off a production-wide safeguard.
4. Try calling tasks()->count() inside the loop after eager loading. It makes a new query; counting the loaded tasks collection is different. Record the difference.
5. When selecting fewer child columns, retain id and project_id so Eloquent can connect children to parents. Verify the result, not just that SQL ran.

Memory, counts and scope

Eager loading avoids this per-parent query pattern, but still materializes the selected parents and children. It is not automatically appropriate for an unbounded report. Use deliberate pagination/chunking and preserve the same permission scope. If only a count is needed, investigate withCount rather than hydrating every child.

Fewer queries can still be slower or use more memory if they fetch excessive rows. Observe real SQL, dataset size, indexes and workload timings before making a performance claim. Do not leave unbounded query logs enabled in production. Parent filtering protects this fixture’s traversal, not every possible unrelated child endpoint.

Independent exercise

Build a task-count report for the same owner using withCount. Keep parent ordering, include the zero-task project, expose only selected fields and compare counts with the original report.

Test owner isolation, no parents, zero tasks and a new child. Add a bounded page and explain how ordering/cursors interact with changed rows. Hint: preserve the parent primary key and the foreign key required by the relation; treat a passed owner ID as trusted only after the request layer has authorized it.

Common mistakes and interview

Avoid counting relation queries inside a loop, omitting hydration keys from selected columns, removing ownership filters while optimizing, dropping zero-task parents, claiming all eager loads are one query, or equating query count with elapsed-time proof.

Interview: “How did you detect and fix N+1?” Give the initial query/data evidence, a precise eager/count strategy, identical-result assertions, query-count scope and remaining memory/workload limits.

English practice and glossary

Relationship: a model-level association. Foreign key: a stored reference between rows. Lazy loading: querying related data when accessed. Eager loading: retrieving related data in advance. Hydration: creating model objects from rows. N+1: one parent query plus repeated per-parent queries.

Say: “The first version ran __ queries for __ parents. I changed __ and verified __ stayed the same. I have not measured __.” Save actual dummy-data evidence in your journal.

Recap and next step

Measure both data correctness and query behavior. Eager loading reduces this fixture’s N+1 pattern without changing its report. Keep owner authorization at the request boundary and bound real report sizes. Next combine API pagination/resources, policies and failure-aware services.