Laravel Aggregates query not working according to group by

0
votes

On POS when I place any order it stored the order detail according to the product id on order_details table. I want to sum the quantity order according to product id. Order Detail table : id, order_id, product_id, total, created_at, updated_at

Product Controller :

public function index()
{

 $orderdetail = DB::table('order_details')
             ->select('quantity', DB::raw('sum(quantity) as sum'))
             ->groupBy('product_id')
             ->havingRaw('SUM(quantity)')
             ->get();

    $products = Product::latest()->with('category', 'supplier', 'orderdetail')->get();
    return view('admin.product.index', compact('products', 'orderdetail'));

}

On Product Model:

class Product extends Model
{

    protected $dates = [
        'buying_date', 'expire_date',
    ];

    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function supplier()
    {
        return $this->belongsTo(Supplier::class);
    }

    public function orderdetail()
    {
        return $this->belongsTo('App\OrderDetail', 'id', 'product_id');
    }
 }

}

But it does not show anything on Blade.

on Blade :

@foreach($products as $key => $product)
    <tr>
        <td>{{ $key + 1 }}</td>
        <td>{{ $product->name }}</td>
        <td>
            <img class="img-rounded" style="height:35px; width: 35px;" src="{{ URL::asset("storage/product/".$product->image) }}" alt="{{ $product->name }}">
        </td>
        <td>{{ $product->category->name }}</td>
        <td>{{ $product->supplier->name }}</td>
        <td>{{ $product->code }}</td>
        <td>{{ $product->buying_date->toFormattedDateString() }}</td>
        <td>{{ number_format($product->buying_price, 2) }}</td>
        <td>{{ number_format($product->selling_price, 2) }}</td>
        <td>{{ $product->product_unit }}</td>
        <td>{{ $product->product_unit - $product->sum }}</td>
        <td>{{ $product->sum }}</td>
        <td>DLT</td>
    </tr>
@endforeach

This is the result of dd on $products and $orderdetail

It aggregates the value not showing the blade template. how I show it on the blade or there any problem on model or blade? please help.

2
laravel
try adding dd($orderdetail, $products); before the return statement in your controller to display the data in those variables – gbalduzzi
It shows that it's aggregate the value but it does not show on the blade. On Blade I Used this one {{ $product->sum }} on the loop. But not show anything. – Jubayer Alam
So the problem is in the blade file, not in the controller. Please include in your question a more complete snippet of your loop inside blade – gbalduzzi
sorry, now I attached the blade loop on the post. Please kindly see it – Jubayer Alam

2 Answers

1
votes

Here I solved via this way on Controller

public function index()
{
    $orderdetail = DB::table('order_details')
            ->select('quantity', DB::raw('sum(quantity) as soldunit'))
            ->groupBy('product_id')
            ->get();

    $products = Product::latest()->with('category', 'supplier', 'orderdetail')->get();
    return view('admin.product.index', compact('products', 'orderdetail'));

}

and on Model, I have slightly Changed

public function orderdetail()
{
    return $this->hasMany('App\OrderDetail','product_id', 'id')->selectRaw('order_details.*,sum(quantity) as soldunit')->groupBy('product_id');
}

after this, to access soldunit on the blade I used this one

{{ $product->orderdetail->sum('soldunit') }}
0
votes

If you want to access sum of a column via a defined relationship, you need to add it during your main query:

$products = Product::with('category', 'supplier')->with(['orderdetail' => function($query){
   $query->select('id', 'product_id', 'quantity', DB::raw('sum(quantity) as sum'));
}])->get();