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', array( 'criteria'=>array( 'with'=>array( 'subcategory'=>array( 'condition'=>'subcategory.category_id=' . $_GET['category'], ), ), 'together'=>true, ), ) );
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?