Join with Relational Data in yii 2

Yii 2.0
1

Hi All,
I am using yii 2 for my rest api application but I am getting stuck in getting data from relational table using joins.
I have two table team and team_player with their corresponding Active record Team and TeamPlayer.
In Team relation is defined as
class Team extends ActiveRecord
{
public function getTeamPlayer(){
return $this->hasMany(TeamPlayer::className(), [‘team_id’=> ‘id’]);
}
}

and in TeamPlayer relation is defined as
class TeamPlayer extends ActiveRecord
{
public function getTeam(){
return $this->hasMany(Team::className(), [‘id’=>‘team_id’]);
}
}

In my rest API controller, I am using following query to get the data .
$player_team = Team::find()
->select([‘team.name’,‘team_player.status’])
->innerJoinWith(‘teamPlayer’, true)
->where([‘team_player.player_id’=> $id])
->all();
By using above statement , I am getting only team.name while not getting team_player.status.
but printing the sql query, running in phpmyadmin, I am getting both name and status.

Please help me to get team_player.status value.

2

Hi @vikaskumar925,

In Team relation is defined as

class Team extends ActiveRecord
{
    public function getTeamPlayer(){
        return $this->hasMany(TeamPlayer::className(), [‘team_id’=> ‘id’]);
    }
}

and in TeamPlayer relation is defined as

class TeamPlayer extends ActiveRecord
{
    public function getTeam(){
        return $this->hasMany(Team::className(), [‘id’=>‘team_id’]);
    }
}

TeamPlayer::getTeam() must be hasOne, I guess. Are you sure that a TeamPlayer has many Teams?

$player_team = Team::find()
    ->select([‘team.name’,‘team_player.status’])
    ->innerJoinWith(‘teamPlayer’, true)
    ->where([‘team_player.player_id’=> $id])
    ->all();

This is a confused query, IMO. Why don’t you try to find a player with his/her team?

$player = TeamPlayer::find()
    ->with(`team`)
    ->where(['id' => $id])
    ->one();
// echo $player->status;
// echo $player->team->name;

[EDIT] Sorry, the proposal above is not correct. I misunderstood the need.

3

Thanks @softark ,
But actually I have three tables and their respective three models.
first one is users table with Users models
class Users extends ActiveRecord {
public function getTeam(){
return $this->hasMany(Team::className(), [‘id’=>‘team_id’])
->viaTable(‘team_player’,[‘player_id’=>‘id’]);
}
public function getPlayerTeam(){
return $this->hasMany(TeamPlayer::className(),[‘player_id’ =>‘id’]);
}
}

So a TeamPlayer has many teams.

Can you guide me in this case?

4

With previous code:

$player_team = Team::find()
->select([‘team.name’,‘team_player.status’])
->innerJoinWith(‘teamPlayer’, true)
->where([‘team_player.player_id’=> $id])
->all();

Team objects will be filled with team.name and team_player.status, but this last field is not in Team model.

You have two way:

1 - Remove select:

$player_team = Team::find()
->innerJoinWith(‘teamPlayer’)
->where([‘team_player.player_id’=> $id])
->all();

Then, for example for first record:

$p = $player_team[0];
$teamName = $p->name;
$teamPlayerStatus = $p->teamPlayer->status;

2 - Using Query

https://www.yiiframework.com/doc/api/2.0/yii-db-query

1 Like
5

You are wrong. A TeamPlayer has only one Team.
Generally speaking, a model for a junction table will have 2 hasOne relations - one is for one end and the other for the other end. PalayerTeam must have a hasOne relation to Team, and a hasOne relation to Player(User).

1 Like
6

Thanks @fabriziocaldarelli,
I found the second solution better.

7

Hi @softark,
Thanks for your answer , but my database tables are organize in such a way that A user(i.e. player) may have multiple teamplayer records but active one at a time. and team player record is bound with team

8

I know that your Team may have multiple Players through team_player junction table, and Player may have multiple Teams through team_player junction table. It’s a well known pattern of MANY-MANY relation through a junction table.

But just see the relation between Team and TeamPlayer. A Team has many TeamPlayers, right? It’s a 1-N relation. Then, a TeamPlayer can have only one Team, because it’s the opposite side of the 1-N relation.
In the same way, a Player has many TeamPlayers and a TeamPlayer has only one Player.

Or you may think about the following - A TeamPlayer has team_id field. How many Teams can a TeamPlayer have? It’s just one Team with the specified team id, isn’t it? And a TeamPlayer has player_id field. How many Players can a TeamPlayer have? Just one Player with the specfied player id in the same way.

1 Like
9

Hi @softark,
You are right. I took it in wrong way but now How i can access the teamplayer status. Can you provide me the solution?

10

First off, I have to apologize that I made a mistake in the first reply. I’ve proposed a solution, but I was wrong. I misunderstood your need.

I would do it like what @fabriziocaldarelli has proposed as the 1st solution.

$teams = Team::find()
  ->innerJoinWith(‘teamPlayer’)
  ->where([‘team_player.player_id’=> $id])
  ->all();
foreach ($teams as $team) {
    echo $team->name;
    foreach($team->teamPlayers as $tp) {
        echo $tp->status;
    }
}

Not tested, but also the following will work:

$teams = Team::find()
  ->select('team.id', 'team.name')
  ->innerJoinWith(‘teamPlayer’)
  ->where([‘team_player.player_id’=> $id])
  ->all();

The key point is that you have to select team.id because it is used to fetch the related records of TeamPlayer.

Note that in the relational queries of ActiveRecord, Yii always uses 2 queries. One for the main model and the other for the related model.

The 1st query:

SELECT * FROM team INNER JOIN WITH team_player WHERE team_player.player_id = XX
or
SELECT team.name, team.id FROM team INNER JOIN WITH team_player WHERE team_player.player_id = XX

Then the array of Team model will be created using the result set of the 1st query.
And before the 2nd query, Yii will gather the team.ids in order to fetch the related records.

The 2nd query:

SELECT * from TEAM_PLAYER WHERE team_player.team_id in (...)

Where ... stands for the gathered team.ids. And the result set will be used to create the arrays of the related TeamPlayer models.

So, you have to select team.id in the first query. And selecting team_player.status in the first query has no meaning.

Please check the following section of the guide:
ActiveRecord > Working with Relational Data > Lazy Loading and Eager Loading

11

Thanks @softark