How to change the sequence of 'joins' in CakePHP?

Question

I have the problem with the sequence of joins. The similar problem was in another question http://stackoverflow.com/questions/3343348/manipulating-order-of-joins-in-cakephp. The answer was to use Containable behavior. In my case that is unacceptable because I have deeper associations and containable generates too many queries. Containable does not generate joins for the three level associations. It generates additional queries for every entry from the second level table.

My query is:

$this->LevelOne->find('all', array(
    'joins' => array(array(
         'table' => 'level_three',
         'alias' => 'LevelThree',
         'type' => 'LEFT',
         'conditions' => array(
              'LevelThree.id = LevelTwo.level_three_field_id'
          )
     ))
));

The problem here is that cake generates several joins but the join of the LevelThree table is done before the joins of the LevelTwo tables and that throws an SQL error "Unknown column 'LevelTwo.level_three_field_id' in 'on clause'". If the LevelThree join would be at the end of the query after all LevelTwo joins the query would be okay.

So, the question is how to change the sequence of joins?

Answer 1

Have you thought about creating a HABTM model and inserting your own 'finderQuery' to override the model query? http://book.cakephp.org/view/1044/hasAndBelongsToMany-HABTM

Sometimes with complex queries, it make more sense to create the custom query so cake doesn't have to deal with it. To do that, you can just create the function in the model and then call it like you would any other query.

function customJoin(){
    return $this->query('CUSTOM QUERY HERE');
}

Then call it from the controller:

$this->ModelName->customJoin();

I think sometimes we rely too heavily on the automation that a framework provides and forget that WE are in control and can implement code outside of the core. I do it all the time. Hope this helps.

Answer 2

Finally I figured out how to do that:

$this->LevelOne->unbindModel(array('belongsTo' => array('LevelTwo')));
$this->LevelOne->find('all', array(
    'joins' => array(
          array(
             'table' => 'level_two',
             'alias' => 'LevelTwo',
             'type' => 'LEFT',
             'conditions' => array(
                  'LevelTwo.id = LevelOne.level_two_field_id'
              )
          ),
          array(
             'table' => 'level_three',
             'alias' => 'LevelThree',
             'type' => 'LEFT',
             'conditions' => array(
                  'LevelThree.id = LevelTwo.level_three_field_id'
              )
          )
     )
));