I have 3 models, Page , Course and Content
Page and Course contain meta data and Content contains HTML content.
Page and Course both hasMany Content
Content belongsTo Page and Course
To avoid having page_id and course_id fields in Content (because I want this to scale to more than just 2 models) I am looking at using Polymorphic Associations. I started by using the Polymorphic Behavior in the Bakery but it is generating waaay too many SQL queries for my liking and it's also throwing an "Illegal Offset" error which I don't know how to fix (it was written in 2008 and nobody seems to have referred to it recently so perhaps the error is due to it not having been designed for Cake 2?)
Anyway, I've found that I can almost do everything I need by hardcoding the associations in the models as such:
Page Model
CREATE TABLE `pages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`)
)
<?php
class Page extends AppModel {
var $name = 'Page';
var $hasMany = array(
'Content' => array(
'className' => 'Content',
'foreignKey' => 'foreign_id',
'conditions' => array('Content.class' => 'Page'),
)
);
}
?>
Course Model
CREATE TABLE `courses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`)
)
<?php
class Course extends AppModel {
var $name = 'Course';
var $hasMany = array(
'Content' => array(
'className' => 'Content',
'foreignKey' => 'foreign_id',
'conditions' => array('Content.class' => 'Course'),
)
);
}
?>
Content model
CREATE TABLE IF NOT EXISTS `contents` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`class` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`foreign_id` int(11) unsigned NOT NULL,
`title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`content` text COLLATE utf8_unicode_ci NOT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
)
<?php
class Content extends AppModel {
var $name = 'Content';
var $belongsTo = array(
'Page' => array(
'foreignKey' => 'foreign_id',
'conditions' => array('Content.class' => 'Page')
),
'Course' => array(
'foreignKey' => 'foreign_id',
'conditions' => array('Content.class' => 'Course')
)
);
}
?>
The good thing is that $this->Content->find('first') only generates a single SQL query instead of 3 (as was the case with the Polymorphic Behavior) but the problem is that the dataset returned includes both of the belongsTo models, whereas it should only really return the one that exists. Here's how the returned data looks:
array(
'Content' => array(
'id' => '1',
'class' => 'Course',
'foreign_id' => '1',
'title' => 'something about this course',
'content' => 'The content here',
'created' => null,
'modified' => null
),
'Page' => array(
'id' => null,
'title' => null,
'slug' => null,
'created' => null,
'updated' => null
),
'Course' => array(
'id' => '1',
'title' => 'Course name',
'slug' => 'name-of-the-course',
'created' => '2012-10-11 00:00:00',
'updated' => '2012-10-11 00:00:00'
)
)
I only want it to return one of either Page or Course depending on which one is specified in Content.class
UPDATE: Combining the Page and Course models would seem like the obvious solution to this problem but the schemas I have shown above are just shown for the purpose of this question. The actual schemas are actually very different in terms of their fields and the each have a different number of associations with other models too.
UPDATE 2
Here is the query that results from running $this->Content->find('first'); :
SELECT `Content`.`id`, `Content`.`class`, `Content`.`foreign_id`, `Content`.`title`,
`Content`.`slug`, `Content`.`content`, `Content`.`created`, `Content`.`modified`,
`Page`.`id`, `Page`.`title`, `Page`.`slug`, `Page`.`created`, `Page`.`updated`,
`Course`.`id`, `Course`.`title`, `Course`.`slug`, `Course`.`created`,
`Course`.`updated` FROM `cakedb`.`contents` AS `Content`
LEFT JOIN `cakedb`.`pages` AS `Page` ON
(`Content`.`foreign_id` = `Page`.`id` AND `Content`.`class` = 'Page')
LEFT JOIN `cakedb`.`courses` AS `Course` ON (`Content`.`foreign_id` = `Course`.`id`
AND `Content`.`class` = 'Course') WHERE 1 = 1 LIMIT 1
