Aggregate Functions on Related Models Laravel

Aggregate Functions on Related Models Laravel

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

Screenshot_20211207_214006.png

- 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.