Abstract

Eloquent, Laravel’s ORM, provides an intuitive interface for database interaction, but its ease of use can mask significant performance pitfalls, most notably the N+1 query problem. This chapter explores advanced optimization techniques in Laravel 11. We delve beyond basic eager loading into constrained eager loads, subquery ordering, and efficient memory management for large datasets using cursors.

The N+1 Problem and Eager Loading

The N+1 problem occurs when code iterates over a collection of parent models and accesses a relationship on each iteration.

• The Problem: Fetching 100 posts, then accessing $post->author in a loop results in 1 query for posts + 100 queries for authors.
• The Fix: Post::with(‘author’)->get(). This executes exactly 2 queries: one for posts, and one WHERE id IN (…) for authors.

Advanced: Constraining Eager Loads

A common requirement is to eager load a relationship, but only a subset of it. For example, loading users and their latest 5 published posts.

$users = User::with()->get();

This loads the data efficiently while applying SQL filters at the database level rather than filtering collections in memory.

Ordering by Relationships (Subqueries)

A more complex challenge is sorting the parent model based on an aggregate of the child model. For example, “Show users ordered by their last login date.” You cannot simply do orderBy(‘logins.created_at’) because the logins table isn’t joined.

The Subquery Solution:

Laravel allows injecting subqueries into the SELECT clause to create virtual columns for sorting.

$users = User::addSelect(['last_login_at' => Login::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
    ->take(1)
])->orderBy('last_login_at', 'desc')->paginate(20);

This executes a single, highly optimized SQL query. It avoids the performance penalty of using join (which can duplicate rows) or hydrating thousands of models just to sort them in PHP.

Memory Management: Chunk vs. Cursor

When processing millions of records (e.g., a background migration), standard get() or even chunk() can be inefficient.

• chunk(): Fetches block of records (e.g., 1000) into memory. It’s good, but loading 1000 Eloquent models is still memory-intensive.
• cursor(): Uses PHP Generators and database cursors to stream a single record at a time. This keeps memory usage flat, regardless of dataset size.

Cursor Example:

foreach (User::cursor() as $user) {
    // Memory usage remains constant even if processing 1M users
    $user->calculateStats();
}