How to get rid of null associated model in cakephp 2.x

Question

I am trying to get the item types that Order.Item.ItemType.show_type = 1. I have written the query but I want to show only Items that their ItemType.show_type = 1 not all items.

$brief = $this->Order->find('first', array(
            'fields' => array(
                'Order.*'
            ),
            'conditions' => array(
                'Order.order_id' => $orderId,
            ),
            'contain' => array(                 
                'Item' => array(
                    'fields' => array(
                        'Item.*', 'CHAR(64 + Item.num) AS letter'
                    ),
                    'conditions' => array(
                        'Item.deleted' => 0,
                    ),
                    'ItemType' => array(
                        'conditions' => array(
                            'ItemType.show_type' => 1
                        ),
                    )
                ),
            )
        ));

The query shouldn't show Item id = 25741

Associations:

// Order
public $hasMany = array(
    'BriefInstalment' => array(
        'foreignKey' => 'order_id'
    )
);

// Item Model
public $belongsTo = array(
    'Order',
    'ItemType' => array(
        'type' => 'inner'
    )
);

// ItemType Model
public $hasMany = array('Item');

Print:

array(
    'Order' => array(
        'order_id' => '67817',
        'service' => '',
    ),
    'Item' => array(
        (int) 0 => array(
            'id' => '25741',
            'order_id' => '67817',
            'num' => '2',
            'item_type_id' => '8',
            'name' => '3-5 titles active',
            'deleted' => false,
            'ItemType' => array(),  // <= how to remove this empty model
            'Item' => array(
                (int) 0 => array(
                    'letter' => 'B'
                )
            )
        ),
        (int) 1 => array(
            'id' => '25742',
            'order_id' => '67817',
            'num' => '3',
            'item_type_id' => '2',
            'name' => '1,000 pro active',
            'deleted' => false,
            'ItemType' => array(
                'id' => '2',
                'name' => 'Part Instalment',
                'show_type' => true,
                'deleted' => false
            ),
            'Item' => array(
                (int) 0 => array(
                    'letter' => 'C'
                )
            )
        )
    )
)

Answer 1

This could not be done using Countaible behaviour, but iwth the joins method, set the recursive to -1

$brief = $this->Order->find('first', array(
        'recursive' => -1,
        'fields' => array(
            'Order.*'
        ),
        'conditions' => array(
            'Order.order_id' => $orderId,
        ),
        'joins' => array(                 
            array(
                'table' => 'items',
                'alias' => 'Item',
                'type' => 'inner',
                'conditions' => array(
                    'Order.id = Item.order_id'
                )
            ),            
            array(
                'table' => 'item_types',
                'alias' => 'ItemType',
                'type' => 'inner',
                'conditions' => array(
                    'ItemType.id = Item.item_type_id'
                )
            ),
        )
    ));

You have to check if the table names are correct and also the foreign keys names.

Another solution would be to go through your results, and unset the empty ones

foreach($brief as $k => $v){
    foreach($v['Item'] as $kk => $vv){
        if(empty($vv['ItemType'])){
             unset($brief[$k]['Item'][$kk];
        }
    }
}
debug($brief);