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-exampleStep 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=salesStep 3: Add Migration Code
Open the generated migration files and define the schema by adding the code below.
For the products table use:
<?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:
<?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 migrateStep 4: Create Models
Generate models for both Product and Sale by running the following commands:
php artisan make:model Product
php artisan make:model SaleStep 5: Add Model Code
Open the generated model files and define the relationships:
For Product use:
<?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:
<?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:
- Open your terminal and run:
php artisan tinker- 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:
products and sales Tables After Inserting Some TestdataStep 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:
<?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 serveThen 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
- How to Calculate the Average of a Column Using Eloquent
- How to Calculate the Sum of Multiple Columns Using Eloquent
- How to Calculate the Sum of a Relation Column in Eloquent
- How to Get the Max Value of a Column Using Eloquent