Laravel 5.6 withCount and where statement

Question

I'm using laravel 5.6.

I have 3 tables : players, games and game_player table.

Retrieving the game count of every player is easy with this in the index action of the PlayersController:

//Get game count of every player
$players = Player::withCount('games')->get();

Is there a way to retrieve the game count for the games when the player has won the game? (in the index action of the playerscontroller) I'm not sure how to do this. Can someone help?

games table migration

$table->integer('winner')->unsigned()->index();
$table->foreign('winner')->references('id')->on('players')->onDelete('cascade');

game_player table migration

table->integer('game_id')->unsigned()->nullable();
$table->foreign('game_id')->references('id')->on('games')->onDelete('cascade');

$table->integer('player_id')->unsigned()->nullable();
$table->foreign('player_id')->references('id')->on('players')->onDelete('cascade');

game model relation

public function players(){
    return $this->belongsToMany('App\Player')->withTimestamps();
}

//this is for the winner of the game
public function player()
{
    return $this->hasOne('App\Player');
}

player model relation

public function games(){
    return $this->belongsToMany('App\Game')->withTimestamps();
}

//the game the player has won
public function game()
{
    return $this->belongsTo('App\Game');
}

playerscontroller

public function index()
{
    $players = Player::all();

    //Get game count of every player
    $players = Player::withCount('games')->get();

    /*Load the view and pass the groups*/
    return \View::make('players.index')->with('players', $players);
}

The result I want is to get played games (is working) and won games.

player > index blade

@foreach($players as $player)
   <p>{{ $player->id }}</p>
   <p>{{ $player->firstname }} {{ $player->lastname }}</p>
   <ul>
      <li>Played games: {{ $player->games_count }}</li>
      <li>Won games: </li>
   </ul>
@endforeach

update

I don't think we can see it as a duplicate of this question (Laravel using where clause on a withCount method) because I'm using a many to many relationship also.

If I use this code which isn't the correct one, because the 1 needs to be dynamic $id:

$players = Player::withCount('games')
     ->having('winner', '=', 1)
     ->get();

I get the error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'winner' in 'having clause' (SQL: select players., (select count() from games inner join game_player on games.id = game_player.game_id where players.id = game_player.player_id) as games_count from players having winner = 1)

update 2

When I use this code:

controller

$players = Player::all();

//Get game count of every player
$players = Player::withCount('games')->get();

$wongames = Player::withCount(['games' => function($query) { $query->where('winner', '=', 5); }])->get();

//$players = Player::withCount('games')->where('winner', '=', 1);

/*Load the view and pass the groups*/
return \View::make('players.index')->with('players', $players)->with('wongames', $wongames);

blade index

@foreach($players as $player)
   <p>{{ $player->id }}</p>
   <p>{{ $player->firstname }} {{ $player->lastname }}</p>
   <ul>
      <li>Played games: {{ $player->games_count }}</li>
         @foreach($wongames as $wongame)
            <li>Won games: {{ $wongame->games_count }}</li>
         @endforeach
   </ul>
@endforeach

I get this (not what I exactly want, but getting there I think):

Answer 1

Since you define a foreign key on the games table, you have a one-to-many relationship between the Player and Game already. Try adding the following relation to your Player model:

// Player.php
public function won()
{
    // must specify the foreign key because it is not the usual `_id` convention.
    return $this->hasMany(Game::class, 'winner');
}

Then access it on each player like:

@foreach($players as $player)
    {{ $player->won->count() }}
@endforeach

Rather than querying in the view file, you should ideally do the following in your controller:

public function index()
{
    /*Load the view and pass the groups*/
    return \View::make('players.index')->with('players', Player::with('won')->get());
}