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

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 开发者_JAVA百科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?

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

To those who has similar problems but in relations a-la $belongsTo, to have correct order you should set it correctly.

For example when you have such code:

var $belongsTo = array(
    'Vacancy',
    'Applicant' => array(
        'className' => 'Person',
    ),
    'Recruiter' => array(
        'className' => 'Person',
    ),
    'Company' => array(
        'conditions' => array('Company.id = Vacancy.company_id'),
    ),
);

But in the result always receive results where Vacancy always joins last, you should do juts simple thing: add those "Vacancy" model not as array value, but as a key=>value, as others:

var $belongsTo = array(
    'Vacancy' => array(), // Just add empty array here -- all magic is here :)
    'Applicant' => array(
        'className' => 'Person',
    ),
    'Recruiter' => array(
        'className' => 'Person',
    ),
    'Company' => array(
        'conditions' => array('Company.id = Vacancy.company_id'),
    ),
);

Now all will be in straight order: Vacancy, Applicant, Recruiter & only then Company.

Have you thought about creating a HABTM model and inserting your own 'finderQuery' to override the model query? http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#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.

public function selectdata(){
   $option= $this->Group->find('all', 
                    array(
                        'joins' =>
                            array(
                                array(
                                    'table'=> 'user_groups',
                                    'alias'=>'g',
                                    'type'=> 'INNER',
                                    'conditions'=> array('g.group_id =Group.id ')
                                 ),
                                array(
                                    'table'=> 'users',
                                    'alias'=>'u',
                                    'type'=> 'INNER',
                                    'conditions'=> array('u.id = g.user_id')
                                ),
                     ),'fields'=>array('Group.name,Group.created,Group.modified,Group.status,Group.created_by,u.first_name,u.last_name'),
                       'conditions'=>array('g.group_id=Group.created_by or g.user_id=u.id')
//                        ."'".$created_by."'"
                        )
               ); 
             var_dump($option);//die(); 
               if(isset($this->params['requested']))
               {
                       return $option;
               }
               $this->set('groups', $option);

    }