Laravel Multiple Relationship for a sum column

Question

I have Three Models name Invoices, Invoiceitems and Products.

Each Invoices HasMany Relationship with Invoiceitems Model.

Each Invoiceitems HasOne Relationship with Products Model.

I need to sum of Invoiceitem product amount where the Product has category 4.

Table Structure

Invoice

id | date | total_amt

Invoiceitem

Product

id | product_name | category_id

Relationship

Invoice Model

public function invoiceitems()
{

  return $this->hasMany('App\Invoiceitems', 'invoiceid', 'id');

}

Invoiceitem Model

public function products()
{

  return $this->hasOne('App\Products', 'id', 'product_id');

}

Expected Report

Invoice No | Date | Veg Category Product Amt | NonVeg Category Product Amt | Total Amt

KL0001 | 15-05-2021 | 0.00 | 190.366 | 190.366

KL0002 | 16-05-2021 | 20.00 | 350.000 | 370.000

Currently we use following Helper Function to get Particular category Products Total Amount

function getInvdiscsumamt($inv_id, $prdtype)
{

    $totaldisamt = Invoiceitems::Where('invoice_id', $inv_id)->whereHas('products', function ($query) use ($prdtype) {

        $query->where('category_id', $prdtype);

    })->sum('total_amt');

    return $totalpdtamt;
}

How to display particular category products total amount using Elequoent method

Flame Flame · Accepted Answer · 2021-06-05T15:37:14

You can try one of the aggregate functions that already exist in Eloquent, see https://laravel.com/docs/8.x/eloquent-relationships#other-aggregate-functions, for instance withSum:

$invoiceItem = InvoiceItem::query()
    ->where('invoice_id', $invId)
    ->withSum(['products', function($query) use ($productType) {
        $query->where('category_id', $productType);
    }, 'frequency'])
    ->first();

Your property will then be available using the property {relation}_{function}_{column}, so in this case products_sum_frequency.

Note that the withSum does not share anything with the with (or whereHas) function, that means that if you use a subselection in your query like ->with(['products', function($query){...}]) that will be a separate selection from the withSum query.

I also recommend to use proper camelcasing in your functions and also your models. I'd also use singular names for all your model class names. So Invoiceitems would become InvoiceItem (models/InvoiceItem.php). This is the default way to define it in laravel as far as I'm aware.

Laravel Multiple Relationship for a sum column

1 Answers