Eager Loading in Laravel 4

Dec 30, 2013

Table of contents:

What is the N + 1 problem?
What is Eager Loading?
Laravel Eager Loading extras
Conclusion

When building web applications, it’s important to understand where bottle necks are likely to appear first. Reducing the number of database queries down to be as efficient as possible is the first step towards building an application that will scale.

However a lot of developers make the mistake of writing inefficient database queries that are hard to spot and using an ORM like Eloquent can hide the problem until it’s too late. You don’t want to wake up one morning to find your application has been hosed because you wrote a query that killed your database.

In this article I’m going to be looking at Eager Loading in Laravel 4. Eager Loading helps to alleviate the N + 1 problem so that when you make database queries, you aren’t secretly running hundreds of queries when you should just be running one or two.

What is the N + 1 problem?

The N + 1 problem is where you load a set of records and then you request an attribute which is a relation to each of those records.

For example, say your application has users and posts. Your relationship might look something like this in your User model:

/**
 * Post relationship
 */
public function posts()
{
    return $this->hasMany('Post');
}

For each user you want to list their post titles. So you might write something like this:

$users = User::all();

foreach ($users as $user) {
    echo $user->posts->title;
}

This would work exactly how you intended it to.

However, under the surface your application is actually not very efficient. when you run the code above, you would be running the following database queries:

SELECT * from users;
SELECT * from posts WHERE user_id = 1;
SELECT * from posts WHERE user_id = 2;
SELECT * from posts WHERE user_id = 3;
SELECT * from posts WHERE user_id = 4;
SELECT * from posts WHERE user_id = 5;

So if you have 1000 users, your database would be running 1000 + 1 queries to run this little chunk of code. That’s where the N + 1 name comes from.

What is Eager Loading?

Eager loading is a method for defining what we are going to need ahead of time so that we can run more efficient database queries.

So instead of getting all users and then requesting the posts for each user, we can declare that we also want the user’s posts when we select the users.

Fortunately Laravel 4 makes this really easy.

So in the case of the example above, we could write the following which would be much more efficient:

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

foreach ($users as $user) {
    echo $user->posts->title;
}

Using Eager Loading, Laravel would actually be running the following queries:

select * from users
select * from posts where user_id in (1, 2, 3, 4, 5)

So as you can see, we’ve reduced 1000+ queries down to two. Running the two queries above is much more efficient than running them as separate queries, especially if your database is on another server.

Laravel Eager Loading extras

Laravel makes it really easy to do Eager Loading on your database, but it can also do much more than simply solving the N + 1 problem.

Loading multiple relationships

For example, say you wanted to load multiple relationships. You can achieve this by simply passing multiple options to the with method:

$users = User::with("posts", "followers")->get();

Loading nested relationships

Sometimes you will want to load relationships that are nested. A common example of this would be to load all users, their posts and the comments on a post. As you can imagine this is some pretty heavy query work, but eager loading can make this more efficient.

$users = User::with("posts.comments")->get();

Eager Loading with constraints

And finally, often you will want to eager load relationships but with a constraint because you know you don’t need to load every record.

Laravel 4 solves this problem by allowing you to pass a Closure to specify the WHERE clause. For example:

$users = User::with([
    "posts" => function ($query) {
        $query->where("published", "=", 1);
    },
])->get();

In the example above, I would only be loading a user’s posts if they were published. If I was using this query to display a list of posts on the user’s profile page, there is no need to load any draft or unpublished posts.

Conclusion

In the early days of building a web application, eager loading can take very inefficient queries and boil them down to become efficient. This can make your application feel much quicker because you will be able to massively reduce the back and forth with the database.

Eager Loading is an excellent practice for building high quality web applications and so you should leverage this technique whenever you want to load records and relationships.

However, Eager Loading will not solve all of your problems forever. If you are lucky enough to really find traction with your application there will come a point where even Eager Loading is not efficient enough for you.

In next week’s tutorial I will be looking at the next step towards building efficient web applications by using Cache.

This is a series of posts on building an entire Open Source application called Cribbb. All of the tutorials will be free to web, and all of the code is available on GitHub.