This question involved four tables:
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?