Create query by eloquent

I Guess you have to use the QueryBuilder

    https://laravel.com/docs/5.2/queries#joins



$plays = DB::table('plays')
    ->select('plays.*' , 'games.tournament_id', DB::raw('SUM(score) as scores'))
    ->join('games', 'games.id', '=', 'plays.game_id')
    ->where('games.tournament_id', 5) 
    ->groupBy('user_id')
    ->get();

Thx for fast reply :) Your code gives me a query which I expected and that works in phpmyadmin, but in laravel, receives such an error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'fifacup.plays.id' isn't in GROUP BY (SQL: select plays.*, games.tournament_id, SUM(score) as scores from plays inner join games on games.id = plays.game_id where games.tournament_id = 5 group by user_id)

what about this

$plays = DB::table('plays')
 ->select('user_id', 'plays.*' , 'games.tournament_id', DB::raw('SUM(score) as scores'))
    ->join('games', 'games.id', '=', 'plays.game_id')
    ->where('games.tournament_id', 5) 
    ->groupBy('user_id')
    ->get();

Same thing :/

Hi, I am not sure that you can achieve this. You are trying to sum the score while still selecting all the details about the plays. The sum would return one record per group, but plays.* returns all the records, not aggregated. If you need the user's score in a certain tournament, could you select only the tournament_id, user_id and sum(score)?

$plays = DB::table('plays')
 ->select('user_id', 'games.tournament_id', DB::raw('SUM(score) as scores'))
    ->join('games', 'games.id', '=', 'plays.game_id')
    ->where('games.tournament_id', 5) 
    ->groupBy('user_id', 'games.tournament_id')
    ->get();