How to Order By Relationship Count Using Laravel Eloquent

In Laravel, sorting data based on the count of related records is a common and technique, often used to create sorted overviews of data in real-world applications. Laravel Eloquent supports an elegant syntax to sort by the count of related records:

$posts = Post::withCount('comments')
->orderBy('comments_count', 'desc')
->get();

This blog post will describe how to build an application that uses this technique to order posts by their number of comments. We’ll cover all the necessary steps, including creating the required models and their relationships, populating the tables with test data, and making a simple page that prints a list of posts sorted by their comment counts.

Let’s get started!

Step 1: Create a Laravel Project

Begin by creating a new Laravel project if you haven’t done so already. Open your terminal and run:

composer create-project laravel/laravel order-by-relationship
cd order-by-relationship

Step 2: Create Migration and Model

Run the following artisan commands to generate the migration and model files:

php artisan make:migration create_posts_table --create=posts
php artisan make:model Post

php artisan make:migration create_comments_table --create=comments
php artisan make:model Comment

Step 3: Add Migration Code

Open the generated migration file (e.g., create_posts_table) and define the columns. Ensure there is a foreign key for comments relationship.

For the posts migration use:

database/migrations/2024_01_20_165620_create_posts_table.php
<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->string('excerpt')->nullable();
            $table->text('body');
            // Add other post fields as needed
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('posts');
    }
};

For the comments migration use:

database/migrations/2024_01_20_170730_create_comments_table.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.
     */
    public function up(): void
    {
        Schema::create('comments', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('post_id');
            $table->text('content');
            $table->timestamps();

            // Foreign key relationship
            $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('comments');
    }
};

Step 4: Run the Migrations

Create the table by running the following Artisan command:

php artisan migrate

Step 5: Define Models and Relationships

In the Post model, define the relationship with comments:

app/Models/Post.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $fillable = [
        'title',
        'excerpt',
        'body',
    ];
    
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

For the Comments model use:

app/Models/Comment.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Comment extends Model
{
    protected $fillable = [
        'post_id',
        'content',
    ];

    // Define the relationship with the Post model
    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

Step 6: Add Testdata

  1. Create a Seeder:

Run the following Artisan command to generate a seeder:

php artisan make:seeder PostSeeder
  1. Add Seeder code

Open the generated seeder file (PostSeeder.php) and update the run method with the following code:

database/seeders/PostSeeder.php
<?php

namespace Database\Seeders;

use App\Models\Post;
use Illuminate\Database\Seeder;

class PostSeeder extends Seeder
{
    public function run()
    {
        // Create 6 posts
        $posts = [];
        for ($postNumber = 1; $postNumber <= 6; $postNumber++) {
            $post = Post::create([
                'title' => "Post $postNumber",
                'body' => "This is the content of Post $postNumber."
            ]);

            // Attach comments to specific posts
            $commentsCount = rand(1, 5); // Adjust the range as needed
            for ($commentNumber = 1; $commentNumber <= $commentsCount; $commentNumber++) {
                $post->comments()->create([
                    'content' => "Comment $commentNumber on Post $postNumber."
                ]);
            }

            $posts[] = $post;
        }
    }
}
  1. Run the Seeder:

After updating the seeder file, run the following Artisan command to execute the seeder:

ShellScript
php artisan db:seed --class=PostSeeder

You should now have plenty of testdata as shown in the screenshot below:

Our Database Filled With Testdata as Generated by Our PostSeeder

Note that we used a simple seeder without factories to keep this tutorial simple. For more complex scenarios, I recommend checking out Seeding a Model With Relationship Using Laravel Factories. This method allows for a much more flexible definition of data for records and related data, specifically useful for automated testing.

Step 7: Fetching Ordered Results

Now, when querying posts, you can order them by the count of comments. For testing purposes we add a simple route to our web.php:

PHP
use App\Models\Post;
use Illuminate\Support\Facades\Route;

Route::get('/test', function () {
    $posts = Post::withCount('comments')
        ->orderBy('comments_count', 'desc')
        ->get();

    foreach ($posts as $post) {
        dump("Post with title `$post->title` has $post->comments_count comments");
    }
});

Step 8: Run the application

Run the following command to run the application:

php artisan serve

Now to your browser and open:

http://127.0.0.1:8000/test

You’ll see the posts ordered by their comment count, starting with the highest number:

Conclusion

Ordering by relationship count in Laravel is a powerful feature that helps with creating useful overviews for your application. By following the steps in this post, you can easily implement this functionality in your real-world projects.

As an extra exercise, consider formatting the list of posts as a table and adding a feature to toggle sorting from the lowest to the highest count and vice versa. Let me know how it went in the comments. Happy coding!

References:

Johan van den Broek

Johan is the creator of laracoding.com. As a child, he began tinkering with various programming languages, many of which have been long forgotten today. Currently, he works exclusively with PHP, and his passion for programming remains to this day.

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Posts