MySQL two columns relate to another tables column

Question

MySQL two columns relate to another tables column

Eike Oct '13

Created Oct '13	Last Reply Oct '13	Replies 3	Views 1854	Votes 2

Eike 12.1k

Oct '13

Hey community!

I have one table "Tickers" with the columns "team_home" and "team_guest" and another table "Teams" with "id" and "name". Now I want to get the teams without separately calling the teams-model.

So I think the code should look like the following

foreach (Tickers::findFirst() as $ticker) {
    $teamHome = $ticker->teamHomeObj;
    $teamGuest = $ticker->teamGuestObj;
}

And NOT:

foreach (Tickers::findFirst() as $ticker) {
    $teamHome = Teams::findFirst("id=".$ticker->team_home);
    $teamGuest = Teams::findFirst("id=".$ticker->team_guest);
}

greetings Eike

2

Evgeny Sinitsyn
269

Accepted
answer

Oct '13

Seems alias should solve this problem.

        $this->hasMany('id', 'Fresh\Models\UserChangePassword', 'usersId', array(
            'alias' => 'passwordChanges',
            'foreignKey' => array(
                'message' => 'User cannot be deleted because he/she has activity in the system'
            )
        ));

Usage:

$user->getPasswordChanges();

xAockd · Answer 1 · 2013-10-16T07:47:24-07:00

read this https://docs.phalcon.io/en/latest/reference/models.html#defining-relationships

hope this help you

Eike · Answer 2 · 2013-10-16T08:32:16-07:00

First: Thanks for your reply! I already read this part. In my understanding there is only mentioned the relation of one column of a table to one column of another table. But I have TWO COLUMNS each of them relating to ONE COLUMN of another table Am I wrong?

Edit: Maybe it has something to do with this sentence "You could also use arrays to define multiple fields in the relationship." But there is no example in the docs :(

xAockd · Answer 3 · 2013-10-16T09:10:15-07:00

I guess you should use OneToMany relation. One Ticker has Many (2) team. And use:

foreach (Tickers::find() as $ticker) {
foreach($ticker->getTeam() as $team){
      //do some stuff with your team
}
$teamHome = Teams::findFirst("id=".$ticker->team_home);
$teamGuest = Teams::findFirst("id=".$ticker->team_guest);
}

Hope this help P.S. findFirst() returns 1 row, find() returns N rows

Eike · Answer 4 · 2013-10-16T10:27:32-07:00

I cannot find the doku for the method oneToMany. Do you mean hasMany? That is what I already have.

Tickers:

$this->hasMany("team_home", "Teams", "id");
$this->hasMany("team_guest", "Teams", "id");

Teams:

$this->belongsTo("id", "tickers", "team_home");
$this->belongsTo("id", "tickers", "team_guest");

foreach (Tickers::find() as $ticker) {
    foreach($ticker->getTeam() as $team) {
        // Here I only get the Team of the second column
        xdebug_var_dump($team->name);
    }
}

Edit: Even in the mysql log only one team is selected

  4 [Wed, 16 Oct 13 19:41:35 +0200][INFO] SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='tickers'
  5 [Wed, 16 Oct 13 19:41:35 +0200][INFO] DESCRIBE `tickers`
  6 [Wed, 16 Oct 13 19:41:35 +0200][INFO] SELECT `tickers`.`team_home`, `tickers`.`team_guest` FROM `tickers` WHERE `tickers`.`id` = 20
  7 [Wed, 16 Oct 13 19:41:35 +0200][INFO] SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='teams'
  8 [Wed, 16 Oct 13 19:41:35 +0200][INFO] DESCRIBE `teams`
  9 [Wed, 16 Oct 13 19:41:35 +0200][INFO] SELECT `teams`.`id`, `teams`.`name` FROM `teams` WHERE `teams`.`id` = :0

Eike · Answer 5 · 2013-10-16T23:37:42-07:00

Thanks a lot @xAockd and @cuhuak! The alias was the solution.

My code now: Teams

$this->belongsTo("id", "Tickers", "team_home");
$this->belongsTo("id", "Tickers", "team_guest");

Tickers

$this->hasMany("team_home", "Teams", "id", array(
    "alias" => "teamHomeObjekt"
));
$this->hasMany("team_guest", "Teams", "id", array(
    "alias"  => "teamGuestObjekt"
));