Optimize Laravel Eloquent Queries with Eager Loading

Published on by

Optimize Laravel Eloquent Queries with Eager Loading image

Object Relational Mapping (ORM) makes working with databases amazingly simple. While defining database relationships in an object-oriented way makes it easy to query related model data, developers might not pay attention to the underlying database calls.

What is Eager Loading?

At its core Eager Loading, is telling Eloquent that you want to grab a model with specific relationships that way the framework produces a more performant query to grab all the data you will need. By eager loading, you can take many queries down to just one or two.

In this tutorial, we will set up some example relationships and then walk through how queries change with and without eager loading. I like to get my hands directly on code and experiment with things, and I hope to illustrate how eager loading works with some examples that will further help you understand how to optimize your queries.

Introduction

At a basic level, ORMs “lazy” load-related model data. After all, how’s the ORM supposed to know your intention? Perhaps you will never actually use the related model’s data after querying the model. Not optimizing the query is known as an “N+1” issue. When you use objects to represent queries, you might be making queries without even knowing it.

Imagine that you were received 100 objects from the database, and each record had 1 associated model (i.e. belongsTo). Using an ORM would produce 101 queries by default; one query for the original 100 records, and additional query for each record if you accessed the related data on the model object. In pseudo-code, let’s say you wanted to list all published authors that have contributed a post. From a collection of posts (each post having one author) you could get a list of author names like so:

$posts = Post::published()->get(); // one query
 
$authors = array_map(function($post) {
// Produces a query on the author model
return $post->author->name;
}, $posts);

We are not telling the model that we need all the authors, so an individual query happens each time we get the author’s name from the individual Post model instances.

Eager Loading

As I mentioned, ORMs “lazy” load associations. If you intend to use the associated model data you can trim that 101 query total to 2 queries using eager loading. You just need to tell the model what you need it to load eagerly.

Here’s an example from the Rails Active Record guide on using eager loading. As you can see, the concept is quite similar to Laravel’s eager loading concept.

# Rails
posts = Post.includes(:author).limit(100)
 
# Laravel
$posts = Post::with('author')->limit(100)->get();

I find that I receive a better understanding by exploring ideas from a wider perspective. The Active Record documentation covers some examples that can further help the idea resonate.

Laravel’s Eloquent ORM

Laravel’s ORM, called Eloquent, makes it trivial to eager load models, and even eagerly loading nested relationships. Let’s build on the Post model example and learn how to work with eager loading in a Laravel project.

We will work with the project setup and then go through some eager loading examples more in-depth to wrap up.

Setup

Let’s set up some database migrations, models, and database seeding to experiment with eager loading. If you want to follow along, I am assuming you have access to a database and can go through a basic Laravel installation.

Using the Laravel installer, let’s create the project:

laravel new blog-example

Edit your .env values to match your database or choice.

Next, we will create three models so you can experiment with eager loading nested relationships. The example is simple so we can focus on eager loading, and I’ve omitted things you might use like indexes and foreign key constraints.

php artisan make:model -m Post
php artisan make:model -m Author
php artisan make:model -m Profile

The -m flag creates a migration to go along with the model that you will use to create the table schema.

The data models will have the following associations:

Post -> belongsTo -> Author
Author -> hasMany -> Post
Author -> hasOne -> Profile

Migrations

Let’s create a simple schema for each table; I’ve only provided the up() method because Laravel will generate the down() automatically for new tables. The migration files are in the database/migrations/ folder:

<?php
 
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
 
class CreatePostsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('posts', function (Blueprint $table) {
$table->increments('id');
$table->unsignedInteger('author_id');
$table->string('title');
$table->text('body');
$table->timestamps();
});
}
 
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('posts');
}
}
<?php
 
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
 
class CreateAuthorsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('authors', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->text('bio');
$table->timestamps();
});
}
 
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('authors');
}
}
<?php
 
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
 
class CreateProfilesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('profiles', function (Blueprint $table) {
$table->increments('id');
$table->unsignedInteger('author_id');
$table->date('birthday');
$table->string('city');
$table->string('state');
$table->string('website');
$table->timestamps();
});
}
 
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('profiles');
}
}

Models

You need to define model associations to experiment more with eager loading. When you ran the php artisan make:model command, it created model files for you.

The first model is app/Post.php:

<?php
 
namespace App;
 
use Illuminate\Database\Eloquent\Model;
 
class Post extends Model
{
public function author()
{
return $this->belongsTo(Author::class);
}
}

Next, the app\Author.php has two associations:

<?php
 
namespace App;
 
use Illuminate\Database\Eloquent\Model;
 
class Author extends Model
{
public function profile()
{
return $this->hasOne(Profile::class);
}
 
public function posts()
{
return $this->hasMany(Post::class);
}
}

With the models and migrations in place, you can run the migrations and then move on to experimenting with eager loading with some seeded model data.

php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated: 2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated: 2014_10_12_100000_create_password_resets_table
Migrating: 2017_08_04_042509_create_posts_table
Migrated: 2017_08_04_042509_create_posts_table
Migrating: 2017_08_04_042516_create_authors_table
Migrated: 2017_08_04_042516_create_authors_table
Migrating: 2017_08_04_044554_create_profiles_table
Migrated: 2017_08_04_044554_create_profiles_table

If you check the database, you should see all the created tables!

Model Factories

To create some fake data that we can run queries against, let’s add a few model factories that we can use to seed the database with test data.

Open the database/factories/ModelFactory.php file and append the following three factories to the file below the existing User factory:

/** @var \Illuminate\Database\Eloquent\Factory $factory */
$factory->define(App\Post::class, function (Faker\Generator $faker) {
return [
'title' => $faker->sentence,
'author_id' => function () {
return factory(App\Author::class)->create()->id;
},
'body' => $faker->paragraphs(rand(3,10), true),
];
});
 
/** @var \Illuminate\Database\Eloquent\Factory $factory */
$factory->define(App\Author::class, function (Faker\Generator $faker) {
return [
'name' => $faker->name,
'bio' => $faker->paragraph,
];
});
 
$factory->define(App\Profile::class, function (Faker\Generator $faker) {
return [
'birthday' => $faker->dateTimeBetween('-100 years', '-18 years'),
'author_id' => function () {
return factory(App\Author::class)->create()->id;
},
'city' => $faker->city,
'state' => $faker->state,
'website' => $faker->domainName,
];
});

These factories will make it easy to populate a bunch of posts that we can query; we can use them to create associated model data with database seeding.

Open the database/seeds/DatabaseSeeder.php file and add the following to the DatabaseSeeder::run() method:

public function run()
{
$authors = factory(App\Author::class, 5)->create();
$authors->each(function ($author) {
$author
->profile()
->save(factory(App\Profile::class)->make());
$author
->posts()
->saveMany(
factory(App\Post::class, rand(20,30))->make()
);
});
}

You create five authors and then loop through each author and save an associated profile and many posts (between 20 and 30 posts per author).

We are done creating migrations, models, model factories, and database seeds. We can combine it all and re-run our migrations and database seeding in a repeatable way:

php artisan migrate:refresh
php artisan db:seed

You should now have some seeded data that you can play around with, in the next section. Note that Laravel 5.5 includes a migrate:fresh command which drops the tables instead of rolling back migrations and then re-applying them.

Experimenting with Eager Loading

We are finally ready to see eager loading in action. In my opinion, the best way to visualize eager loading is logging queries to the storage/logs/laravel.log file.

To log database queries, you can either enable the MySQL log or listen for database calls from Eloquent. To log queries through Eloquent, add the following code to the app/Providers/AppServiceProvider.php boot() method:

namespace App\Providers;
 
use DB;
use Log;
use Illuminate\Support\ServiceProvider;
 
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
DB::listen(function($query) {
Log::info(
$query->sql,
$query->bindings,
$query->time
);
});
}
 
