Yii Many To Many To Many?

Question

Let's say I have 3 main tables: category, tag, url

category and tag have a MANY_MANY relationship.

tag and url have a MANY_MANY relationship.

I would like from category to retrieve all the linked url.

What is the easiest, most efficient way to do this?

Can I declare a sort of MANY_MANY_MANY relation? Or is this doable in criteria? Or do I have to do it in pure SQL?

I like the idea of Criteria because I will later pass it 'offset' and 'limit' parameters for pagination purposes...

Stu · Answer 1 · 2012-11-19T16:49:09.367

I think the best way would be to create some intermediary tables to hold the many:many information, for example;

CREATE TABLE `categories_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_categories` int(11) NOT NULL,
  `id_tags` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
ALTER TABLE `categories_tags`
  ADD CONSTRAINT `categories_tags_ibfk_1` FOREIGN KEY (`id_categories`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `categories_tags_ibfk_2` FOREIGN KEY (`id_tags`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

and...

CREATE TABLE `tags_urls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_tags` int(11) NOT NULL,
  `id_urls` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
ALTER TABLE `tags_urls`
  ADD CONSTRAINT `tags_urls_ibfk_1` FOREIGN KEY (`id_tags`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `tags_urls_ibfk_2` FOREIGN KEY (`id_urls`) REFERENCES `urls` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

You can use through in your relations between those tables.

In your category model:

public function relations()
{
    return array(
        'categoryTags' => array(self::_MANY, 'CategoriesTags', 'id_categories'),
        'tags' => array(self::HAS_MANY, 'Tag', 'id_tags', 'through'=>'categoryTags'),
        'tagsUrls' => array(self::HAS_MANY, 'TagUrl', 'id_tags', 'through'=>'tags'),
        'urls' => array(self::HAS_MANY, 'Url', 'id_url', 'through'=>'tagsUrls'),
    );
}

To call all your urls for a given category then you should be able to:

$model = Category::model()->findByPk($categoryId);

$model->urls; // will be an array of url models that belong to the category.

[EDIT] Added the edits above after re-reading the question, I've not tested and you may well need to make a few (or many) edits to the code, but the principle should work in theory. There may well be a quicker way to grab the 'through' relations data, but I've not actually used through in two many:many relationships before, so not had need to try it out myself.

Currently `tags` is a MANY_MANY. It's safe to switch it to HAS_MANY? — Nathan H, Nov 19 '12 at 16:30
The documentation says it only works with HAS_MANY or HAS_ONE. Can I still make it work with my MANY_MANY architecture? — Nathan H, Nov 19 '12 at 16:33
@nute Ah, sorry misread, you're right. I'll edit the answer ;) — Stu, Nov 19 '12 at 16:49
ah, awesome, then it should work something like in the relations example above? — Stu, Nov 19 '12 at 17:41

score 0 · Accepted Answer · answered Nov 19 '12 at 16:58

I may have found a trick: VIEWS

I created the following view:

CREATE VIEW category_url AS
(SELECT DISTINCT category.id AS category_id, url.id AS url_id 
FROM category
LEFT JOIN category_tag ON category_tag.category_id=category.id
LEFT JOIN url_tag ON url_tag.tag_id = category_tag.tag_id
LEFT JOIN url ON url.id = url_tag.url_id)

And I added the following relation to Category:

self::MANY_MANY, 'Url', 'category_url(category_id, url_id)'

I haven't done extensive testing yet. Comments welcomed.

Yii Many To Many To Many?

2 Answers2