CakePHP Multiple Nested Joins
- by Paul
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