// ...
}

I like to wrap this listener around a configuration check so that I can toggle query logging on and off. You can also get this information from the Laravel Debugbar.

Let’s see what happens when we don’t load model relations eagerly. Clear out your storage/log/laravel.log file and run the “tinker” command.

php artisan tinker
 
>>> $posts = App\Post::all();
>>> $posts->map(function ($post) {
... return $post->author;
... });
>>> ...

If you check your laravel.log file, you should see a bunch of queries to get the associated author:

[2017-08-04 06:21:58] local.INFO: select * from `posts`
[2017-08-04 06:22:06] local.INFO: select * from `authors` where `authors`.`id` = ? limit 1 [1]
[2017-08-04 06:22:06] local.INFO: select * from `authors` where `authors`.`id` = ? limit 1 [1]
[2017-08-04 06:22:06] local.INFO: select * from `authors` where `authors`.`id` = ? limit 1 [1]
....

Empty your laravel.log file again, and this time call with() to eager load the author records:

php artisan tinker
 
>>> $posts = App\Post::with('author')->get();
>>> $posts->map(function ($post) {
... return $post->author;
... });
...

This time you should only see two queries in the log file. The first query for all the posts, and the second query for all the associated authors.

[2017-08-04 07:18:02] local.INFO: select * from `posts`
[2017-08-04 07:18:02] local.INFO: select * from `authors` where `authors`.`id` in (?, ?, ?, ?, ?) [1,2,3,4,5]

If you had multiple related associations, you can eager load them with an array:

$posts = App\Post::with(['author', 'comments'])->get();

Nested Eager Loading in Eloquent

Nested eager loading works the same way. In our example, the Author model has one profile. Thus, a query will be executed for each profile.

Empty out the laravel.log file and let’s try it out:

php artisan tinker
 
>>> $posts = App\Post::with('author')->get();
>>> $posts->map(function ($post) {
... return $post->author->profile;
... });
...

You will now have seven queries. The first two are eagerly loaded, and then each time we get a new profile a query is required to get the profile data for each author.

With eager loading we can avoid the extra queries in nested relationships. Clear your laravel.log file one last time and run the following:

>>> $posts = App\Post::with('author.profile')->get();
>>> $posts->map(function ($post) {
... return $post->author->profile;
... });

Now, you should only have 3 queries total:

[2017-08-04 07:27:27] local.INFO: select * from `posts`
[2017-08-04 07:27:27] local.INFO: select * from `authors` where `authors`.`id` in (?, ?, ?, ?, ?) [1,2,3,4,5]
[2017-08-04 07:27:27] local.INFO: select * from `profiles` where `profiles`.`author_id` in (?, ?, ?, ?, ?) [1,2,3,4,5]

Lazy Eager Loading

You might only need to gather associated models based on a conditional. In this case, you can lazily invoke additional queries for related data:

php artisan tinker
 
>>> $posts = App\Post::all();
...
>>> $posts->load('author.profile');
>>> $posts->first()->author->profile;
...

You should see three queries total, but only if $posts->load() is called.

Conclusion

Hopefully, you learned a bit more about eager loading models and understand how it works on a deeper level. The eager loading documentation is comprehensive, and I hope that the additional hands-on practice helps you feel more confident with optimizing your relationship queries.

Paul Redmond photo

Staff writer at Laravel News. Full stack web developer and author.

Cube

Laravel Newsletter

Join 40k+ other developers and never miss out on new tips, tutorials, and more.

Laravel Forge logo

Laravel Forge

Easily create and manage your servers and deploy your Laravel applications in seconds.

Laravel Forge
Tinkerwell logo

Tinkerwell

The must-have code runner for Laravel developers. Tinker with AI, autocompletion and instant feedback on local and production environments.

Tinkerwell
No Compromises logo

No Compromises

