Cakephp: Contain Behavior, conditions on array of array

Question

I have a problem with contain behavior conditions and empty array.

There is part of code:

   $menusTopUser = $this->Menu->find('all', array(
            'contain' => array('TemplateRegion', 'Language', 'Item', 'MenusUserGroup' => array('conditions' => array('MenusUserGroup.user_group_id ' => $this->Auth->user('UserGroup.id')))
            ),
            'conditions' => array(
                'TemplateRegion.alias' => 'menu_top_user',
                'Menu.active' => 1,
                'Language.ISO-639-2' => $this->Session->read('Config.frontendLanguage'),
            ),
            'fields' => array('Menu.title', 'Item.slug', 'Menu.url', 'Menu.target', 'Language.ISO-639-2'),
            'order' => array('Menu.lft'),
            'recursive' => 0));
        $this->set(compact('menusTopUser'));

My associations look like:

Menu
hasMany: MenusUserGroup

MenusUserGroup
belongsTo: Menu, UserGroup

After executing the find, I have the next array where there are many MenusUserGroup empty. How make to filter only the record with 'MenusUserGroup.user_group_id ' => $this->Auth->user('UserGroup.id')?

Array(
[0] => Array
    (
        [Menu] => Array
            (
                [title] => Offerte e modulistica promoter
                [url] => 
                [target] => _self
                [id] => 11
            )

        [Item] => Array
            (
                [slug] => offerte-e-modulistica-promoter
                [id] => 36
            )

        [Language] => Array
            (
                [ISO-639-2] => ita
                [id] => 1
            )

        [TemplateRegion] => Array
            (
                [id] => 10
            )

        [MenusUserGroup] => Array
            (
            )

    )

[1] => Array
    (
        [Menu] => Array
            (
                [title] => Offerte e modulistica agenti
                [url] => 
                [target] => _self
                [id] => 10
            )

        [Item] => Array
            (
                [slug] => offerte-modulistica
                [id] => 29
            )

        [Language] => Array
            (
                [ISO-639-2] => ita
                [id] => 1
            )

        [TemplateRegion] => Array
            (
                [id] => 10
            )

        [MenusUserGroup] => Array
            (
                [0] => Array
                    (
                        [id] => 7
                        [menu_id] => 10
                        [user_group_id] => 5
                    )

            )

    )

[2] => Array
    (
        [Menu] => Array
            (
                [title] => Gestione contratti
                [url] => 
                [target] => _self
                [id] => 13
            )

        [Item] => Array
            (
                [slug] => 
                [id] => 
            )

        [Language] => Array
            (
                [ISO-639-2] => ita
                [id] => 1
            )

        [TemplateRegion] => Array
            (
                [id] => 10
            )

        [MenusUserGroup] => Array
            (
                [0] => Array
                    (
                        [id] => 10
                        [menu_id] => 13
                        [user_group_id] => 5
                    )

            )

    )

Answer 1

Use a join.

 $menusTopUser = $this->Menu->find('all', array(
        // join statement
        'joins' => array(
            array(
                'table' => 'menus_user_groups',
                'alias' => 'MenuUserGroupJoin',
                'type' => 'INNER',
                'conditions' => array(
                    'MenuUserGroupJoin.menu_id = Menu.id',
                    // condition
                    'MenuUserGroupJoin.user_group_id = ' . $this->Auth->user('UserGroup.id'),
                ),
            ),
        ),
        'contain' => array('TemplateRegion', 'Language', 'Item', 'MenusUserGroup'),
        'conditions' => array(
            'TemplateRegion.alias' => 'menu_top_user',
            'Menu.active' => 1,
            'Language.ISO-639-2' => $this->Session->read('Config.frontendLanguage'),
        ),
        'fields' => array('Menu.title', 'Item.slug', 'Menu.url', 'Menu.target', 'Language.ISO-639-2'),
        'order' => array('Menu.lft'),
        'recursive' => 0));