Elisamuel Resto Thu May 17 14:31:24 -0400 2012

Subject: Foreign Keys and Linked InnoDB tables

I've looked around and tried several things, and as far as one right here on the forums but even following the recommendation (that actually applies to my case) does not work.

Basically, here's the deal: core_store has website_id and group_id that link to two tables respectively: core_website whose primary key is website_id and core_store_group whose primary key is group_id.

For whatever hellish reason, it keeps using store_id to pull group_id and website_id which is completely incorrect, but I can't seem to find the proper way to tell this to AR. I have tried specifying primary_key in the $has_one arrays, and in the $belongs_to arrays in the target classes, specifying foreign_key on both also does not seem to do anything other than tell it to use the correct column in the child tables... even specifying BOTH of them does not cure this. I can't see what I'm doing wrong, so... here's some code in hopes I can be educated as to the proper way of doing this, because either I am doing something wrong (likely) or I want to do something AR was not designed for (doubtful).

 1 class Store extends ActiveRecord\Model {
 2     public static $table_name = 'core_store';
 3     public static $primary_key = 'store_id';
 4 
 5     public static $has_one = array(array('core_store_group', 'class_name' => 'StoreGroup', 'foreign_key' => 'group_id', 'primary_key' => 'group_id', 'readonly' => true),
 6                                      array('core_website', 'class_name' => 'StoreWebsite', 'foreign_key' => 'website_id', 'primary_key' => 'website_id', 'readonly' => true));
 7 }
 8 
 9 class StoreGroup extends ActiveRecord\Model {
10     public static $table_name = 'core_store_group';
11     public static $primary_key = 'group_id';
12 
13     public static $belongs_to = array(array('core_store', 'class_name' => 'Store', 'readonly' => true));
14 }
15 
16 class StoreWebsite extends ActiveRecord\Model {
17     public static $table_name = 'core_website';
18     public static $primary_key = 'website_id';
19 
20     public static $belongs_to = array(array('core_store', 'class_name' => 'Store', 'readonly' => true));
21 }
 1 CREATE TABLE IF NOT EXISTS `core_store` (
 2   `store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store Id',
 3   `code` varchar(32) DEFAULT NULL COMMENT 'Code',
 4   `website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
 5   `group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
 6   `name` varchar(255) NOT NULL COMMENT 'Store Name',
 7   `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Sort Order',
 8   `is_active` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Activity',
 9   PRIMARY KEY (`store_id`),
10   UNIQUE KEY `UNQ_CORE_STORE_CODE` (`code`),
11   KEY `IDX_CORE_STORE_WEBSITE_ID` (`website_id`),
12   KEY `IDX_CORE_STORE_IS_ACTIVE_SORT_ORDER` (`is_active`,`sort_order`),
13   KEY `IDX_CORE_STORE_GROUP_ID` (`group_id`)
14 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Stores' AUTO_INCREMENT=71 ;
15 
16 CREATE TABLE IF NOT EXISTS `core_store_group` (
17   `group_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Group Id',
18   `website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
19   `name` varchar(255) NOT NULL COMMENT 'Store Group Name',
20   `root_category_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Root Category Id',
21   `default_store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Default Store Id',
22   PRIMARY KEY (`group_id`),
23   KEY `IDX_CORE_STORE_GROUP_WEBSITE_ID` (`website_id`),
24   KEY `IDX_CORE_STORE_GROUP_DEFAULT_STORE_ID` (`default_store_id`)
25 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Store Groups' AUTO_INCREMENT=72 ;
26 
27 CREATE TABLE IF NOT EXISTS `core_website` (
28   `website_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Website Id',
29   `code` varchar(32) DEFAULT NULL COMMENT 'Code',
30   `name` varchar(64) DEFAULT NULL COMMENT 'Website Name',
31   `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Sort Order',
32   `default_group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Default Group Id',
33   `is_default` smallint(5) unsigned DEFAULT '0' COMMENT 'Defines Is Website Default',
34   PRIMARY KEY (`website_id`),
35   UNIQUE KEY `UNQ_CORE_WEBSITE_CODE` (`code`),
36   KEY `IDX_CORE_WEBSITE_SORT_ORDER` (`sort_order`),
37   KEY `IDX_CORE_WEBSITE_DEFAULT_GROUP_ID` (`default_group_id`)
38 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Websites' AUTO_INCREMENT=37 ;
39 
40 ALTER TABLE `core_store` ADD CONSTRAINT `FK_CORE_STORE_GROUP_ID_CORE_STORE_GROUP_GROUP_ID` FOREIGN KEY (`group_id`) REFERENCES `core_store_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE;
41 ALTER TABLE `core_store` ADD CONSTRAINT `FK_CORE_STORE_WEBSITE_ID_CORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE;
42 
43 ALTER TABLE `core_store_group` ADD CONSTRAINT `FK_CORE_STORE_GROUP_WEBSITE_ID_CORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE;