CODEWITHSUNDEEP
laravel
Jubayer Alam
Jubayer Alam

Reputation: 29

Laravel Aggregates query not working according to group by

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.

Upvotes: 0

Views: 493

Answers (2)

Jubayer Alam
Jubayer Alam

Reputation: 29

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') }}

Upvotes: 1

iazaran
iazaran

Reputation: 216

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();

Upvotes: 0

Related Questions