AwadGorg
1 week ago

Select and groupBy are not working

Posted 1 week ago by AwadGorg

I don't know how to explain this so just let me show you I have this code

$top_games = Browsergames::join('browsergames_rating', 'browsergames.id', 'browsergames_rating.game_id')->select('browsergames.*')->orderBy('rating', 'DESC')->take(4)->get();

first is getting the data from the browser games table and join the browsergames_rating table to it the browsergames_rating table has many rows with the same game_id many users can rate the same game so now the code above is showing let's see if game one have 5 rating rows in the rating table and it will show the game one 5 times and i don't want that so i used the gropBy to group data using the browsergames table insted of the browsergames_rating but it returned an error this is the code

$top_games = Browsergames::join('browsergames_rating', 'browsergames.id', 'browsergames_rating.game_id')->select('browsergames.*')->orderBy('rating', 'DESC')->groupBy('browsergames.id')->take(4)->get();

and this is the error

SQLSTATE[42000]: Syntax error or access violation: 1055 'ovongames.browsergames.g_name' isn't in GROUP BY (SQL: select `browsergames`.* from `browsergames` inner join `browsergames_rating` on `browsergames`.`id` = `browsergames_rating`.`game_id` group by `browsergames`.`id` order by `rating` desc limit 4)

and this is the code am using in my pure php website

// get the games from the browsergames table
                $getGames  = "SELECT browsergames.game_name,browsergames.id,browsergames.game_tag,browsergames.game_link,browsergames.game_img_path,browsergames.mainImg,
                browsergames.reviewGameName,rating.game_id,rating.rating,
                AVG(rating.rating) AS avgRating FROM browsergames LEFT join rating ON
                browsergames.id = rating.game_id GROUP BY browsergames.id ORDER BY avgRating DESC LIMIT 4";

is there a way to do this with laravel without returning an error and thanks a lot.

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