Display best selling items by status

Question

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?

Answer 1

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

Answer 2

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.