CODEWITHSUNDEEP
phplaravel
mykoman
mykoman

Reputation: 1905

Laravel Sort Model based on relationship values

I have a model called Business, a business can have several services they provide. I have another model called Payments. Payment holds a record of what service people pay for. A service can have many Payments. I intend to fetch top 10 and worst 10 businesses based on payments received. The code below works fine, but it is very inefficient. I have to loop through the whole data to retrieve the information I need. Any more efficient way of achieving this?

$businesses = Business::with(['services'])->get();
foreach($businesses as $business){
    $id = $business->id;
    $name = $business->display_name;
    $services = $business->services;
    $businessRevenue = 0;
    if(count($services)>0){
        foreach($services as $service){
            $serviceId = $service->id;
            $totalAmount = PaymentTransaction::whereHas('invoice', function($query) use ($serviceId){
                $query->where('product_code_id', $serviceId);
            })->where('amount_paid', ">", 0)->sum('amount_paid');
            $businessRevenue= $businessRevenue + $totalAmount;
        }
    }
    $businessArray = (object) array('id'=> $id, 'name'=> $name, 'revenue'=> $businessRevenue);
    
    array_push($transformedBusiness, $businessArray);
}
$topBusiness = $bottomBusiness = $transformedBusiness;
usort($bottomBusiness, function($a, $b) {return strcmp($a->revenue, $b->revenue);});
usort($topBusiness, function($a, $b) {return strcmp($b->revenue, $a->revenue);});
$topBusiness = array_slice($topBusiness, 0, 10);
$bottomBusiness = array_slice($bottomBusiness, 0, 10);
return view('report.department_performance', compact('topBusiness', 'bottomBusiness'));

Upvotes: 1

Views: 73

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

I guess you could use a join query to get top and lowest 10 businesses directly from database instead of looping all business records and manually calculate their revenue

For top 10 business you can use inner joins for rest of the related tables

$topBusinesses = DB::query()
      ->select('b.id', 'b.display_name',  DB::raw('sum(p.amount_paid) as revenue')
      ->from('business as b')
      ->join('service as s', 'b.id', '=', 's.business_id')
      ->join('invoice as i', 's.id', '=', 'i.product_code_id')
      ->join('payment_transaction as p', function ($join) {
                    $join->on('p.id', '=', 'i.payment_transaction')
                     ->where('p.amount_paid', '>', 0);
      })
      ->groupBy('b.id', 'b.display_name')
      ->orderByDesc('revenue')
      ->limit(10)
      ->get();

For lowest 10 business use left joins for invoice and payment_transaction so that if there are no records in these table for a business you will still get these business records

$lowestBusinesses = DB::query()
      ->select('b.id', 'b.display_name',  DB::raw('coalesce(sum(p.amount_paid),0) as revenue')
      ->from('business as b')
      ->join('service as s', 'b.id', '=', 's.business_id')
      ->leftJoin('invoice as i', 's.id', '=', 'i.product_code_id')
      ->leftJoin('payment_transaction as p', function ($join) {
                    $join->on('p.id', '=', 'i.payment_transaction')
                     ->where('p.amount_paid', '>', 0);
       })
      ->groupBy('b.id', 'b.display_name')
      ->orderBy('revenue')
      ->limit(10)
      ->get();

I have used MySQL coalesce function to show 0 value in case sum() returns null, If you are using any other database you can use an alternate function.

Upvotes: 1

Related Questions