CakePHP retrieve data ignoring 'hasMany'

Question

I have 2 tables - Box and Apple. Apple belongs to one box, and one box has many apples. In the code in looks like this:

class Apple extends AppModel {
    public $useDbConfig = 'somedb';
    public $useTable = 'apple';
    public $belongsTo = array(
        'Box' => array(
            'className' => 'Box',
            'foreignKey' => 'box_id'
        )
    );
} 
class Box extends AppModel {
    public $useDbConfig = 'somedb';
    public $useTable = 'box';
    public $hasMany = array(
        'Apple' => array(
            'className' => 'Apple',
            'foreignKey' => 'box_id',
            'dependent' => true
        )
    );
}

When I request for boxes: $this->Box->find("all") I receive a list of all boxes + list of all apples for each box:

(Array):
    [0] => (Array):
        [Box] => (Array):
            // some data
        [Apple] => (Array):
            [0] => (Array):
                // some data
            [1] => (Array):
                // some data
            ...
    [1] => (Array):
        [Box] => (Array):
            // some data
        [Apple] => (Array):
            [0] => (Array):
                // some data
            [1] => (Array):
                // some data
    ...

So, how can I receive only array of boxes, without apples?

UPDATE: As @Ben answered, I need to put recursive = -1 (or 0).

But now, I have a continuation of this question: If I have one more model - Room, room can contain boxes, so there should be $hasMany option in Room for Box, and $belongsTo option for Room in Box:

 class Room extends AppModel {
    public $useDbConfig = 'somedb';
    public $useTable = 'room';
    public $hasMany = array(
        'Box' => array(
            'className' => 'Box',
            'foreignKey' => 'room_id'
        )
    );
}
// Edited Box class
class Box extends AppModel {
    public $useDbConfig = 'somedb';
    public $useTable = 'box';
    public $hasMany = array(
        'Apple' => array(
            'className' => 'Apple',
            'foreignKey' => 'box_id',
            'dependent' => true
        )
    );
    public $belongsTo = array(
        'Room' => array(
            'className' => 'Room',
            'foreignKey' => 'room_id'
        )
    );
}

So, how can I get data about Apples, Boxes and Rooms together? Let's say, that I have a condition for Room: where room.name like "room0%". I tried next thing:

    $some = $this->Apple->find("all", array(
        'joins' => array(
            array('table' => 'room',
                'alias' => 'Room',
                'type' => 'INNER',
                'conditions' => array(
                    'Room.id = Box.room_id',
                )
            )
        ),
        'conditions' => array('Room.name LIKE' => 'room0%')
    ));

But I only receive an array of Apple-Box "pairs".

UPDATE: The only solution currently I found is next:

    $temp = "room0%";
    $db = $this->Apple->getDataSource();
    $some = $db->fetchAll(
        "SELECT * 
          FROM  apple 
          JOIN box on apple.box_id = box.id
          JOIN room on box.room_id = room.id
          WHERE room.name LIKE '$temp'"
    );

Is there a way to make the same using models?

Answer 1

For complex association queries, it is advisable to use Containable. With it, you can go deeper than with just recursive = -1 in the model/find call, without doing the manual joins by hand.

After setting up Containable, your query could be something like*

$this->Apple->find('all', array(
    'contain' => array(
        'Box' => array(
            'Room' => array(
                'conditions' => array('Room.name LIKE' => 'room0%')
            )
        )
    )
));

*) just my mockup from memory, not guaranteed to work as-is

Answer 2

In cakephp 2 the contains are joined automatically set recursive => -1 in your find call.