Get SUM of a related column filtered by a field in the parent model in Laravel Eloquent

Question

I have the following tables:

purchase_orders

• id
• status

line_items

• id
• purchase_order_id
• product_id
• qty

products

• id
• product_name

A purchase order contains multiple line items and each line item has one product and a field for quantity ('qty').

See ERD Model

Here is how I defined the Laravel models:

Product Model:

class Product extends Model
{
    public function lineitems()
    {
        return $this->hasMany('App\LineItem');
    }
}

LineItem Model:

class LineItem extends Model
{
    public function purchase_order()
    {
        return $this->belongsTo('App\PurchaseOrder', 'purchase_order_id');
    }

    public function product()
    {
        return $this->hasOne('App\Product', 'product_id');
    }
}

PurchaseOrder Model:

class PurchaseOrder extends Model
{
    public function lineitems()
    {
        return $this->hasMany('App\LineItem');
    }

}

I would like to get the the SUM of all 'line_items.qty' for a 'product.id' filtered by the 'purchase_order.status'.

How do I go about writing the query in a Model function so that I can use it to loop through all products thru Blade @foreach?

Thank you in advance!

Answer 1

If you want to get more speed and get everything all at once, then you can try this query :

$products = Product::selectRaw('products.*, sum(line_items.qty) as qty_sum')
            ->join('line_items', 'line_items.product_id', '=', 'products.id')
            ->join('purchase_orders', 'purchase_orders.id', '=', 'line_items.purchase_order_id')
            ->where('purchase_orders.status', 'sold')
            ->groupBy('products.id')
            ->get();

But Your Database if MySql must accept whole grouping with one column that always matches with other columns.

But if you want to stick to the Laravel relations only then, take the following steps.

1- When your table name is plural, PK is id, and FK is table name singular with _id ex (table products(id), table line_items(product_id)), there is no need to specify Foreign key in the Relations.

2- Since it's a Many-to-Many case, you don't necessarily have to Define a Model for the Intermediate table line_items, you can have it for special and fast queries, but you can handle CRUD on it via Laravel Pivot system. You can learn more about Pivot https://laravel.com/docs/5.7/eloquent-relationships#many-to-many

Retrieving Intermediate Table Columns

As you have already learned, working with many-to-many relations requires the presence of an intermediate table. Eloquent provides some very helpful ways of interacting with this table. For example, let's assume our User object has many Role objects that it is related to. After accessing this relationship, we may access the intermediate table using the pivot attribute on the models:

So your Models could look like this :

Product Model:

class Product extends Model
{
    public function purchaseOrders()
    {
        return $this->belongsToMany('App\PurchaseOrder', 'line_items')->withPivot('qty')->withTimestamps();
    }

    public function soldPurchaseOrders()
    {
        return $this->belongsToMany('App\PurchaseOrder', 'line_items')
                    ->where('status', 'sold')
                    ->withPivot('qty')->withTimestamps();
    }
}

PurchaseOrder Model:

class PurchaseOrder extends Model
{
    public function products()
    {
        return $this->belongsToMany('App\Product', 'line_items')->withPivot('qty')->withTimestamps();
    }

}

Use ->withPivot('field1', 'field2', 'etc'), and ->withTimestamps() if your pivot table has created_at, updated_at columns. And access pivot value with prefix pivot_+attribute in query builder, and with pivot->+attribute in results object method, ex : $products->first()->pivot->qty.

If you still want to use LineItem model, then change Product relation to belongsTo.

LineItem Model:

class LineItem extends Model
{
    public function purchaseOrder()
    {
        return $this->belongsTo('App\PurchaseOrder');
    }

    public function product()
    {
        return $this->belongsTo('App\Product');
    }
}

3- And finally to retrieve Products with SUM of qty use the following codes :

Use eager loading to autoload Purchase Orders for all Products all at once in 1 request to avoid N-Products + 1 requests to Database. https://laravel.com/docs/5.7/eloquent-relationships#eager-loading

$products = Product::with('soldPurchaseOrders')->get();

foreach($products as $product) {
    $qtySum = $product->soldPurchaseOrders->sum('pivot.qty');
    echo '<br/> Product '.$product->id.' - '.$product->name.' - '.$qtySum;
}

So at the end, Laravel will convert this with INNER JOIN query like the 1st example above, but only with fancy design and Pivot table.

EDIT------

After OP specify orders status as sold, I've update the codes to use sold.

EDIT 2 -----

Replace $product->pivot->qty by $qtySum. Pivot quantity will be available after calling the soldPurchaseOrders relations like :

foreach($product->soldPurchaseOrders as $order) {
    echo '<br/>Order .'$order->id.' - '.$order->pivot->qty;
}