CakePHP Multiple Nested Joins

Question

I have an App in which several of the models are linked by hasMany/belongsTo associations. So for instance, A hasMany B, B hasMany C, C hasMany D, and D hasMany E. Also, E belongs to D, D belongs to C, C belongs to B, and B belongs to A. Using the Containable behavior has been great for controlling the amount of information comes back with each query, but I seem to be having a problem when trying to get data from table A while using a condition that involves table D. For instance, here is an example of my 'A' model:

class A extends AppModel {
    var $name = 'A';

    var $hasMany = array(
        'B' => array('dependent' => true)
    );

    function findDependentOnE($condition) {
        return $this->find('all', array(
            'contain' => array(
                'B' => array(
                    'C' => array(
                        'D' => array(
                            'E' => array(
                                'conditions' => array(
                                    'E.myfield' => $some_value
                                )
                            )
                        )
                    )
                )
            )
        ));
    }
}

This still gives me back all the records in 'A', and if it's related 'E' records don't satisfy the condition, then I just get this:

Array(
    [0] => array(
        [A] => array(
            [field1] => // stuff
            [field2] => // more stuff
            // ...etc
        ),
        [B] => array(
            [field1] => // stuff
            [field2] => // more stuff
            // ...etc
        ),
        [C] => array(
            [field1] => // stuff
            [field2] => // more stuff
            // ...etc
        ),
        [D] => array(
            [field1] => // stuff
            [field2] => // more stuff
            // ...etc
        ),
        [E] => array( 
            // empty if 'E.myfield' != $some_value'
        )
    ),
    [1] => array( // ...etc )
)

When If 'E.myfield' != $some_value, I don't want the record returned at all.

I hope this expresses my problem clearly enough...

Basically, I want the following query, but in a database-agnostic/CakePHP-y kind of way:

SELECT * 
FROM A INNER JOIN
        (B INNER JOIN 
            (C INNER JOIN 
                (D INNER JOIN 
                    E ON D.id=E.d_id) 
                ON C.id=D.c_id) 
            ON B.id=C.b_id) 
        ON A.id=B.a_id 
    WHERE E.myfield = $some_value

Answer 1

Your issue is a misconception of what the Containable behaviour does and what the contain option does in Model::find. The Model::find call in your first code-sample would translate roughly to:

Find all A; then find all B associated with each A; then find all C associated with each B; then find all D associated with each C; finally, find all E associated with each D where one field in E matches a specified value.

The condition statement only filters results of D, not up the chain to C, then B, then A. If you scan the SQL log, you'll see an enormous number of queries pulling out each level of your contain chain.

In order to get CakePHP to return the results as you desire, straight from the database, you'd have to configure a hasOne association between A and E. With a long chain such as you describe, this might be fairly unwieldy. It'd look something like (read: untested):

$this->bindModel(array('hasOne'=>array(
    'B'=>array(
        'foreignKey' => false,
        'conditions' => array('A.id = B.a_id')
    ),
    'C'=>array(
        'foreignKey' => false,
        'conditions' => array('B.id = C.b_id')
    ),
    'D'=>array(
        'foreignKey' => false,
        'conditions' => array('C.id = D.c_id')
    ),
    'E'=>array(
        'foreignKey' => false,
        'conditions' => array('D.id = E.d_id')
    )
)));

$this->find('all', array(
    'conditions' => array( 'E.my_field' => $some_value )
));

An alternative is to remove the E.my_value condition entirely from the Model::find call, and instead perform a fairly complex Set::extract at the end:

$results = $this->find('all', array(
    'contain' => array(
        'B' => array(
            'C' => array(
                'D' => array(
                    'E' => array()
                )
            )
        )
    )
));
return Set::extract("/A/B/C/D/E[my_field={$some_value}]/../../../../", $results);

Performance would be a real issue with a deep Set::extract though, especially if you were operating on a lot of rows.

EDIT: I just want to emphasise how terrible an idea the Set::extract option is if this operation needs to scale. It shifts the entire filtering burden from the database engine onto PHP's array functions.