Eloquent Performance: Eager-loading

As I discussed earlier N+1 is a common problem in ORM systems such as Eloquent.

Laravel provides a smart way to overcome N+1 issues, but first you need to understand the problem before the solution makes sense.

Say you want to list all the users along with their company names:

// App\Http\Controllers\UserController

public function index()
{
    $users = User::take(20)->get();
    return view('users.index', compact('users'));
}

<-- resources/views/users/index.blade.php -->

@foreach ($users as $user)
    <p>Name: {{ $user->name }}</p>
    <p>Company: {{ $user->company->name }}</p>
@endforeach

If you inspect the Queries tab, you will see several companies queries due to the company calling within the foreach ($user->company->name):

select * from `companies` where `companies`.`id` = 5843 limit 1;
select * from `companies` where `companies`.`id` = 2116 limit 1;
select * from `companies` where `companies`.`id` = 1345 limit 1;
# ...

That is an N+1 problem.

Laravel uses the term lazy-loading in contrast to eager-loading.

You can mitigate this with eager loading.

Eager-loading uses one single SELECT statement instead of N SELECTs, thanks to the SQL IN operator:

select * from `companies` where `companies`.`id` in (5843, 2116, 1345);

Here is how you fix it:

$users = App\Models\User::with('company')
    ->take(20)
    ->get();

By using the with method you are telling Eloquent to eager-load the company relationship.

You can also tell Eloquent to eager-load the relationships directly from the model, although I do not recommend this approach:

# App\Models\User

// The `Company` will be automatically loaded
// whenever we access the `User` model.

protected $with = ['company'];

You can also eager-load multiple relationships:

$users = User::with(['company', 'posts'])->paginate();

Nested eager loading is supported as well using the dot notation.

Say you want to eager-load the App\Models\Country in the App\Models\Company from the App\Models\User:

$users = User::with('company.country')->paginate();

Now, you can safely access the App\Models\Country in the App\Models\Company without worrying about N+1 problems:

@foreach ($users as $user)
    <p>Company: {{ $user->company->name }}, Country {{ $user->company->country->name }}</p>
@endforeach

That covers eager-loading. But will it solve all your N+1 problems? No. It solves some of them, but not all.

Here is a question for you: How can you get the latest login date from App\Models\User in an efficient way?

By efficient way, I mean a single SQL query.

Think about that for a moment.

In the meantime, here is a piece of code I found in a real project:

// App\Models\User
public function logins()
{
    return $this->hasMany(Login::class);
}

public function latestLogin()
{
    return $this->logins->latest();
}

Can you see the problem?

It fetches all the login records for the current user, and then it uses only the last one.

While this is not a big problem with a few login entries, it becomes a serious problem when there are 1000+ records per user.

I will show you a proper solution in the next upcoming post.

Summary

Eager loading with with() -- replaces N individual queries with a single query using the SQL IN operator. This is your first line of defense against N+1 problems.
Nested eager loading -- use dot notation like with('company.country') to eager-load deeply nested relationships in one call.
Avoid $with on the model -- auto-loading relationships on every query is wasteful. Use with() explicitly where you need it.
Eager loading has limits -- it does not solve every N+1 problem. For cases like fetching the latest related record, you need subqueries.

#Summary

Summary