A Bono Wed Mar 21 13:37:48 -0400 2012

Subject: php-activerecord and 2 foreign keys referring to one table

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:
home_team_id // referring to "teams.name"
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:
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:
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;


Adrian Wobito Thu Mar 22 14:11:54 -0400 2012

I had the same problem as you, I was able to figure it out with the following posting
http://www.phpactiverecord.org/boards/4/topics/1124-re-simple-i-think-association-problem-help

but here is the code you'll want.

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

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

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

A Bono Fri Mar 23 05:53:29 -0400 2012

Thanks Adrian, it works. I just needed to make a small change to match class names:

before:

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

after:

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

(1-2/2)