Question about relations many to many

So I was wondering if it would be possible to run this query

SELECT `home`.`screenname` as `home_player`, `away`.`screenname` as `away_player`, `tm`.`home_score` as `home_score`, `tm`.`away_score` as `away_score`, `tm`.`reported_at` as `reported_at` From `tournament_match` as `tm` LEFT JOIN `player` as `home` ON `tm`.`home_id` = `home`.`id` LEFT JOIN `player` as `away` ON `tm`.`away_id` = `away`.`id` where `tm`.`tournament_id` = 1 AND `tm`.`status` = 2

via the model using relations.

So I have 4 tables

Tournament, Tournament_Match, Tournament_Participant, and Player

I’m pretty sure the SQL is self explanatory here.

Just wondering if it’s possible without running the raw query for sake of learning.

Your question is rather unclear. On which model do you want the relation and objects of what model should it return?

Btw. your table Tournament_Participant doesn’t show up in your query.

Yeah, there isn’t any data that is needed from that table and both the tournament_match and torunament_participant have a relation to the the player table.

So the relations are like so:

Tournament_Match and Tournament_Participant have a column tournament_id which is related to Tournament

Tournament_Match has two columns home_id and away_id which are related to Tournament_Participant and Player.

The Player table has holds the screenname which I am ultimately wanting to display on the page.

$model = Tournament::findModel($id);

//Am I wrong that both these should render the screen name from the player table with the relations below.

$model->tournamentMatch->home->player->screenname;

$model->tournamentMatch->away->player->screenname;

The models look like this

[code]

class Tournament

{

…

public function getTournamentMatches()


{


    return $this->hasMany(TournamentMatch::className(), ['tournament_id' => 'id']);


}

}

class TournamentMatch

{

…

public function getAway()


{


    return $this->hasOne(TournamentParticipant::className(), ['player_id' => 'away_id']);


}








public function getHome()


{


    return $this->hasOne(TournamentParticipant::className(), ['player_id' => 'home_id']);


}

}

class TournamentParticipant

{

…

public function getPlayer()


{


    return $this->hasOne(Player::className(), ['id' => 'player_id']);


}

}

Basically what I’m doing is from the TournamentController I am rendering the Bracket and

SELECT home.screenname as home_player, away.screenname as away_player, tm.home_score as home_score, tm.away_score as away_score, tm.reported_at as reported_at

From tournament_match as tm

LEFT JOIN player as home ON tm.home_id = home.id

LEFT JOIN player as away ON tm.away_id = away.id

where tm.tournament_id = 1 AND tm.status = 2

According to your model code, A tournament hasMany() TournamentMatches, so, while close, this won’t work. If you would want to get home player’s screenname for the tournaments first match match you would do:

$model->tournamentMatch[0]->home->player->screenname;

As with your model, a tournament can have multiple matches, it wouldn’t know which one to take, so you have to tell it. (If there’s only one match to a tourny, you should change it to hasOne())

In your SQL Query you also had tm.status = 2. You could do this like that:

public function getTournamentMatches()

{

return $this->hasMany(TournamentMatch::className(), ['tournament_id' => 'id'])->where(["status" => 2]);

}

If there are multiple matches to a tournament, but (per tournament) they all have a different status you could do:

public function getFinishedMatch() // You choose the name according to the meaning of the status.

{

return $this->hasOne(TournamentMatch::className(), ['tournament_id' => 'id'])->where(["status" => 2]);

}

and then do

$model->finishedMatch->home->player->screenname;

I’m an idiot! Thanks for the clarification though… {{SMH}}

np!