How to Calculate the Sum of a Relation Column in Eloquent

In this blog post, we’ll explore a practical scenario, calculating the sum of sales for a product using Laravel Eloquent’s built-in methods.

The Eloquent syntax to achieve this involves using withSum and looks like:

// Calculate the sum of the 'quantity' column in the related 'sales' table
$products = Product::withSum('sales', 'quantity')->get();

// Each product in the collection will automatically have a `sales_sum_quantity`:
foreach ($products as $product) {
    dump("$product->name has Total Sales of: $product->sales_sum_quantity");
}

In this post we’ll build a full example for this by creating migrations and models for products and sales tables, adding some test data and finally calculating total sales per product using Eloquent’s withSum method.

Let’s get started!

Step 1: Create a Laravel Project

Begin by creating a new Laravel project using the following commands in your terminal:

composer create-project laravel/laravel sum-relation-example
cd sum-relation-example

Step 2: Create Migrations

Let’s consider a scenario where we have a Product model and a Sale model. Each product can have multiple sales, and we want to calculate the total sales amount for each product.

Generate migrations for both entities:

php artisan make:migration create_products_table --create=products
php artisan make:migration create_sales_table --create=sales

Step 3: Add Migration Code

Open the generated migration files and define the schema by adding the code below.

For the products table use:

database/migrations/2024_01_28_171154_create_products_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('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->decimal('price', 8, 2);
            $table->timestamps();
        });
    }

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

And for the sales table use:

database/migrations/2024_01_28_171154_create_sales_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('sales', function (Blueprint $table) {
            $table->id();
            $table->foreignId('product_id')->constrained();
            $table->integer('quantity');
            $table->timestamps();
        });
    }

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

Now run the migrations to create the 2 tables:

php artisan migrate

Step 4: Create Models

Generate models for both Product and Sale by running the following commands:

php artisan make:model Product
php artisan make:model Sale

Step 5: Add Model Code

Open the generated model files and define the relationships:

For Product use:

app/Models/Product.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $fillable = [
        'name',
        'price'
    ];

    public function sales()
    {
        return $this->hasMany(Sale::class);
    }
}

For Sale use:

app/Models/Sale.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Sale extends Model
{
    protected $fillable = [
        'quantity'
    ];

    public function product()
    {
        return $this->belongsTo(Product::class);
    }
}

Step 6: Generate Some Test Data

To test the calculation of the sum of sales amount in Step 6 without having data, you can manually insert sample data into your database for the Product and Sales models. Here’s a simplified example using Laravel’s Tinker:

  1. Open your terminal and run:
php artisan tinker
  1. Create a few products and associated sales, by copy pasting in tinker:
use App\Models\Product;
use App\Models\Sale;

$product1 = Product::create(['name' => 'Product A', 'price' => '19.95']);
$product2 = Product::create(['name' => 'Product B', 'price' => '49.95']);

$product1->sales()->create(['quantity' => 10]);
$product1->sales()->create(['quantity' => 15]);

$product2->sales()->create(['quantity' => 8]);
$product2->sales()->create(['quantity' => 12]);

Now our table is filled with data, it should look like this:

Screenshot of Our products and sales Tables After Inserting Some Testdata

Step 7: Add Calculating of Sum to Routes

Now, integrate the calculation of the total sales amount for each product by adding the following code to your routes/web.php file:

routes/web.php
<?php

use App\Models\Product;
use Illuminate\Support\Facades\Route;

Route::get('/test', function () {
    // Calculate the sum of the 'quantity' column in the related 'sales' table
    $products = Product::withSum('sales', 'quantity')->get();

    // Each product in the collection will automatically have a `sales_sum_quantity`:
    foreach ($products as $product) {
        dump("Product: $product->name, Total Sales Quantity: $product->sales_sum_quantity");
    }
});

Step 8: Test the application

Start the application by running:

php artisan serve

Then you can open your browser and enter the following url: http://127.0.0.1:8000/test

You should now see the following output in the browser:

This shows that the sum has been calculated correctly for both products: Product A was sold 25 times and Product B was sold 20 times, which is expected given the inserted data.

Conclusion

That’s it! You’ve successfully created Models, Migrations and Data for Products and Sales and calculated their totals using a sum on the quantity column in the sales table.

Calculating the sum of a relation column in Eloquent is quite straightforward using the built-in Eloquent method: withSum. With the proper setup, you can easily join aggregated data of your related records and use a sum on them.

Now, take this newfound knowledge and apply it to your own application. Happy coding!

References

This entry is part 3 of 4 in the series Aggregating With Laravel Eloquent

  1. How to Calculate the Average of a Column Using Eloquent
  2. How to Calculate the Sum of Multiple Columns Using Eloquent
  3. How to Calculate the Sum of a Relation Column in Eloquent
  4. How to Get the Max Value of a Column Using Eloquent

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