CODEWITHSUNDEEP
laravel
AE1995
AE1995

Reputation: 372

Laravel sum quantity and group by product_id and return table data

Table Data:

id || product_id || quantity || sold_price || image (Relationship)
1  ||      1     ||    10    ||     10     || 1.png
2  ||      2     ||    5     ||     20     || 2.png
3  ||      1     ||    15    ||     15     || 1.png

Expected return data

Sum of repeated product_id which is 25 and average sold price of sold_price which is 10+15/2 = 2

To get average we get sum/counter as you already know.

EXPECTED RESULT

id || product_id || quantity || sold_price || image (Relationship)
1  ||      1     ||    25    ||     12.5   || 1.png
2  ||      2     ||    5     ||     20     || 2.png

What I have tried

$products = SoldProducts::whereIn('export_invoice_id', $export_invoice_ids)
            ->groupBy(DB::raw('product_id'))
            ->select([
                'sold_price',
                DB::raw('sum(quantity) total_quantity'),
            ])
            -> get();

But this return only total_quantity and product_id without id, average_sold_price, image.

Problem

Problem is that image is from a relationship.

I have tried to use with('image') but select doesn't work.

Upvotes: 1

Views: 1985

Answers (1)

Simon R
Simon R

Reputation: 3772

If I've understood the question properly the following code should rectify your issue.

$products = SoldProducts::whereIn('export_invoice_id', $export_invoice_ids)
    ->groupBy('product_id')
    ->join('images', 'product.image_id', '=', 'images.id')
    ->select([
        'products.id',
        'products.product_id',
        DB::raw('sum(products.quantity) AS quantity'),
        DB::raw('(sum(products.sold_price) / count(*)) AS sold_price'),
        'images.image'
    ])
    ->get();

If you use select, you need to select every column you require in the select for Eloquent to retrieve it, otherwise, it won't pull the column for you.

You need two DB::raw in your select - one for quantity and one for the sold price as per your specification; Quantity is a simple sum. It sums up all the items per product id. The sold price is summed to start with then divided by the number of that product_id.

Edit: I've updated the code example - but since you haven't specified how the image is joined to the table I've taken an educated guess. You may need to make it ->leftJoin if you don't always have an image assigned.

Upvotes: 2

Related Questions