Eloquent Relations, N+1 Query and Debugbar

Finally, let's build the functionality to manage the posts. Posts CRUD is almost identical to the categories, so we will make this part quickly.

First, we will create a Controller and add a Route with the link in the navigation.

php artisan make:controller PostController --resource --model=Post

We can create another group inside the first one for the Route and assign is_admin Middleware to it.

routes/web.php:

Route::get('/', function () {
    return view('welcome');
});
 
Route::middleware('auth')->group(function () {
    // ...
 
    Route::middleware('is_admin')->group(function () { 
        Route::resource('categories', \App\Http\Controllers\CategoryController::class)->middleware('is_admin'); 
        Route::resource('categories', \App\Http\Controllers\CategoryController::class); 
        Route::resource('posts', \App\Http\Controllers\PostController::class);
    });
});
 
require __DIR__.'/auth.php';

In the navigation, we should also add an if statement to check if a user is an admin and show links only then. In Blade, it is written using the @if Blade directive.

resources/views/layouts/navigation.blade.php:

// ...
 
<!-- Navigation Links -->
<div class="hidden space-x-8 sm:-my-px sm:ms-10 sm:flex">
    <x-nav-link :href="route('dashboard')" :active="request()->routeIs('dashboard')">
        {{ __('Dashboard') }}
    </x-nav-link>
    @if(auth()->user()->is_admin) 
        <a href="{{ route('categories.index') }}" class="inline-flex items-center px-1 pt-1 border-b-2 border-transparent text-sm font-medium leading-5 text-gray-500 hover:text-gray-700 hover:border-gray-300 focus:outline-none focus:text-gray-700 focus:border-gray-300 transition duration-150 ease-in-out">
            Categories
        </a>
        <a href="{{ route('posts.index') }}" class="inline-flex items-center px-1 pt-1 border-b-2 border-transparent text-sm font-medium leading-5 text-gray-500 hover:text-gray-700 hover:border-gray-300 focus:outline-none focus:text-gray-700 focus:border-gray-300 transition duration-150 ease-in-out"> 
            Posts
        </a>
    @endif
</div>
 
// ...

app/Http/Controllers/PostController.php:

use App\Models\Post;
use App\Models\Category;
use Illuminate\Http\Request;
 
class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
 
        return view('posts.index', compact('posts'));
    }
 
    public function create()
    {
        $categories = Category::all();
 
        return view('posts.create', compact('categories'));
    }
 
    public function store(Request $request)
    {
        Post::create([
            'title' => $request->input('title'),
            'text' => $request->input('text'),
            'category_id' => $request->input('category_id'),
        ]);
 
        return redirect()->route('posts.index');
    }
 
    public function show(Post $post)
    {
        //
    }
 
    public function edit(Post $post)
    {
        $categories = Category::all();
 
        return view('posts.edit', compact('post', 'categories'));
    }
 
    public function update(Request $request, Post $post)
    {
        $post->update([
            'title' => $request->input('title'),
            'text' => $request->input('text'),
            'category_id' => $request->input('category_id'),
        ]);
 
        return redirect()->route('posts.index');
    }
 
    public function destroy(Post $post)
    {
        $post->delete();
 
        return redirect()->route('posts.index');
    }
}

For the create and edit pages, we also need a list of categories. You can pass multiple variables to the View using the compact() method.

resources/views/posts/index.blade.php:

<x-app-layout>
    <x-slot name="header">
        <h2 class="font-semibold text-xl text-gray-800 leading-tight">
            {{ __('Posts') }}
        </h2>
    </x-slot>
 
    <div class="py-12">
        <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
            <div class="bg-white overflow-hidden shadow-sm sm:rounded-lg">
                <div class="p-6 bg-white border-b border-gray-200">
                    <a href="{{ route('posts.create') }}">Add new post</a>
                    <br /><br />
                    <table>
                        <thead>
                            <tr>
                                <th>Title</th>
                                <th>Category</th>
                                <th></th>
                            </tr>
                        </thead>
                        <tbody>
                            @foreach($posts as $post)
                                <tr>
                                    <td>{{ $post->title }}</td>
                                    <td>???</td>
                                    <td>
                                        <a href="{{ route('posts.edit', $post) }}">Edit</a>
                                        <form method="POST" action="{{ route('posts.destroy', $post) }}">
                                            @csrf
                                            @method('DELETE')
                                            <button type="submit" onclick="return confirm('Are you sure?')">Delete</button>
                                        </form>
                                    </td>
                                </tr>
                            @endforeach
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</x-app-layout>

We will add a relationship to the post and show the category later in this lesson.

resources/views/posts/create.blade.php:

