Blog

In this article, we’ll walk through how to order results by a sum of a related model’s column in Laravel. We’ll use a practical example to make it easy to understand and follow.

Prerequisites

Before we start, make sure you have the following:

1. Laravel installed.
2. A basic understanding of Eloquent relationships.
3. A database set up.

Scenario

Imagine you have two models: User and Order. Each User can have multiple Orders, and each Order has a total field that stores the order amount. Our goal is to retrieve a list of users ordered by the total amount of their orders.

Step-by-Step Guide

Step 1: Setting Up Models and Migrations

First, let’s create the models and migrations for User and Order.

Run the following commands to create the models and migrations:

php artisan make:model User -m
php artisan make:model Order -m

This will create the User and Order models and their respective migration files.

Next, update the migration files.

Create Users Table Migration

Open the create_users_table migration file and update it like this:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->timestamps();
    });
}

Create Orders Table Migration

Open the create_orders_table migration file and update it like this:

public function up()
{
    Schema::create('orders', function (Blueprint $table) {
        $table->id();
        $table->foreignId('user_id')->constrained()->onDelete('cascade');
        $table->decimal('total', 8, 2);
        $table->timestamps();
    });
}

Run the migrations to create the tables:

php artisan migrate

Step 2: Define Relationships

Now, let’s define the relationships in our models.

User Model

Open the User model and add the orders relationship:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    public function orders()
    {
        return $this->hasMany(Order::class);
    }
}

Order Model

Open the Order model and define the user relationship:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $fillable = ['user_id', 'total'];

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

Step 3: Seed the Database

To see our ordering in action, we need some dummy data. Let’s create a seeder.

Run the following command to create a seeder:

php artisan make:seeder DatabaseSeeder

Update the DatabaseSeeder class to include some users and orders:

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use App\Models\User;
use App\Models\Order;

class DatabaseSeeder extends Seeder
{
    public function run()
    {
        User::factory()
            ->count(5)
            ->has(Order::factory()->count(3), 'orders')
            ->create();
    }
}

Also, create factories for User and Order models:

UserFactory

namespace Database\Factories;

use App\Models\User;
use Illuminate\Database\Eloquent\Factories\Factory;

class UserFactory extends Factory
{
    protected $model = User::class;

    public function definition()
    {
        return [
            'name' => $this->faker->name,
        ];
    }
}

OrderFactory

namespace Database\Factories;

use App\Models\Order;
use Illuminate\Database\Eloquent\Factories\Factory;

class OrderFactory extends Factory
{
    protected $model = Order::class;

    public function definition()
    {
        return [
            'total' => $this->faker->randomFloat(2, 10, 100),
        ];
    }
}

Run the seeder to populate the database with dummy data:

php artisan db:seed

Step 4: Query and Order by Relationship Sum

Now, let’s query the users and order them by the sum of their orders’ total.

We will use Eloquent to perform this query.

Example Query

Open your controller or create a new route to test the query.

use App\Models\User;
use Illuminate\Support\Facades\DB;

Route::get('/users', function () {
    $users = User::withSum('orders as total_orders', 'total')
        ->orderBy('total_orders', 'desc')
        ->get();

    return $users;
});

Explanation:

1. withSum('orders as total_orders', 'total'): This adds a total_orders attribute to each User model that contains the sum of the total column in the related orders table.
2. orderBy('total_orders', 'desc'): This orders the users by the total_orders attribute in descending order.

Step 5: Displaying Results

To display the results, you can update your view or simply return the data to see it in JSON format.

For example, if you are using Blade templates, you might do something like this:

In your Controller:

use App\Models\User;

public function index()
{
    $users = User::withSum('orders as total_orders', 'total')
        ->orderBy('total_orders', 'desc')
        ->get();

    return view('users.index', compact('users'));
}

In your Blade view (resources/views/users/index.blade.php):

<!DOCTYPE html>
<html>
<head>
    <title>Users</title>
</head>
<body>
    <h1>Users Ordered by Total Orders</h1>
    <ul>
        @foreach($users as $user)
            <li>{{ $user->name }} - Total Orders: ${{ $user->total_orders }}</li>
        @endforeach
    </ul>
</body>
</html>

Conclusion

In this article, we learned how to order Laravel models by the sum of a related model’s column. By following these steps, you can easily extend this example to fit your specific needs. The key concept here is using the withSum method to aggregate related data and then ordering the results based on this aggregated value.

• Check our tools small Tools
• Check our tools website Word count