Laravel 5.6 withCount和where语句 [英] Laravel 5.6 withCount and where statement

问题描述

我正在使用laravel 5.6.

I'm using laravel 5.6.

我有3个表:玩家，游戏和game_player表.

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

通过PlayersController的index动作，很容易检索每个玩家的游戏计数:

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

当玩家赢得游戏时，是否可以获取游戏的游戏计数? (在playerscontroller的索引动作中)我不确定如何执行此操作.有人可以帮忙吗?

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?

游戏表迁移

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

game_player表迁移

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

游戏模型关系

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

玩家模型关系

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

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

玩家控制器

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.

玩家>索引刀片

@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

更新

我认为我们不能将其视为此问题的重复副本(

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.

如果我使用的代码不是正确的代码，因为1需要动态$ id:

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

我得到了错误:

SQLSTATE [42S22]:找不到列:1054'具有子句'中的未知列'winner'(SQL:选择players.，(从games内部联接中选择count()) games上的game_player.id = game_player.game_id其中players.id = game_player. )

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)

更新2

当我使用此代码时:

控制器

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

刀片索引

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

推荐答案

由于您在游戏表上定义了外键，因此Player和Game之间已经存在一对多的关系.尝试将以下关系添加到您的Player模型:

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());
}

这篇关于Laravel 5.6 withCount和where语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！