Laravel 5.5: How to get top selling items of a given shop?

Question

My tables are like:

shops [id]

inventories [id, shop_id]

orders [id, shop_id]

order_item [order_id, inventory_id, quantity]

Models:

//Shop
class Shop extends Model
{
    public function inventories()
    {
        return $this->hasMany(Inventory::class);
    }

    public function orders()
    {
        return $this->hasMany(Order::class);
    }
}

//Inventory
class Inventory extends Model
{
    public function shop()
    {
        return $this->belongsTo(Shop::class);
    }

    public function orders()
    {
        return $this->belongsToMany(Order::class, 'order_items')
                    ->withPivot('quantity');
    }
}

//Order
class Order extends Model
{
    public function shop()
    {
        return $this->belongsTo(Shop::class);
    }

    public function inventories()
    {
        return $this->belongsToMany(Inventory::class, 'order_items')
                    ->withPivot('quantity');
    }
}

Now I want 5 top selling inventories of a given shop, What will be the best possible way to do that?

I'm on Laravel 5.5

Answer 1

Though this was my own question I found the solution on my own and I want to share the solution with the community. I wanted to solve it using Eloquent because I need the model on the view and didn't want to query the model again.

    Inventory::where('shop_id', \Auth::user()->shop_id)
                    ->select(
                        'inventories.*',
                        \DB::raw('SUM(order_items.quantity) as quantity')
                    )
                    ->join('order_items', 'inventories.id', 'order_items.inventory_id')
                    ->groupBy('inventory_id')
                    ->get();

I hope this'll help someone with similar issue. Thanks