Laravel - Nested select (Eloquent)

Athul Raj

I have a scores table that I have to group by the attempt_number and take the sum of scores I want to nest this query using Eloquent and SQL raw and take the Max score from the attempts and order it according to score. I need the final result as a leaderboard.

  $usersByScore =  Attempt::where('game_id',$id)
    ->select('user_id','attempt_no','game_id',DB::raw('SUM(score) as total_score'))
    ->groupBy('attempt_no')
    ->orderBy('total_score', 'DESC')
    ->get()

this gives me the leaderboard but it has all attempts from the user. I need just the max score attempt for each user ordered by the score in descending order.

Athul Raj

Got the solution - Implemented the from method to nest the query

$usersByScore =  Attempt::with('user')
        ->select(DB::raw('MAX(tscore) as total_score, user_id,attempt_no'))
        ->from(DB::raw('(SELECT user_id,attempt_no,SUM(score) as tscore FROM 
         attempts WHERE game_id = '.$id.' GROUP By attempt_no,user_id) AS T'))
        ->groupBy('user_id')
        ->get();

Collected from the Internet

Please contact [email protected] to delete if infringement.