Here is an example almost exactly like the one I am trying to solve, but
using more familiar objects (television series) to hopefully allow common
knowledge to fill in any gaps to my description
3 tables: series, character, episode
Series hasMany Charater
Series hasMany Episode
and conversely
Character belongsTo Series
Episode belongsTo Series
Now I also want to associate characters with the episodes in which they
appear.
Character hasAndBelongsToMany Episode
Episode hasAndBelongsToMany Character
I've set that up, everything worked fine. As I played around with
scaffolding I realized it would be posible to associate characters with
episodes from different series. That could create a lot of confusion if
this ever happened. Although there are probably many ways to avoid this in
the controller, I wanted to force this constraint on the model... or better
yet the database itself.
My first though was to simply add a condition in the association. Something
like this:
class Character extends AppModel {
var $hasAndBelongsToMany = array(
'Episode' => array(
'className' => 'Episode',
'joinTable' => 'characters_episodes',
'foreignKey' => 'character_id',
'associationForeignKey' => 'episode_id',
'unique' => true,
'conditions' => 'Episode.series_id = Character.series_id'
)
);
}
But performing $this->Episode->Character->find('list') produces a bad sql
query:
SELECT `Character`.`id`, `Character`.`series_id`, `Character`.`name`,
`CharactersEpisode`.`id`, `CharactersEpisode`.`episodes_id`,
`CharactersEpisode`.`characters_id`, `CharactersEpisode`.`series_id` FROM
`characters` AS `Character` JOIN `episodes_characters` AS
`CharactersEpisode` ON (`CharactersEpisode`.`episode_id` = 1 AND
`CharactersEpisode`.`character_id` = `Character`.`id`) WHERE
`Episode`.`series_id` = `Character`.`series_id`
(I'm sorry if I'm getting my Character and Episode examples mixed up here,
these aren't my actual model names and the problem is symmetrical, as in I
want the condition to work both ways, so it shouldn't make a difference)
My second thought was to add a column (series_id) to the join table
(characters_episodes), and add that column to both FK constraints.
Something like this:
CONSTRAINT `fk_episodes_has_characters_episodes1`
FOREIGN KEY (`episode_id`, 'series_id')
REFERENCES `tvdb`.`episodes` (`id`, 'series_id' ),
CONSTRAINT `fk_episodes_has_characters_characters1`
FOREIGN KEY (`character_id`, 'series_id' )
REFERENCES `tvdb`.`characters` (`id`, 'series_id' )
Which seems to work (mysql accepts the schema), but Cake doesn't like
multi-column assoiciations. I've read that if I find myself needing them
then there is a flaw in my design. Can somebody point out the flaw and
provide suggestions or even clues to solving it the way Cake prefers?
Thanks,
Carl
--
Like Us on FacekBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com.
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
