Add Relation to Relation of CActiveDataProvider

Hello all,

I am pretty stuck.

I have the following table design:


tbl_subcategory (category_id has FK to tbl_category id)


tbl_category (has PK to tbl_subcategory.category_id)

tbl_product_subcategory is a n:m joiner table for products and its subcategory. Each subcategory belongs to a parent category.

What I am trying to accomplish is in CActiveDataProvider getting a list of products based on a parent category id.

I have the followings relations setup already:


	'subcategory' => array(self::MANY_MANY, 'ProdSubcategory', 'tbl_products_category(product_id, subcategory_id)'),


return array(

			'category'=>array(self::BELONGS_TO, 'ProdCategory', 'id'),

			'product'=>array(self::MANY_MANY, 'Products', 'tbl_products_category(subcategory_id, product_id)'),



return array(

			'subcategory'=>array(self::HAS_MANY, 'ProdSubcategory', 'category_id'),


My current code for the dataprovider is:

$dataProvider = new CActiveDataProvider('Products',





								'condition'=>'subcategory.category_id=' . $_GET['category'],







But, as you might imagine - this does not work. The subcategory relation in the with only has two columns (product_id and subcategory_id) so it cannot be used as the criteria to limit by the parent category.

Do I / Can I add a with to the subcategory relation? I.e. can I attach the subcategory tables relation with category to the data provider?

What is the appropriate way of handling this type of relation?

Thank you!


it seems I spoke to soon. For somereason, (I honestly don’t know why) this works just fine.

Why does this condition work?

'condition'=>'subcategory.category_id=' . $_GET['category'],

when category_id is not a valid column in the table referenced in the subcategory relation.