Multiple Level Relation Findall()

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?