Display best selling items by status-CodePudding

Explaining my problem, I have two tables in my database called order and order_details.

On my dashboard, I display the three best-selling items (currently work!). However, I would like to display only the best selling items THAT have the status = delivered.

Today, it works like this:

$top_sell_items = OrderDetails::with(['product'])
             ->select('product_id', DB::raw('SUM(quantity) as count'))
             ->groupBy('product_id')
             ->orderBy("count", 'desc')
             ->take(3)
             ->get();

The problem is that the order status is stored in another table, called orders, column order_status.

How can I create this rule and include it in my $top_sell_items?

CodePudding user response：

if you relationship is done between this table already, you can use this code, if not you have to go to the OrderDetails Model and add new method orders

$top_sell_items = OrderDetails::with(['product', 'orders'])
             ->whereHas('orders', function($query) {
                $query->where('status', 'delivered');
              })
             ->select('product_id', DB::raw('SUM(quantity) as count'))
             ->groupBy('product_id')
             ->orderBy('count', 'desc')
             ->take(3)
             ->get();

CodePudding user response：

You could either define a relationship between Orders and OrderDetails or use a join like so...

<?php

$top_sell_items = OrderDetails::with(['product'])
    ->join('orders', 'orders.id', '=', 'order_details.order_id')
    ->select('product_id', DB::raw('SUM(quantity) as count'))
    ->where('orders.order_status', 'delivered');
    ->groupBy('product_id')
    ->orderBy("count", 'desc')
    ->take(3)
    ->get();

More info here: https://laravel.com/docs/8.x/queries#joins

CodePudding user response：

Depending if you desire this, the following solution might be the most efficient:

$products = Product
    ::whereHas('orderDetails.order', function ($query) {
        $query->where('orders.order_status', 'delivered');
    })
    ->withSum('orderDetails', 'quantity')
    ->orderBy('order_details_sum_quantity', 'desc')
    ->take(3)
    ->get();

It will directly return instances of Product. In addition it puts everything in a single query instead of the two that with produces.