Order By AVG from the forign table using laravel

Community support
#1

Hello, I have this table browser games and has many rows in it games and I also have another table browsergames_rating that has the ratings for the browser games I want to select data from the browsergames table based on the AVG rating from the browsergames_rating.
this is the code am using right now

$topGames = DB::table('browsergames')
                 ->leftjoin('browsergames_rating', 'browsergames.id', 'browsergames_rating.g_id')
                 ->orderByDesc('AVG(browsergames_rating.rating)')
                 ->get();
        return $topGames;

and it returns an error for me this is the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'AVG(browsergames_rating.rating)' in 'order clause' (SQL: select * frombrowsergamesleft joinbrowsergames_ratingonbrowsergames.id=browsergames_rating.g_idorder byAVG(browsergames_rating.rating)desc)

can you please help me and thanks.

#2

Hello! Try to use raw queries instead:

$topGames = DB::table('browsergames')
                 ->leftjoin('browsergames_rating', 'browsergames.id', 'browsergames_rating.g_id')
                 ->raw('ORDER BY AVG(browsergames_rating.rating) DESC')
                 ->get();

Note that ‘ORDER BY’ command requires a column based on which results will be ordered, so I’m not sure what you’re aiming for :thinking: You can’t order by number…

You probably mean ORDER BY browsergames_rating.rating DESC instead?

#3

this is what i want

$games = DB::table('browsergames')->select(['browsergames.*','browsergames_media.*','browsergames_rating.*',DB::raw('AVG(browsergames_rating.rating) as av')])->leftjoin('browsergames_media', 'browsergames.id', 'browsergames_media.g_id')->leftjoin('browsergames_rating', 'browsergames.id', 'browsergames_rating.g_id')->groupBy('browsergames.id')->orderByDesc('av')->take(22)->get();

got some help from laracast now my issue is fixe thanks

#4

Glad it is solved :slight_smile:

#5