Does anyone knows how Yii handles Mysql CONSTRAINS like ON DELETE or ON UPDATE CASCADE when using MANY_MANY relations?. Since MANY to MANY relations aren’t natively supported on most SQL DBs we need an associative table to declare a MANY_MANY relation on Yii.[/size]
Example:
tbl_post has a MANY_MANY relationship with tbl_tag via the tbl_post_tag associative table
Something like this:
class Post extends CActiveRecord
{
......
public function relations()
{
return array(
'tags'=>array(self::MANY_MANY, 'Tag',
'tbl_post_tag(post_id, tag_id)'),
);
}
}
class Tag extends CActiveRecord
{
......
public function relations()
{
return array(
'posts'=>array(self::MANY_MANY, 'Post',
'tbl_post_tag(post_id, tag_id)'),
);
}
}
What’s the best way to get CONSTRAIN like ON DELETE CASCADE to work properly?, so that if I delete a post, all of the post tags are deleted as well as the associative rows on tbl_post_tag.
Should I handle this via a function on the models?, attaching a behavior?, using beforeDelete() / beforeSave()?, or does Yii behaves like this natively?
Yes, but since Many to Many constrains/foreign keys aren’t supported in Mysql, the cascade wouldn’t affect the whole relationship structure, just the original table and the associative table.
so if I DELETE FROM tbl_post WHERE id = 1; the cascade will extend only to tbl_post and tbl_post_tag, and not to tbl_tag, which is what i want to achieve.
Yeah, I’m starting to understand that using the MANY_MANY relation is not really a good practice (it’s not going to make it to Yii 2.0). It seems the best way to handle this problem is to create a model for “middle” tables and use “Through” relations combined with beforeDelete() / afterDelete() or the With Related Behavior Extension (Seems that it’s going to be part of Yii2 in some form)
Thanks for all the help!, and I hope this helps people in the future.
