How to use withSum() with a condition where() in Laravel Eloquent Relationships?

In this blog, we are going to use withSum() function in Laravel Query to get the sum of the relational columns. These methods will place a {relation}_{function}_{column} attribute on your resulting models.


We will be having two tables Category and Products with their models and how we connect them in relation and get the desired output.


Here is the Code,


Category Model:


<?php

  

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;

use Illuminate\Database\Eloquent\Model;

  

class Category extends Model

{

    use HasFactory;

  

    /**

     * Get the comments for the blog post.

     */

    public function products()

    {

        return $this->hasMany(Product::class);

    }

}



Product Model:


<?php

  

namespace App\Models; 

use Illuminate\Database\Eloquent\Factories\HasFactory;

use Illuminate\Database\Eloquent\Model;

  

class Product extends Model

{

    use HasFactory;

  

    protected $fillable = [

        'name', 'price', 'is_active'

    ];

}



Using withSum() :


<?php
  
namespace App\Http\Controllers;
use App\Models\Category;
  
class SignaturePadController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $categories = Category::select("id", "name")
                        ->withSum('products', 'price')
                        ->get()
                        ->toArray();
 
        dd($categories);
    }
}


Output:


Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Mobile
            [products_sum_price] => 30
        )
    [1] => Array
        (
            [id] => 2
            [name] => Laptop
            [products_sum_price] => 20
        )
)


Using withSum() with Where Condition


<?php

namespace App\Http\Controllers;
use App\Models\Category;
  
class SignaturePadController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $categories = Category::select("id", "name")
                        ->withSum([
                            'products' => function ($query) { 
                                $query->where('is_active', '1');
                            }], 'price')
                        ->get()
                        ->toArray();
 
        dd($categories);
    }
}


Output:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Mobile
            [products_sum_price] => 20
        )
    [1] => Array
        (
            [id] => 2
            [name] => Laptop
            [products_sum_price] => 20
        )
)


I hope this will help you in understanding the code. Let me know in the comments if you have any doubts.