Aggregate Functions on Related Models Laravel
I'm a self-taught web developer who likes sharing my knowledge, helping people.
Sometimes you may need to execute some queries a little complex like counting the number of sales of a book or obtaining the minimum price for a product of a determinate category. To achieve this, we can use aggregate functions like withSum(), withMin(), withAverage()
In this example, I'm going to use this schema
- withSum()
Sum products by category
<?php
namespace App\Http\Controllers;
use App\Models\Category;
class CategoryController extends Controller
{
public function index()
{
$categories = Category::select("id", "name")
->withSum('products as sum_products', 'price')
->take(3)
->get()
->toArray();
dd($categories);
}
}
Result
Array
(
[0] => Array
(
[id] => 1
[name] => Home & Kitchen
[sum_products] => 250
)
[1] => Array
(
[id] => 2
[name] => Electronics
[sum_products] => 1500
)
[2] => Array
(
[id] => 2
[name] => Clothes
[sum_products] => 2000
)
)
-withMin()
class CategoryController extends Controller
{
public function index()
{
$categories = Category::select("id", "name")
->withMin('products as min_products', 'price')
->take(3)
->get()
->toArray();
dd($categories);
}
}
Result
Array
(
[0] => Array
(
[id] => 1
[name] => Home & Kitchen
[min_products] => 50
)
[1] => Array
(
[id] => 2
[name] => Electronics
[min_products] => 100
)
[2] => Array
(
[id] => 2
[name] => Clothes
[min_products] => 120
)
)
Note: withMax() and withAvg() works the same way as withMin() but the first one calculating the max value and the second one the average, to try only you need to change your query for the function you want.
- withCount()
You can use withCount() eloquent in laravel as follow
public function index()
{
$categories = Category::select("id", "name")
->withCount('products')
->take(3)
->get()
->toArray();
dd($categories);
}
Result
Array
(
[0] => Array
(
[id] => 1
[name] => Home & Kitchen
[products_count] => 2
)
[1] => Array
(
[id] => 2
[name] => Electronics
[products_count] => 6
)
[2] => Array
(
[id] => 2
[name] => Clothes
[products_count] => 7
)
)
Note: withCount() you can calculate “children” entries
Thanks for reading.
![How To Create Admin Panel using Filament [TALL STACK]](/_next/image?url=https%3A%2F%2Fcdn.hashnode.com%2Fres%2Fhashnode%2Fimage%2Fupload%2Fv1639173022637%2F4BT9qk5g3.png&w=3840&q=75)
