Hi
I have 3 models
Product
(PK) Id
MenuCategoryItem
ProductId
CategoryId
MenuCategory
(PK)CategoryId
ParentId
Each model has relations according to the db relation
Product:
‘catItems’ => array(self::HAS_MANY, ‘MenuCategoryItem’, ‘Id’)
CategoryItem:
‘category’ => array(self::BELONGS_TO, ‘MenuCategory’, ‘CategoryId’)
I’m trying to find all products belonging to categories which has an given parent. I’m trying to do this thrue model relations instead of sql statement because I need some other relations as well.
This is my code
$products = Product::model()->with(array(
'media' => array('scopes'=>array('main')),
'text',
'catItems' => array(
'select' => false,
'joinType' => 'INNER JOIN',
'category' => array(
'select' => false,
'joinType' => 'INNER JOIN',
'condition' => 'category.ParentId=:parent',
'params' => array('parent' => $this->category->Id),
),
),
))->findAll();
This returns (for some reason) all products, and completely ignores the parent parameter.
Now if I run the following sql statement
$stmt = "select invproduct.* from invproduct inner join invmenucategoryitem on invproduct.Id = invmenucategoryitem.ProductId
left join invmenucategory on invmenucategory.Id = invmenucategoryitem.CategoryId
where invmenucategory.ParentId = :parent";
I get the products I want
How can I get the same result as the $stmt using the first approach?