CODEWITHSUNDEEP
phplaraveleloquent
Adam Lambert
Adam Lambert

Reputation: 1421

Laravel 5.6 / Eloquent Sum and GroupBy with related tables/relationships

I am trying to sum a column while grouping by data within the table as well as a related table.

Order Table

Order Items Table

Relationships

Order item belongs to Order

public function order() : BelongsTo
{
    return $this->belongsTo(Order::class);
}

Query

I am looking for a result of:

The following gets a collection with the correct groups:

$items = OrderItem::with(['order'])
    ->get()
    ->groupBy(['fulfilment_location_id', 'product_id']);

I can then walk through this array to get the result I am looking for as follows:

$result = [];
foreach($items as $location_id => $products_collection){
    foreach($products_collection as $product_id => $order_items_collection){
        $sum = $order_items_collection->sum(function(OrderItem $oi){
            return $oi->qty;
        });
        isset($result[$location_id][$product_id]) ?
            $result[$location_id][$product_id] += $sum :
            $result[$location_id][$product_id] = $sum;
    }
}

... however this seems very long winded. I could get the result far easier by just duplicating the location_id column from the orders table to the order_items table, but this does not feel very nice. I hoped there would be a simpler way that uses eloquent (opposed to writing a raw query).

Upvotes: 1

Views: 656

Answers (1)

Leo
Leo

Reputation: 7420

Something along the lines of this: 

$items = OrderItem::select(DB::raw('sum(order_items.qty) as sumqty, o.location_id as lcoation, order_items.product_id as product_id'))
            ->leftJoin('orders as o', 'o.id', 'order_items.order_id')
            ->groupBy(['product_id', 'lcoation']);

Should give you:

sumqty | location | product_id
---------------------------------
 12       3          12                   // example data

Upvotes: 1

Related Questions