Hello all,
I am pretty stuck.
I have the following table design:
tbl_product
tbl_subcategory (category_id has FK to tbl_category id)
tbl_product_subcategory
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:
products:
'subcategory' => array(self::MANY_MANY, 'ProdSubcategory', 'tbl_products_category(product_id, subcategory_id)'),
ProdSubcategory:
return array(
'category'=>array(self::BELONGS_TO, 'ProdCategory', 'id'),
'product'=>array(self::MANY_MANY, 'Products', 'tbl_products_category(subcategory_id, product_id)'),
);
ProdCategory:
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?
Thank you!