<x-app-layout>
    <x-slot name="header">
        <h2 class="font-semibold text-xl text-gray-800 leading-tight">
            {{ __('New Post') }}
        </h2>
    </x-slot>
 
    <div class="py-12">
        <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
            <div class="bg-white overflow-hidden shadow-sm sm:rounded-lg">
                <div class="p-6 text-gray-900">
                    <form method="POST" action="{{ route('posts.store') }}">
                        @csrf
 
                        <div>
                            <div>
                                <label for="title">Title:</label>
                            </div>
                            <input type="text" name="title" id="title" class="border-gray-300 focus:border-indigo-500 focus:ring-indigo-500 rounded-md shadow-sm">
                        </div>
                        <div>
                            <div>
                                <label for="text">Text:</label>
                            </div>
                            <textarea name="text" id="text" class="border-gray-300 focus:border-indigo-500 focus:ring-indigo-500 rounded-md shadow-sm"></textarea>
                        </div>
                        <div>
                            <div>
                                <label for="category_id">Category:</label>
                            </div>
                            <select name="category_id" id="category_id" class="rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50">
                                @foreach ($categories as $category)
                                    <option value="{{ $category->id }}">{{ $category->name }}</option>
                                @endforeach
                            </select>
                        </div>
                        <div>
                            <button type="submit" class="inline-flex items-center px-4 py-2 bg-gray-800 border border-transparent rounded-md font-semibold text-xs text-white uppercase tracking-widest hover:bg-gray-700 focus:bg-gray-700 active:bg-gray-900 focus:outline-none focus:ring-2 focus:ring-indigo-500 focus:ring-offset-2 transition ease-in-out duration-150">
                                Save
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        </div>
    </div>
</x-app-layout>

resources/views/posts/edit.blade.php:

<x-app-layout>
    <x-slot name="header">
        <h2 class="font-semibold text-xl text-gray-800 leading-tight">
            {{ __('Edit Post') }}
        </h2>
    </x-slot>
 
    <div class="py-12">
        <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
            <div class="bg-white overflow-hidden shadow-sm sm:rounded-lg">
                <div class="p-6 text-gray-900">
                    <form method="POST" action="{{ route('posts.update', $post) }}">
                        @csrf
                        @method('PUT')
 
                        <div>
                            <div>
                                <label for="title">Title:</label>
                            </div>
                            <input type="text" name="title" id="title" value="{{ $post->title }}" class="border-gray-300 focus:border-indigo-500 focus:ring-indigo-500 rounded-md shadow-sm">
                        </div>
                        <div>
                            <div>
                                <label for="text">Text:</label>
                            </div>
                            <textarea name="text" id="text" class="border-gray-300 focus:border-indigo-500 focus:ring-indigo-500 rounded-md shadow-sm">{{ $post->text }}</textarea>
                        </div>
                        <div>
                            <div>
                                <label for="category_id">Category:</label>
                            </div>
                            <select name="category_id" id="category_id" class="rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50">
                                @foreach ($categories as $category)
                                    <option value="{{ $category->id }}" @selected($category->id == $post->category_id)>{{ $category->name }}</option>
                                @endforeach
                            </select>
                        </div>
                        <div>
                            <button type="submit" class="inline-flex items-center px-4 py-2 bg-gray-800 border border-transparent rounded-md font-semibold text-xs text-white uppercase tracking-widest hover:bg-gray-700 focus:bg-gray-700 active:bg-gray-900 focus:outline-none focus:ring-2 focus:ring-indigo-500 focus:ring-offset-2 transition ease-in-out duration-150">
                                Save
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        </div>
    </div>
</x-app-layout>

In the category's edit form, we have a Blade directive @selected. If the condition is true, the @selected attribute will add the selected tag.

Now, let's show the category name. We have a column category_id in the posts table. How do we show from the post category name?

For that, we define the Eloquent relationship. In the Model, you define relationship as a public method. There are various relationship types, but in our case, the post belongs to a category: that is a one-to-many relation.

app/Models/Post.php:

class Post extends Model
{
    use HasFactory;
 
    protected $fillable = ['title', 'text', 'category_id'];
 
    public function category() 
    {
        return $this->belongsTo(Category::class);
    }
}

Then, we can use a category on a post Model and call the field from the categories table: $post->category->name.

resources/views/posts/index.blade.php:

// ...
 
<tbody>
    @foreach($posts as $post)
        <tr>
            <td>{{ $post->title }}</td>
            <td>???</td> 
            <td>{{ $post->category->name }}</td> 
            <td>
                <a href="{{ route('posts.edit', $post) }}">Edit</a>
                <form method="POST" action="{{ route('posts.destroy', $post) }}">
                    @csrf
                    @method('DELETE')
                    <button type="submit" onclick="return confirm('Are you sure?')">Delete</button>
                </form>
            </td>
        </tr>
    @endforeach
</tbody>
 
// ...

In the table, we can now see the category's name.

Very Important: Eager Loading

But there is a catch. You shouldn't use relationships just like that. There is a thing called eager loading to prevent too many SQL queries to the database.

Currently, this page would make three queries:

1. To get the list of the posts.
2. To get the category of the first post.
3. To get the category of the second post.

And there may be many more queries if there are more posts: one query for each of them.

This is the most typical and common mistake in the performance of Laravel projects. This is also called the N+1 query problem.

To test how many queries are executed, we can use a package barryvdh/laravel-debugbar.

composer require barryvdh/laravel-debugbar --dev

After reloading the page, you will see a bar at the bottom. And the most important tab of the bar is Queries. In the Queries tab we can see two duplicate queries.

In the Controller, instead of calling all() on the Post Model, we must use the with() and provide the relationships that should be eagerly loaded.

app/Http/Controllers/PostController.php:

class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all(); 
        $posts = Post::with('category')->get(); 
 
        return view('posts.index', compact('posts'));
    }
 
    // ...
}

The all() method is used when you don't have any conditions. If there are any conditions, then at the end, the get() method should be used.

Now, Eloquent will load all the categories of all posts in one query.