AwadGorg
1 week ago

DB use select to get avg and then using the orderBy to order the items by the avg from the select

Posted 1 week ago by AwadGorg

Hello, I have this code

$top_games = DB::table('browsergames')->leftjoin('browsergames_rating', 'browsergames.id', 'browsergames_rating.game_id')->leftjoin('browsergames_media', 'browsergames.id', 'browsergames_media.g_id')->select('browsergames.*', 'AVG(browsergames_rating.rating) AS avgRating', 'browsergames_media.*')->orderBy('avgRating', 'desc')->groupBy('browsergames.id')->take(4)->get();

i want to get the average of the user rating and asign a nickname on it and then using the order by i want to order games based on the avgRating of the game desc right now am using the code above but it returns an error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'AVG(browsergames_rating.rating)' in 'field list' (SQL: select `browsergames`.*, `AVG(browsergames_rating`.`rating)` as `avgRating`, `browsergames_media`.* from `browsergames` left join `browsergames_rating` on `browsergames`.`id` = `browsergames_rating`.`game_id` left join `browsergames_media` on `browsergames`.`id` = `browsergames_media`.`g_id` group by `browsergames`.`id` limit 4)

Please sign in or create an account to participate in this conversation.