CODEWITHSUNDEEP
laraveleloquentsummax
Aleks Per
Aleks Per

Reputation: 1639

Laravel query - get the best selling item

I'm new to Laravel and I use Laravel 5.1.

I have this table in database: enter image description here

This is table for purchased itesm. I want to know what is the best way to calculate the best selling voucher - every purchase has voucher_id ...

Upvotes: 2

Views: 6813

Answers (4)

Pawan Verma
Pawan Verma

Reputation: 1269

If you want today's best selling item:-

Order::whereDate('date','<',date('Y-m-d'))->select('voucher_id', DB::raw('COUNT(*) as `count`'))->groupBy('voucher_id')->orderBy('count', 'DESC')->first();

If you want to get top 5 selling items:-

Order::select('voucher_id', DB::raw('COUNT(*) as `count`'))->groupBy('voucher_id')->orderBy('count', 'DESC')->limit(5)->get();

If you want top 5 selling items between two dates:-

Order::whereDate('date','<',date('Y-m-d',strtotime('2023-02-01')))->whereDate('date','>',date('Y-m-d',strtotime('2022-12-31')))->select('voucher_id', DB::raw('COUNT(*) as `count`'))->groupBy('voucher_id')->orderBy('count', 'DESC')->limit(5)->get();

Upvotes: 0

user11878157
user11878157

Reputation: 1

I will Try And Success this Code.You Can try to best.

<?php      

    $topsales = DB::table('purchase_order_details')
           ->leftJoin('products','products.id','=','purchase_order_details.product_id')
           ->select('products.id','products.name','purchase_order_details.product_id',
                DB::raw('SUM(purchase_order_details.quantity) as total'))
           ->groupBy('products.id','purchase_order_details.product_id','products.name')
           ->orderBy('total','desc')
           ->limit(6)
           ->get();

   ?>

Upvotes: -2

rchatburn
rchatburn

Reputation: 752

Could Probably do something like this if you want to use eloquent

   $Voucher = Sales::sortBy(function ($sale) {
     return $sale->voucher_id->count();
    }, SORT_REGULAR, true)->take(1)->get();

Upvotes: 0

user6612690
user6612690

Reputation:

The best way of doing it is using one single query for counting and also getting the id of the best seller in the same query:

PurchasedItem::select(DB::raw('COUNT(id) as cnt', 'voucher_id'))->groupBy('voucher_id')->orderBy('cnt', 'DESC')->first();

Upvotes: 5

Related Questions