2

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');

enter image description here

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');

enter image description here

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.

result

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):

enter image description here

Improve this question
7
  • 1
    Possible duplicate of Laravel using where clause on a withCount method Commented Apr 16, 2018 at 19:53
  • 1
    Try Player::withCount(['games' => function($query) { $query->where('winner', '=', 1); }]. The logic is the same of using with() with additional query constraints. Commented Apr 16, 2018 at 20:08
  • Player::withCount('games')->where('winner', '=', 1)->get(); Commented Apr 16, 2018 at 20:16
  • @EliasSoares I just get a blanc page now ; @MikeFoxtech I get the error : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'winner' in 'where 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 where winner = 1) Commented Apr 16, 2018 at 20:23
  • My example code is short and don't include the ->get() part. did you included it? Commented Apr 16, 2018 at 20:26

1 Answer 1

Reset to default
2

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()); }
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

This worked! Nice! Thanks @DigitalDrifter I just need to study this so I can also understand what you are doing. In the player model, you create a function where you get the games associated with your player_id. and in the view you use that function with a count?
Yeah exactly. Eloquent has naming conventions where it uses _id on the end of foreign keys by default. Since you named your foreign key winner instead of winner_id you need to tell the hasMany method what the foreign key name is. If it was winner_id you could just do return $this->hasMany(Game::class);
Okay :-) what is the "::class" for?
Do note however, that looping in the view is running queries how it's written. It's best to eager load the won games like I show in the controller method using with.
::class is the fully qualified name of the object, so App\Models\Player, for example.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.