Re: tricky HABTM condition (or maybe situation where multi-column foreign key is needed)

Wasn't there an episode of Laverne & Shirley in which Fonzi appeared?


On Sun, Sep 23, 2012 at 3:46 PM, Carl Clements <typo101@clemnet.info> 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 cake-php@googlegroups.com.
> To unsubscribe from this group, send email to
> cake-php+unsubscribe@googlegroups.com.
> 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 cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.