title: How to control sql join order in Yii CDbCriteria "with"
body: <p>I have the following criteria for a findAll statement</p>
  
  <pre><code>$with=array(
      'tumor'=&gt;array(
      'select'=&gt;false,
      'joinType'=&gt;'INNER JOIN',
      ),
      'tumorLibraryType'=&gt;array(
      'select'=&gt;false,
      'joinType'=&gt;'INNER JOIN',
      'condition'=&gt;'tumorLibraryType.id = 1 OR tumorLibraryType.id = 6',
      ),  
      'tumorPatient'=&gt;array(
      'select'=&gt;false,
      'joinType'=&gt;'INNER JOIN',
      )
  );
  
  $libPairs=LibraryPairs::model()-&gt;with($with)-&gt;findAll();
  </code></pre>
  
  <p>These are the relevant model relations:</p>
  
  <pre><code>    'tumor' =&gt; array(self::BELONGS_TO, 'Libraries', array('tumor_library'=&gt;'id')),
      'normal' =&gt; array(self::BELONGS_TO, 'Libraries', array('normal_library'=&gt;'id')),        
      // making separate AR routes for tumor and normal. only tumor used currently
      'tumorLibraryType'=&gt;array(self::HAS_ONE,'LibraryTypes','','on'=&gt;'tumor.library_type_id = tumorLibraryType.id'),
      'tumorLibrariesIsolates'=&gt;array(self::HAS_MANY,'LibrariesIsolates',array('id'=&gt;'library_id'),'through'=&gt;'tumor'),
      'tumorSamplesIsolates'=&gt;array(self::HAS_MANY,'SamplesIsolates',array('isolate_id'=&gt;'isolate_id'),'through'=&gt;'tumorLibrariesIsolates'),
      'tumorSamples'=&gt;array(self::HAS_MANY,'Samples',array('sample_id'=&gt;'id'),'through'=&gt;'tumorSamplesIsolates'),
      'tumorPatient'=&gt;array(self::HAS_ONE,'Patients',array('patient_id'=&gt;'id'),'through'=&gt;'tumorSamples'),
  </code></pre>
  
  <p>This code generates the following sql:</p>
  
  <pre><code>SELECT `t`.`tumor_library` AS `t0_c0`, `t`.`normal_library` AS `t0_c1`, `t`.`created` AS `t0_c2`, `t`.`created_by` AS `t0_c3`, `t`.`last_modified` AS `t0_c4`, `t`.`last_modified_by` AS `t0_c5`, `tumor`.`library_type_id` AS `t1_c2`, `tumor`.`id` AS `t1_c0` 
  FROM `library_tumor_normal_pairs` `t` 
  INNER JOIN `library_types` `tumorLibraryType` ON (tumor.library_type_id = tumorLibraryType.id) 
  INNER JOIN `libraries` `tumor` ON (`t`.`tumor_library`=`tumor`.`id`) 
  LEFT OUTER JOIN `libraries_isolates` `tumorLibrariesIsolates` ON (`tumor`.`id`=`tumorLibrariesIsolates`.`library_id`) 
  LEFT OUTER JOIN `samples_isolates` `tumorSamplesIsolates` ON (`tumorLibrariesIsolates`.`isolate_id`=`tumorSamplesIsolates`.`isolate_id`) 
  LEFT OUTER JOIN `samples` `tumorSamples` ON (`tumorSamplesIsolates`.`sample_id`=`tumorSamples`.`id`) 
  INNER JOIN `patients` `tumorPatient` ON (`tumorSamples`.`patient_id`=`tumorPatient`.`id`) 
  WHERE (tumorLibraryType.id = 1 OR tumorLibraryType.id = 6) 
  </code></pre>
  
  <p>But that sql throws an error:</p>
  
  <p><strong>"Column not found: 1054 Unknown column 'tumor.library_type_id' in 'on clause'. "</strong></p>
  
  <p>However if I simply move the tumor line in the sql query up to be the first join statement, and run the query manually, then the query works.</p>
  
  <pre><code>SELECT `t`.`tumor_library` AS `t0_c0`, `t`.`normal_library` AS `t0_c1`, `t`.`created` AS `t0_c2`, `t`.`created_by` AS `t0_c3`, `t`.`last_modified` AS `t0_c4`, `t`.`last_modified_by` AS `t0_c5`, `tumor`.`library_type_id` AS `t1_c2`, `tumor`.`id` AS `t1_c0` 
  FROM `library_tumor_normal_pairs` `t` 
  INNER JOIN `libraries` `tumor` ON (`t`.`tumor_library`=`tumor`.`id`) 
  INNER JOIN `library_types` `tumorLibraryType` ON (tumor.library_type_id = tumorLibraryType.id) 
  LEFT OUTER JOIN `libraries_isolates` `tumorLibrariesIsolates` ON (`tumor`.`id`=`tumorLibrariesIsolates`.`library_id`) 
  LEFT OUTER JOIN `samples_isolates` `tumorSamplesIsolates` ON (`tumorLibrariesIsolates`.`isolate_id`=`tumorSamplesIsolates`.`isolate_id`) 
  LEFT OUTER JOIN `samples` `tumorSamples` ON (`tumorSamplesIsolates`.`sample_id`=`tumorSamples`.`id`) 
  INNER JOIN `patients` `tumorPatient` ON (`tumorSamples`.`patient_id`=`tumorPatient`.`id`) 
  WHERE (tumorLibraryType.id = 1 OR tumorLibraryType.id = 6) 
  </code></pre>
  
  <p>So my question is, how can I control the sql join order of "with" criteria in Yii?  Is it possible? As you can see my 'with' array and relations have 'tumor' before the others, but the join order is not preserved.</p>