php-activerecord and 2 foreign keys referring to one table

[eluser]thevenin[/eluser]
Hi.

In php-activerecord it is easy to create association with foreign table when we're using standard field names ("user_id", "post_id", etc.) and there is only one FK associated with one foreign table: user_id -> users table, post_id -> posts table, etc.

In my database I would like to retrieve teams' names for each match (table: matches).

Every match record has 2 foreign keys:

Code:

home_team_id // referring to "teams.name"

Code:

away_team_id // referring to "teams.name"

I know how to create query in plain SQL but I would like to use php-activerecord.

Do you have any idea how to create delegates/associations to retrieve teams' names in $match object?


"teams" table:

Code:

CREATE TABLE `teams` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `short_name` varchar(16) DEFAULT NULL,
  `code` varchar(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

"matches" table:

Code:

CREATE TABLE `matches` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `home_team_id` int(11) unsigned NOT NULL,
  `away_team_id` int(11) unsigned NOT NULL,
  `home_goals` enum('0','1','2','3','4','5','6','7','8','9','10') NOT NULL DEFAULT '0',
  `away_goals` enum('0','1','2','3','4','5','6','7','8','9','10') NOT NULL DEFAULT '0',
  `starting_at` datetime NOT NULL,
  `status` enum('not_started','afoot','finished') NOT NULL DEFAULT 'not_started',
  `stage` enum('group_a','group_b','group_c','group_d','quarter_final','semi_final','final') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `home_team` (`home_team_id`),
  KEY `away_team` (`away_team_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

[eluser]Akinzekeel[/eluser]
I think you just need a JOIN. Also, you should use table aliases and field name aliases because you'll have to join the same table twice. Here's an example (untested):

Code:

$this->db->select( "m.id, ht.name home_team_name, at.name away_team_name" );
$this->db->join( "teams ht", "ht.id = home_team_id" );
$this->db->join( "teams at", "at.id = away_team_id" );
$result = $this->db->get( "matches m" )->result();

foreach( $result as $match )
    echo "Match " . $match->id . ": " . $match->home_team_name . " VS " . $match->away_team_name;

[eluser]thevenin[/eluser]
sHiRoKKo1337 thank you for your contribution.

I found the answer on phpactiverecord official forum:

Code:

class Match extends ActiveRecord\Model {
  
    static $table_name = 'matches';

    static $belongs_to = array(
        array('home_team', 'class'=>'Team','foreign_key'=>'home_team_id'),
        array('away_team', 'class'=>'Team','foreign_key'=>'away_team_id')
    );
}

Code:

class Team extends ActiveRecord\Model {
    
    static $has_many = array(
        array('home_matches', 'class'=> 'Match'),
        array('away_matches', 'class'=> 'Match')
    );    
}

In the controller:

Code:

class Matches extends MY_Controller
{
    function index()
    {
        $this->output->enable_profiler(TRUE);
        $this->view_data['matches'] = Match::find('all');
    }
}

and finally in the index.php view:

Code:

<?foreach($matches as $match):?>
<li>&lt;?=$match->home_team->name?&gt; - &lt;?=$match->away_team->name?&gt;</li>
&lt;?endforeach?&gt;

Best regards.