Laravel with(): How to Solve the N+1 Problem with Eager Loading Relationships
Tags: php, laravel, eloquentorm, backend
Published: 2023-01-20 01:29:57

---
title: Laravel with(): How to Solve the N+1 Problem with Eager Loading Relationships
published: true
date: 2022-12-31 06:50:02 UTC
tags: 
- PHP
- Laravel
- EloquentORM
- Backend
canonical_url: https://fajarwz.com/blog/laravel-with-how-to-solve-the-n+1-problem-with-eager-loading-relationships/
---

![Relationship illustration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/54pie58o65uorj8d1tmt.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8voj9lcdom5gadp9u965.png)

### 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
```

### Create Posts Migration

Create a migration for `posts` table .

```
php artisan make:migration "create posts table"
```

Write the migration based on the database diagram above.

```php
<?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');
    }
};
```

### Create Post Model

Create `Post` model first for `posts` table

```
php artisan make:model Post
```

### 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
<?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,
            ]);
        }
    }
}
```

### Install Laravel Debugbar

To see what query we execute in our Laravel project we need [barryvdh/laravel-debugbar](https://github.com/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
```

### Modify Web Route

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

```php
<?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
    ]);
});
```

> **Read also:**
> * [How to Fix 'Client Intended to Send Too Large Body' Nginx 413 Error](https://fajarwz.com/blog/how-to-fix-client-intended-to-send-too-large-body-nginx-413-error/)
> * [Create Global Query With Laravel Global Scope](https://fajarwz.com/blog/create-global-query-with-laravel-global-scope/)

### Modify Welcome view

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

```php
<!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>
```

## 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ikqejlh569danoxoorrs.png)

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.

```php
...

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
    ]);
});

...
```

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/si1lwaurkt2s0rpigopj.png)

### 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
<?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);
    }
}

```

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

```php
...

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

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

...
```

And we will get the same result.

![Eager load posts global with](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4viagxulnayivclpx2v7.png)

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

```php
$posts = Post::without('user')->get();
```

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

```php
$posts = Post::withOnly('user')->get();
```

## 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](https://github.com/fajarwz/blog-laravel-with-method).

## Reference
[Eloquent: Relationships - Eager Loading - Laravel](https://laravel.com/docs/9.x/eloquent-relationships#eager-loading)

## Read Also
[Create Login With Google OAuth Using Laravel Socialite](https://fajarwz.com/blog/create-login-with-google-oauth-using-laravel-socialite/)