Querying data based on 3rd level relationship in CakePHP

Question

I have the following relationships set up:

A HABTM B
B belongsTo C
C hasMany B

Now, for a given A, I need all C with the B's attached. I can write the SQL queries, but what's the proper CakePHP way? What method do I call on which model, and with which parameters?

Answer 1

$this->A->find(
   'first', 
   array('conditions'=>array('id'=>'someword'), 
   'recursive'=>2)
);

like this?

Answer 2

this might work

$this->C->find('all',
array('conditions'=>array('id'=>'someword','B.aid'=>$a.id)));

Answer 3

I'd go with Aziz' answer and simply process the data as it comes in. If you need C to be your primary model though, you'll have to do a little workaround. Cake is not terrifically good with conditions on related models yet, especially on removed 3rd cousins kind of queries. It usually only does actual JOIN queries on belongsTo or hasMany relations; not on HABTM relations though, those it gets in separate queries. That means you can't include conditions on related HABTM models.

Your best bet then might be something like this:

// get related records as usual with the condition on A, limit to as little data as necessary
$ids = $this->A->find('first', array(
    'conditions' => array('A.id' => 'something'), 
    'recursive'  => 2,
    'fields'     => array('A.id'),
    'contain'    => array('B.id', 'B.c_id', 'B.C.id') // not quite sure if B.C.id works, maybe go with B.C instead
));

// find Cs, using the ids we got before as the condition
$Cs = $this->C->find('all', array(
    'conditions' => array('C.id' => Set::extract('/B/C/id', $ids)),
    'recursive   => 1
);

Note that this produces quite a bunch of queries, so it's not really an optimal solution. Writing your own SQL might actually be the cleanest way.

EDIT:

Alternatively, you could re-bind your associations on the fly to make them hasMany/belongsTo relationships, most likely using the join table/model of A and B. That might enable you to use conditions on related models more easily, but it's still tricky to fetch Cs when the condition is on A.

Answer 4

I'd think of "Containable" behaviour (http://book.cakephp.org/view/474/Containable)... gives a lot control on finding related data.