php - 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 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

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'
              )
          )
     )
));