A MANY_MANY through relationship?

Yii 1.1 General Discussion
1

This question involved four tables:

  • tag

  • website_to_tag

  • website, with a MANY_MANY relationship to tag through website_to_tag

  • company, with a HAS_MANY relationship to website

I’m trying to create a data provider for the results of searching for companies. I would like to be able to search companies based on the tags that websites have.

I can do this by writing the SQL joins by hand:




LEFT OUTER JOIN website websites ON websites.company_id = t.id

LEFT OUTER JOIN website_to_tag w2t ON w2t.website_id = websites.id

LEFT OUTER JOIN tag ON tag.id = w2t.tag_id

But it would be nicer to do it the ‘Yii way’.

Here is what I tried. In the company model, I put the following relations:




'websites' => array(self::HAS_MANY, 'Website', 'company_id'),

'websiteTags' => array(self::MANY_MANY, 'Tag', 'website_to_tag(website_id, tag_id)', 'through' => 'websites')

And in the website model:




'tags' => array(self::MANY_MANY, 'Tag', 'website_to_tag(website_id, tag_id)'),

And the CDbCriteria is set up like this:




$criteria = new CDbCriteria;

$criteria->with = array('websiteTags');

$criteria->together = true;

This doesn’t work quite right. Here are the SQL joins that it generates:




LEFT OUTER JOIN `website` `websites` ON (`websites`.`company_id`=`t`.`id`)  

LEFT OUTER JOIN `website_to_tag` `websiteTags_websiteTags` ON (`t`.`id`=`websiteTags_websiteTags`.`website_id`) 

LEFT OUTER JOIN `tag` `websiteTags` ON (`websiteTags`.`id`=`websiteTags_websiteTags`.`tag_id`)

On the second line, the join predicate is matching the column website_to_tag.website_id to company.id, not website.id.

Is this a bug? If not, is there a way to fix it?

Thanks

2

No. MANY_MANY relation has no ‘through’ option.

Yes. The way to fix is using ‘through’ option when it’s really needed :) You can solve you problem by using this criteria:




$criteria = new CDbCriteria;

$criteria->with = array('websites.tags');
3

That works perfectly, thank you.