Filtering With Two Join

Yii 1.1 General Discussion
1

Hi!

Here’s my problem, I have 4 tables like this:

  • Image with a sample_id or a project_id

  • Sample with brand_id

  • Project with brand_id

  • Brand

So, an image can belong to either a project or a sample, and projects and samples belong to a brand.

I’m trying to filter like this:




			$criteria->with = array('sample.brand', 'project.brand');

			$criteria->compare('brand.name',$this->brand_name,true);

But it doesn’t work, it says “Not unique table/alias: ‘brand’”. And because ‘$criteria->with’ makes an outer left join, if I just put ‘sample.brand’, it’s filtering only images that belong to a sample!

If anybody has any idea how to do it, thanks! :)

2

Hello

You may use table alias to differentiate the two relations: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-options

And in your case, I guess you need an additional compare with the ‘OR’ operator:


$criteria->with = array(

                      'sample.brand' => array('alias' => 'sampleBrand'),

                      'project.brand' => array('alias' => 'projectBrand')

                  );

$criteria->compare('sampleBrand.name', $this->brand_name, true);

$criteria->compare('projectBrand.name', $this->brand_name, true, 'OR');

Last but not least, if you have additional conditions, you should have two CDbCriteria instances, and merge them together in order that the above condition get correctly converted (to SQL).

3

Works perfectly! Thank you!