Laravel query not working properly

I have an error in this query please help me resolve this error

$topMembers = DB::table('members')->join('games', function ($join) {
            $join->on('members.id', '=', 'games.player1_id')
                ->orWhere('members.id', '=', 'games.player2_id');
        })
            ->select('members.id', 'members.name', DB::raw('SUM(CASE WHEN games.player1_id = members.id THEN player1_score ELSE player2_score END) as total_score'), DB::raw('count(*) as games_played'))
            ->groupBy('members.id')
            ->havingRaw('COUNT(*) >= 10');

        $topMembers =
            $topMembers
            ->orderByRaw($topMembers->total_score / $topMembers->games_played, 'DESC')
            ->limit(10)
            ->get();

LaryAI

3 years ago

Level 58

The issue with the query is that the total_score and games_played columns are not available in the query result. To fix this, you can use subqueries to calculate these values and then use them in the main query. Here's an updated version of the query:

$topMembers = DB::table('members')
    ->join(DB::raw('(SELECT player1_id as id, SUM(player1_score) as total_score, COUNT(*) as games_played FROM games GROUP BY player1_id
                    UNION ALL
                    SELECT player2_id as id, SUM(player2_score) as total_score, COUNT(*) as games_played FROM games GROUP BY player2_id) as games'), function ($join) {
        $join->on('members.id', '=', 'games.id');
    })
    ->select('members.id', 'members.name', 'games.total_score', 'games.games_played')
    ->groupBy('members.id')
    ->havingRaw('COUNT(*) >= 10')
    ->orderByRaw('games.total_score / games.games_played DESC')
    ->limit(10)
    ->get();

In this version, we're using subqueries to calculate the total_score and games_played values for each member. We're then joining this subquery with the members table and using the calculated values in the main query. We're also using the orderByRaw method to sort the results by the calculated value.

Please or to participate in this conversation.