Wasn't there an episode of Laverne & Shirley in which Fonzi appeared?
On Sun, Sep 23, 2012 at 3:46 PM, Carl Clements <[email protected]> wrote: > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > Visit this group at http://groups.google.com/group/cake-php?hl=en. > > -- 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 [email protected]. To unsubscribe from this group, send email to [email protected]. Visit this group at http://groups.google.com/group/cake-php?hl=en.
