DEV Community

Fajar Windhu Zulfikar
Fajar Windhu Zulfikar

Posted on • Originally published at fajarwz.com on

Laravel with(): How to Solve the N+1 Problem with Eager Loading Relationships

Relationship illustration

Eloquent ORM makes it easier to access the database. ORM, or Object-Relational Mapping, is a programming technique for converting data between a Relational Database Management System (RDBMS) and an object-oriented programming language. ORM allows us to access a database using objects rather than writing raw SQL queries.

Laravel also has this feature called Eloquent ORM.

When we use Eloquent ORM in Laravel, we can use the eloquent relationship feature. This allows us to create model relationships for relational database tables in Eloquent ORM.

Laravel's eloquent relationships can be loaded using two methods:

  1. Eager loading
  2. Lazy loading

Project Example

For example we have a blog web. We have two tables named users and posts. users and posts have a one to many relation.

Table relation

Create Laravel Project

Let's practice this. Create a Laravel project and adjust your .env.

composer create-project laravel/laravel:^9.0 blog-laravel-with-method
Enter fullscreen mode Exit fullscreen mode

Create Posts Migration

Create a migration for posts table .

php artisan make:migration "create posts table"
Enter fullscreen mode Exit fullscreen mode

Write the migration based on the database diagram above.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('user_id')->unsigned();
            $table->string('title');
            $table->text('content');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
};
Enter fullscreen mode Exit fullscreen mode

Create Post Model

Create Post model first for posts table

php artisan make:model Post
Enter fullscreen mode Exit fullscreen mode

Create Users and Posts Seeder

Create a seeder for both users and posts tables. I will create it inside database/seeders/DatabaseSeeder.php, so here is my DatabaseSeeder.php

<?php

namespace Database\Seeders;

// use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;
use App\Models\User;
use App\Models\Post;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        // Create the User seeder first
        $stepen = User::factory()->create([
            'name' => 'Stepen',
            'email' => 'stepen@test.com',
        ]);
        $peri = User::factory()->create([
            'name' => 'Peri',
            'email' => 'peri@test.com',
        ]);
        $bambang = User::factory()->create([
            'name' => 'Bambang',
            'email' => 'bambang@test.com',
        ]);

        // Then create the Post seeder
        for ($i=0; $i < 30; $i++) { 
            Post::create([
                'user_id' => rand($stepen->id, $bambang->id), // random between stepen's id (1) and bambang's id (3)
                'title' => 'Laravel CRUD Tutorial ' . $i,
                'content' => 'This is the content of Laravel CRUD Tutorial ' . $i,
            ]);
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Install Laravel Debugbar

To see what query we execute in our Laravel project we need barryvdh/laravel-debugbar.

To install it simply run the following command. It is recommended to use --dev flag so we just install this in development environment only.

composer require barryvdh/laravel-debugbar --dev
Enter fullscreen mode Exit fullscreen mode

Modify Web Route

Let's update the home or / route to our needs

<?php

use Illuminate\Support\Facades\Route;
use App\Models\Post; // include the Post model

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    $posts = Post::all();   // get all posts

    return view('welcome', [
        'posts' => $posts,  // send it to 'welcome' view
    ]);
});
Enter fullscreen mode Exit fullscreen mode

Read also:

Modify Welcome view

Let's just use the welcome view, modify it to our needs

<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <title>Laravel</title>

        <!-- Fonts -->
        <link href="https://fonts.bunny.net/css2?family=Nunito:wght@400;600;700&display=swap" rel="stylesheet">

        <style>
            body {
                font-family: 'Nunito', sans-serif;
            }
        </style>
    </head>
    <body>
        <h2>Posts</h2>
        @foreach ($posts as $post)
            <h3>{{ $post->title }}</h3>
            <p>{{ 'By: ' . $post->user->name }}</p>
            <p>{{ $post->content }}</p>
        @endforeach
    </body>
</html>
Enter fullscreen mode Exit fullscreen mode

Lazy Load

Eloquent relationships can be accessed as properties, but the relationship data is not actually loaded until you first access the property. This is called "lazy loading".

When you run the above code, it will return the relationship value without requiring you to write the with() method.

However, when you look at the Debugbar in the "Queries" tab, you will see that we only executed one query to get the entire posts data and N queries to get the user data associated with each post record, where N is the total number of post records we retrieve.

Lazy load posts

According to the Debugbar, we just executed 31 queries, where 30 of them are duplicated and only 1 is unique. The total duration for running these queries is 29.88ms.

This can cause a performance issue because we don't need to execute that many queries. To fix it, we can use the eager loading approach. This loads the relationship data in a single additional query, rather than one additional query for each relationship.

Eager Load

Eloquent can "eager load" relationships at the time you query the parent model. Eager loading alleviates the "N+1" query problem.

To eager load our user-post relationship we just need to modify Post::all() query and chain it with with() method.

...

Route::get('/', function () {
    $posts = Post::with('user')->get();   // get all posts and eager load the user relation

    return view('welcome', [
        'posts' => $posts,  // send it to 'welcome' view
    ]);
});

...
Enter fullscreen mode Exit fullscreen mode

When we refresh the welcome page and check the "Queries" tab in the Debugbar again, we will see that we now only execute 2 statements. The first is to get all the post records and the second is to get the user data for the owners of the post records we retrieved. The total duration has also dropped to only 8.9ms.

Eager load posts

Eager Loading Relationships By Default

If we need to eager load some relationships by default we can add $with variable to our model. With this we don't need to use with() again every time we query the model we need.

Let's update our Post model with $with.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use HasFactory;

    protected $with = ['user'];

    protected $fillable = [
        'user_id',
        'title',
        'content',
    ];

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

Enter fullscreen mode Exit fullscreen mode

And then let's just try to get the posts data without with().

...

Route::get('/', function () {
    $posts = Post::all();

    return view('welcome', [
        'posts' => $posts,
    ]);
});

...
Enter fullscreen mode Exit fullscreen mode

And we will get the same result.

Eager load posts global with

If we later need to remove the global relationship from $with for a single query, just add without method to the query

$posts = Post::without('user')->get();
Enter fullscreen mode Exit fullscreen mode

And to override all items within the $with for a single query we can use withOnly method like this.

$posts = Post::withOnly('user')->get();
Enter fullscreen mode Exit fullscreen mode

Conclusions

And that's it! We have successfully implemented eager loading examples. Now we know how to handle queries that need to call their relationships without encountering the "N+1" problem using with() or $with. Eager loading can improve the performance of our application by reducing the number of queries that need to be executed.

A repository for this example case can be found here: fajarwz/blog-laravel-with-method.

Reference

Eloquent: Relationships - Eager Loading - Laravel

Read Also

Create Login With Google OAuth Using Laravel Socialite

Top comments (0)