How to sum only top 3 of every score based on level in laravel

user_8937116 0
Viewed 49

I have table like this

enter image description here

here my controller but it sum all score from score1 + all score 2 + all score 3

$data = DB::table('group_table')
    ->select('team','level',DB::raw('sum(score1 + score2 + score3) as total'))
    ->groupBy('team','level')
    ->get();

return response()->json($data);

i want only sum top 3 score from score 1, score 2, and score 3. as en example in Team STARS Level 1 from your DB total will be 646 because:

TOTAL = [(Top 3 Score 1) + (Top 3 Score 2) + (Top 3 In Score 3)] 
TOTAL = [(89 + 81 + 66) + (80 + 73 + 71) + (72 + 64 + 50)]

i dont know how to take only top 3 score on each score 1, score 2, and score 3. can someone help me

laravel-8 laravel rank sum php
1 Answers

This is a rather complex requirement. You can do it using collection functions if you are willing to load the entire table in PHP memory:

$teamLevelScores = DB::table('group_table')->get()
   ->groupBy([ 'team', 'level' ])
   ->map(function ($teams, $teamId) {
        $sum1 = $teams->sortByDesc('score1')->take(3)->sum('score1');
        $sum2 = $teams->sortByDesc('score2')->take(3)->sum('score2');
        $sum3 = $teams->sortByDesc('score3')->take(3)->sum('score3');
        return $sum1+$sum2+$sum3;
   });

This should result in a two dimensional collection like e.g.

$teamLevelScores = [ 
   'STARS' => [
      1 => 646,
      // ...
   ]
   // ...
];

There might be ways to do it within the query but they are probably not simple or DBMS specific.

user_4878130
Related