Joel and Aaron, the two seasoned devs from the No Compromises podcast, are now available to hire for your Laravel project. ⬧ Flat rate of $7500/mo. ⬧ No lengthy sales process. ⬧ No contracts. ⬧ 100% money back guarantee.

No Compromises
Laravel Idea for PhpStorm logo

Laravel Idea for PhpStorm

Ultimate PhpStorm plugin for Laravel developers, delivering lightning-fast code completion, intelligent navigation, and powerful generation tools to supercharge productivity.

Laravel Idea for PhpStorm
Kirschbaum logo

Kirschbaum

Providing innovation and stability to ensure your web application succeeds.

Kirschbaum
Shift logo

Shift

Running an old Laravel version? Instant, automated Laravel upgrades and code modernization to keep your applications fresh.

Shift
Bacancy logo

Bacancy

Supercharge your project with a seasoned Laravel developer with 4-6 years of experience for just $2500/month. Get 160 hours of dedicated expertise & a risk-free 15-day trial. Schedule a call now!

Bacancy
Lucky Media logo

Lucky Media

Get Lucky Now - the ideal choice for Laravel Development, with over a decade of experience!

Lucky Media
Lunar: Laravel E-Commerce logo

Lunar: Laravel E-Commerce

E-Commerce for Laravel. An open-source package that brings the power of modern headless e-commerce functionality to Laravel.

Lunar: Laravel E-Commerce
LaraJobs logo

LaraJobs

The official Laravel job board

LaraJobs
SaaSykit: Laravel SaaS Starter Kit logo

SaaSykit: Laravel SaaS Starter Kit

SaaSykit is a Multi-tenant Laravel SaaS Starter Kit that comes with all features required to run a modern SaaS. Payments, Beautiful Checkout, Admin Panel, User dashboard, Auth, Ready Components, Stats, Blog, Docs and more.

SaaSykit: Laravel SaaS Starter Kit
Supercharge Your SaaS Development with FilamentFlow: The Ultimate Laravel Filament Boilerplate logo

Supercharge Your SaaS Development with FilamentFlow: The Ultimate Laravel Filament Boilerplate

Build your SaaS application in hours. Out-of-the-box multi-tenancy and seamless Stripe integration. Supports subscriptions and one-time purchases, allowing you to focus on building and creating without repetitive setup tasks.

Supercharge Your SaaS Development with FilamentFlow: The Ultimate Laravel Filament Boilerplate
JetShip - Laravel Starter Kit logo

JetShip - Laravel Starter Kit

A Laravel SaaS Boilerplate and a starter kit built on the TALL stack. It includes authentication, payments, admin panels, and more. Launch scalable apps fast with clean code, seamless deployment, and custom branding.

JetShip - Laravel Starter Kit
Rector logo

Rector

Your partner for seamless Laravel upgrades, cutting costs, and accelerating innovation for successful companies

Rector
MongoDB logo

MongoDB

Enhance your PHP applications with the powerful integration of MongoDB and Laravel, empowering developers to build applications with ease and efficiency. Support transactional, search, analytics and mobile use cases while using the familiar Eloquent APIs. Discover how MongoDB's flexible, modern database can transform your Laravel applications.

MongoDB

The latest

View all →
Streamlining Route Parameters in Laravel Using URL Defaults image

Streamlining Route Parameters in Laravel Using URL Defaults

Read article
Flexible Docker Images with PHP INI Environment Variables image

Flexible Docker Images with PHP INI Environment Variables

Read article
Dynamic Form Validation in Laravel with prohibited_if image

Dynamic Form Validation in Laravel with prohibited_if

Read article
Add Approvals to Your Laravel Application image

Add Approvals to Your Laravel Application

Read article
Enhancing Data Processing with Laravel's transform() Method image

Enhancing Data Processing with Laravel's transform() Method

Read article
Get Xdebug Working With Docker and PHP 8.4 in One Minute image

Get Xdebug Working With Docker and PHP 8.4 in One Minute

Read article