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

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.