Laravel eloquent sum relationship quantity and groupby

4
votes

products Table:

id        name
1     first product

product_credits table:

id   product_id   quantity
1         1          10
2         1          20

What I am trying to do:

I am trying to get all the products quantities.

Expected data:

Get the:

  • product name = first product
  • product quantity = 30

Model Relationship code:

public function credits () {
    return $this -> hasMany('App\Models\Product\ProductCredits')
        -> whereHas('importInvoice', function ($query) {
            $query->where('deleted_at', null);
        }) -> orderBy('created_at', 'ASC') -> orderBy('quantity', 'DESC');
}

The relationship works good.

What I have tried:

I have tried to use with function in the eloquent.

public function exportProductsCredit(Request $request) {
    // GET THE COLLECTION
    $products = Product::with(['credits' => function ($query) {
        $query -> where('quantity', '>', 1) -> groupBy('product_id') -> sum('quantity');
    }]) -> get();

    return $products;
}

But it throw an error: product_credits.id' isn't in GROUP BY

EDIT

Problem is that I have Mutator fields that I want to show for example I calculate the product_net_price which I return as a mutator.

2
laraveleloquent

2 Answers

4
votes

If you want it as an attribute, dont try to query build it like a relation.

you can declare the attribute like this in Product::class

public function getSumCreditsAttribute()
{
    return $this->credits->where('quantity', '>', 1)->sum('quantity');
}

you can also always have it loaded with the appends attribute

protected $appends = ['sum_credits'];

or access it when you have an instance of Product::class

$products = Product::get();
foreach ($products as $product) {
    var_dump($product->sum_credits);
}
0
votes

Since we can't achieve group by & aggregate out of the box using model. You need to use the following way

Product::join('product_credits as pc', 'products.id', '=', 'pc.product_id')
        ->select('products.id', 'products.name', DB::raw("SUM(pc.quantity) as 'product_quantity'"))
        ->with('credits')
        ->where('pc.quantity', '>', 1)
        ->groupBy('pc.product_id')
        ->get();

Hope this helps you.