I’ve been working on a project in which I was hoping to implement multiple table inheritance but ended up using separate AR models that do not extend from one another. I’d wanted to extend one model from another, because they will share some common data. Instead, I took the path of least resistance and created two separate models that extend CActiveRecord with the intention of accessing the data using relations, as per the recommendations of many different online sources. (Creating extended objects is easy. The persistence layer - a MySQL db - is what became problematic.) Unfortunately, I’m running into problems with the relations when trying to access the data. I’m assuming I’ve made a/some mistake(s) in the set-up, but I can’t see where I’ve gone wrong.
First the nutshell overview, and then the code:
[list=1]
[*]I am representing calendar events using a model called Event. It has its own table.
[*]One specific type of event is a store’s OperatingHours. That is represented in a model called (you guessed it) OperatingHours. There is a separate table for this with a foreign key referencing Event so that they can share common information.
[*]There is another model called Entity. Entities have Events.
[/list]
Now for some code. I’ll just post the relations portions for each model:
The relations function for Entity
/**
* @return array relational rules.
*/
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'businessType' => array(self::BELONGS_TO, 'EntityType', 'entity_type_id'),
'entityPaymentTypes' => array(self::HAS_MANY, 'EntityPaymentType', 'entity_id'),
'paymentTypes' => array(self::HAS_MANY, 'PaymentType', 'payment_type_id', 'through'=>'entityPaymentTypes'),
'address' => array(self::BELONGS_TO, 'Address', 'address_id'),
'phone' => array(self::BELONGS_TO, 'Phone', 'phone_id'),
'mapCoordinate' => array(self::BELONGS_TO, 'MapCoordinate', 'map_coordinate_id'),
'parking' => array(self::BELONGS_TO, 'Parking', 'parking_id'),
'image' => array(self::BELONGS_TO, 'Image', 'image_id'),
'categories' => array(self::HAS_MANY, 'EntityCategory', 'entity_id'),
'events' => array(self::HAS_MANY, 'Event', 'entity_id'),
'businessHours'=>array(self::HAS_ONE, 'OperatingHours', 'event_id', 'through'=>'events'),
);
}
The relations function for Event
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'scheduleElements' => array(self::BELONGS_TO, 'ScheduleElement', 'schedule_element_id'),
'temporalExpressions' => array(self::HAS_MANY, 'CollectionTE', 'temporal_expr_id', 'through'=>'scheduleElements'),
'entity' => array(self::BELONGS_TO, 'Entity', 'entity_id'),
'image' => array(self::BELONGS_TO, 'Image', 'image_id'),
'exhibitEvents' => array(self::HAS_MANY, 'ExhibitEvent', 'event_id'),
'featuredEvents' => array(self::HAS_MANY, 'FeaturedEvent', 'event_id'),
'operatingHours' => array(self::HAS_MANY, 'OperatingHours', 'event_id'),
'promotions' => array(self::HAS_MANY, 'Promotion', 'event_id'),
);
}
The relations function for OperatingHours
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'event'=>array(self::BELONGS_TO, 'Event', 'event_id'),
);
}
CREATE TABLE statement for Entity
CREATE TABLE IF NOT EXISTS `tbl_entity` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`is_active` tinyint(3) unsigned NOT NULL DEFAULT '1',
`label` varchar(100) NOT NULL,
`url` varchar(255) DEFAULT NULL,
`address_id` int(10) unsigned DEFAULT NULL,
`phone_id` int(10) unsigned DEFAULT NULL,
`description` text,
`image_id` int(10) unsigned DEFAULT NULL,
`map_coordinate_id` int(10) unsigned DEFAULT NULL,
`parking_id` int(10) unsigned DEFAULT NULL,
`entity_type_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `phone_id` (`phone_id`),
KEY `image_id` (`image_id`),
KEY `map_coordinate_id` (`map_coordinate_id`),
KEY `parking_id` (`parking_id`),
KEY `entity_type_id` (`entity_type_id`)
)
CREATE statement for Event table
CREATE TABLE IF NOT EXISTS `tbl_event` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`description` text,
`url` varchar(255) DEFAULT NULL,
`entity_id` int(10) unsigned DEFAULT NULL,
`image_id` int(10) unsigned DEFAULT NULL,
`schedule_element_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `entity_id` (`entity_id`),
KEY `image_id` (`image_id`),
KEY `schedule_id` (`schedule_element_id`)
)
CREATE statement for OperatingHours table
CREATE TABLE IF NOT EXISTS `tbl_operating_hours_event` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`event_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `event_id` (`event_id`)
)
What I want to be able to do is set up the relations such that, when I retrieve an Entity, I have access to $entity->events->operatingHours. Or better still (what I was really hoping for when I wrote the relations method for Entity): $entity->operatingHours.
The current situation using my code as pasted above results in an error message as follows:
This is not what I was expecting with “through.” My expectation was that it would use the foreign key, “event_id”, in the OperatingHours table to get the Event data which would then be related to Entity through the “entity_id” foreign key column in Events. I’ve seen that someone has posted a similar issue
here.
One thing that complicates this is that an Entity might have several different Events, each of which might be of a different type. This suggests that what I need to do is include the entity_id in the individual events tables (e.g., the OperatingHours table) rather than the Event table itself, but I’m conceptualizing Event as the “base class” with the other tables/models as holding bits and pieces of additional, unique, information.
Can anyone see where my relations are wrong? Suggestions for an improved design? Thoughts on a different/better way to get at the data?
Thanks!