How would you aggregate a column in one model grouped by a column in another model in Laravel?

Question

I know this is simple, but I just cant get past this problem in an Eloquent way. For a simple scenario where a product master and transaction table are maintained thus:

**Table : Products**

prod_id
Prod Name
Manufacturer

**Table : Sales**

prod_id
qty_sold
selling_price

A relationship is already established on "product" model

public function sales()

{
return $this->hasMany('\App\Models\Sale','prod_id','prod_id');
}

How do I run an eloquent query on Product to return the sales quantity (sum('qty')) grouped by manufacturer?

Secondly, is it possible to run a query on Sale model to get aggregates grouped by Manufacturer (from Product Model)?

Basically, I want the aggregates from one model to group by a column in another related model.

Here's a possible SQL query (excuse typos, I'm on a mobile device) :

SELECT products.manufacturer, sum(qty_sold) 
FROM products LEFT JOIN sales on products.prod_id = sales.prod_id 
GROUP BY products.manufacturer

Answer 1

I would do it with something like this using a DB::raw() select:

DB::table('products')
->leftJoin('sales', 'products.prod_id', '=', 'sales.prod_id')
->select(DB::raw('products.manufacturer, SUM(qty_sold)'))
->groupBy('products.manufacturer')
->get()

You could also try something like:

$sales = Product::select(DB::raw('products.manufacturer, SUM(qty_sold)'))
                  ->groupBy('products.manufacturer')
                  ->get();