Please advice how to retrieve data from pivot table (AR Models):
table player:
player_id ‘1’
player_label ‘Player A’
table team:
team_id ‘1’
team_label ‘Team A’
team_id ‘2’
team_label ‘Team B’
table team_player:
player_id ‘1’
team_id ‘1’
start_date ‘2012-12-12’
player_id ‘1’
team_id ‘2’
start_date ‘2013-12-12’
I need to get start_date column as well.
Have you read docs?
Yes, there is info how to get data from another table via pivot table, but not from the pivot itself. Please correct me if I mistake.
Technically, since MANY_MANY is implemented as hasMany now, you can just set MANY_MANY using via() (that is, relation-based), and grab relation’s data using with().
Thank you for your prompt reply, will try to find out.
I have models Team, Player and TeamPlayer generated with Gii, in db I have the following data:
table player:
player_id | player_label
1 | Player 1
2 | Player 2
table team:
team_id | team_label
1 | Team 1
2 | Team 2
table team_player:
team_id | player_id | start_date
1 | 1 | t1 p1
1 | 2 | t1 p2
2 | 1 | t2 p1
/** @var Team[] $teams */
$teams = Team::find()
->with('players')
->all();
foreach ($teams as $team) {
echo $team->team_label . '<br />';
foreach ($team->players as $player) {
echo ' ... ' . $player->player_label . ' ' . $player->teamPlayer->start_date . '<br />';
}
}
I receive the following result, which is wrong - ‘Team 2’ should contain ‘Player 1 t2 p1’:
Team 1
… Player 1 t1 p1
… Player 2 t1 p2
Team 2
… Player 1 t1 p1
Please advise how to get the correct result.
Seems like you reversed teamid and playerid in your relation definition. Also consider eager loading of pivot data.
Relations are generated with Gii, what do you mean under "you reversed teamid and playerid in your relation definition".
Model Team:
/**
* @return \yii\db\ActiveRelation
*/
public function getTeamPlayer()
{
return $this->hasOne(TeamPlayer::className(), ['team_id' => 'team_id']);
}
/**
* @return \yii\db\ActiveRelation
*/
public function getPlayers()
{
return $this->hasMany(Player::className(), ['player_id' => 'player_id'])->viaTable('team_player', ['team_id' => 'team_id']);
}
Model Player:
/**
* @return \yii\db\ActiveRelation
*/
public function getTeamPlayer()
{
return $this->hasOne(TeamPlayer::className(), ['player_id' => 'player_id']);
}
/**
* @return \yii\db\ActiveRelation
*/
public function getTeams()
{
return $this->hasMany(Team::className(), ['team_id' => 'team_id'])->viaTable('team_player', ['player_id' => 'player_id']);
}
Model TeamPlayer:
/**
* @return \yii\db\ActiveRelation
*/
public function getPlayer()
{
return $this->hasOne(Player::className(), ['player_id' => 'player_id']);
}
/**
* @return \yii\db\ActiveRelation
*/
public function getTeam()
{
return $this->hasOne(Team::className(), ['team_id' => 'team_id']);
}
Considering eager loading of pivot data - I changed it to the code below, result is the same:
/** @var Team[] $teams */
$teams = Team::find()
->with('teamPlayer')
->all();
I have no idea how are you using Gii (maybe there’s an error or something) but obviously Team:: getTeamPlayer is wrong (should be hasMany) and Player::getTeamPlayer also.
Ok, here’s a working example of what I mean:
ModelA:
public function getAbPivot()
{
return $this->hasMany(ABPivot::className(), ['a_id' => 'id']);
}
public function getModelBs()
{
return $this->hasMany(ModelB::className(), ['id' => 'b_id'])
->via('abPivot');
}
Controller:
$data = \app\models\ModelA::find()
->with(['modelBs', 'abPivot']) //notice extra relation
->asArray()
->all();
After changing relations in models Player and Team the way you wrote above I get it working correct with the code below:
/** @var Team[] $teams */
$teams = Team::find()
->with(['players', 'teamPlayer'])
->all();
foreach ($teams as $team) {
echo $team->team_label . '<br />';
foreach ($team->players as $key => $player) {
echo ' ... ' . $player->player_label . ' -> ' . $team->teamPlayer[$key]->start_date . '<br />';
}
}
Does Gii generate wrong relations by foreign key constraints or do I make mistake in table structure? Here is my db dump:
CREATE TABLE IF NOT EXISTS `player` (
`player_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`player_label` varchar(255) DEFAULT NULL,
PRIMARY KEY (`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
INSERT INTO `player` (`player_id`, `player_label`) VALUES
(1, 'Player 1'),
(2, 'Player 2'),
(3, 'Player 3');
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `team` (
`team_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`team_label` varchar(255) DEFAULT NULL,
PRIMARY KEY (`team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
INSERT INTO `team` (`team_id`, `team_label`) VALUES
(1, 'Team 1'),
(2, 'Team 2'),
(3, 'Team 3');
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `team_player` (
`team_id` int(11) unsigned NOT NULL,
`player_id` int(11) unsigned NOT NULL,
`start_date` varchar(255) DEFAULT NULL,
PRIMARY KEY (`team_id`,`player_id`),
KEY `player_id` (`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `team_player` (`team_id`, `player_id`, `start_date`) VALUES
(1, 1, 't1 p1'),
(1, 2, 't1 p2'),
(2, 1, 't2 p1'),
(2, 3, 't2 p3'),
(3, 3, 't3 p3');
ALTER TABLE `team_player`
ADD CONSTRAINT `team_player_ibfk_2` FOREIGN KEY (`player_id`) REFERENCES `player` (`player_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `team_player_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`team_id`) ON DELETE NO ACTION ON UPDATE CASCADE;
I don’t know, maybe it’s because of extra "KEY player_id". Anyway, all the autogenerated stuff must be looked through, because robots are… well, you know.
PS. Btw you sure on "ON DELETE NO ACTION" in pivot table?
I leave no acion on delete because these tables are just for example. Without extra key player_id gii generates the same code - the problem must be in gii, it would be better to check relations or to write them by hand.
Thanks a lot for your time and explanation.