Laravel: Count rows and group by id with where clause

Question

I am currently creating my very first project with the php framework Laravel. I use the latest version that is 8.9.0 and I have reached a problem I can't solve on my own. I need to create a top scorer list for each tournament that is played. My DB tables like this:

Players
id | player name 
1  | Player A
2  | Player B
3  | Player C

Tournaments
id | tournament
1  | Tournament A
2  | Tournament B
3  | Tournament C

TournamentStats
id  | game   | player_id  |  goal  | tournament_id
1   |  1     |  1         |  1     |  1
2   |  1     |  1         |  1     |  1
3   |  1     |  2         |  1     |  1
4   |  2     |  3         |  1     |  1
5   |  2     |  2         |  1     |  1
6   |  2     |  2         |  1     |  1
7   |  1     |  2         |  1     |  2
8   |  1     |  2         |  1     |  2
9   |  1     |  3         |  1     |  2
10  |  2     |  2         |  1     |  2
11  |  2     |  2         |  1     |  2
12  |  2     |  3         |  1     |  2

My goal is this when the page of tournament A is open it should show:

Player B 3
Player A 1
Player C 1

and if tournament B then

Player B 4
Player C 2
Player A 0

I have tried like this:

Controller 

$goals = Player::all()->sortByDesc('goals');
 return view('tournament.goals')->with('goals', $goals);

Model 
  public function getGoalsAttribute()
{
    return TournamentStats::where(function($query) {
        $query->where('player_id', $this->attributes['id'])
    })->count();
}

This works but it shows all players that are in my players table. I need to show only those that have scored in particular tournament. I know that I need to set a where clause but I don't know where. I have tried to add it like this in my model:

 public function getGoalsAttribute($tournament)
 {
  return TournamentStats::where(function($query) {
  $query->where('player_id', $this->attributes['id'])
        ->where('tournament_id', $tournament);
  })->count();
}

and in my controller like this:

 $goals = Player::all()->where('tournament_id', $id)->sortByDesc('goals');

Make use of many to many relation. This will be easier for you — Deepesh Thapa, Oct 12 '20 at 07:07
it is quite vast and long to give you asnwers. But i suggest you make use of many to many relation where you can make TournamentStats as a pivot table. — Deepesh Thapa, Oct 12 '20 at 07:08
*when the page of tournament A is open it should show* I see 6 rows for it, but sum of values for players gives 5... typo? — Akina, Oct 12 '20 at 07:31

score 0 · Accepted Answer · answered Oct 12 '20 at 07:57

Method 1:

Create relations for TournamentStat model for Player and Tournament models

public function player()
{
    return $this->belongsTo(\App\Player::class, 'player_id');
}

public function tournament()
{
    return $this->belongsTo(\App\Tournament::class, 'tournament_id');
}

Now query with groupBy

$dumpData = \App\TorunamentStats::with([
    'player',
    'tournament'
])
    ->where('tournament_id', 1)
    ->selectRaw('id,tournament_id,count(player_id),player_id')
    ->groupBy('player_id')
    ->get();

dd($dumpData->toArray());

Method 2: Using DB

$dumpData = \DB::table('tournament_stats')
    ->join('player', 'player.id', '=', 'tournament_stats.player_id')
    ->join('tournament', 'tournament.id', '=', 'tournament_stats.tournament_id')
    ->where('tournament_id', 1)
    ->selectRaw('tournament_id,count(player_id),player_id,tournament.name,player.name')
    ->groupBy('player_id')
    ->get();

dd($dumpData->toArray());

Note: you might get groupby error, I haven't understood the fix of this error properly, but it can be fixed by turning strict off on database.php for mysql. Laravel : Syntax error or access violation: 1055 Error

Laravel: Count rows and group by id with where clause

1 Answers1