How to control sql join order in Yii CDbCriteria "with"

Question

I have the following criteria for a findAll statement

$with=array(
    'tumor'=>array(
    'select'=>false,
    'joinType'=>'INNER JOIN',
    ),
    'tumorLibraryType'=>array(
    'select'=>false,
    'joinType'=>'INNER JOIN',
    'condition'=>'tumorLibraryType.id = 1 OR tumorLibraryType.id = 6',
    ),  
    'tumorPatient'=>array(
    'select'=>false,
    'joinType'=>'INNER JOIN',
    )
);

$libPairs=LibraryPairs::model()->with($with)->findAll();

These are the relevant model relations:

    'tumor' => array(self::BELONGS_TO, 'Libraries', array('tumor_library'=>'id')),
    'normal' => array(self::BELONGS_TO, 'Libraries', array('normal_library'=>'id')),        
    // making separate AR routes for tumor and normal. only tumor used currently
    'tumorLibraryType'=>array(self::HAS_ONE,'LibraryTypes','','on'=>'tumor.library_type_id = tumorLibraryType.id'),
    'tumorLibrariesIsolates'=>array(self::HAS_MANY,'LibrariesIsolates',array('id'=>'library_id'),'through'=>'tumor'),
    'tumorSamplesIsolates'=>array(self::HAS_MANY,'SamplesIsolates',array('isolate_id'=>'isolate_id'),'through'=>'tumorLibrariesIsolates'),
    'tumorSamples'=>array(self::HAS_MANY,'Samples',array('sample_id'=>'id'),'through'=>'tumorSamplesIsolates'),
    'tumorPatient'=>array(self::HAS_ONE,'Patients',array('patient_id'=>'id'),'through'=>'tumorSamples'),

This code generates the following sql:

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)

But that sql throws an error:

"Column not found: 1054 Unknown column 'tumor.library_type_id' in 'on clause'. "

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.

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)

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.

I ended up working around this problem another way, but would still like to know how to control join order using "with" if anyone knows how. — 111, Jul 11 '13 at 14:18

Stan · Accepted Answer · 2013-09-24T18:12:13.997

I encountered a similar problem: Yii generates joins in such order that makes SQL statement invalid. This situation occurs, for example, when you try to write custom $CDBCriteria->join which relies on tables specified in relations passed by $CDBCriteria->with. This happens because join is processed in CJoinQuery::__constructor whereas all "standard" joins (from with) are generated by Yii in CJoinQuery::join, that is after the constructor.

Unfortunately there is no solution other than a patch. Here is an example of how I did it my copy of Yii (the code is provided "as is" - please, check if it's applicable for your case).

First, we need to add a field into CDbCriteria, which will switch on/off a new option.

CDbCriteria.php

class CDbCriteria extends CComponent
{
  ...

  /**
   * @var string how to join with other tables. This refers to the JOIN clause in an SQL statement.
   * For example, <code>'LEFT JOIN users ON users.id=authorID'</code>.
   */
  public $join='';

  /**
   * Patch begins: 
   */
  public $joinreorder = false; // new option
  ...

Second, we need to extend CJoinQuery (please, note, it's in CActiveFinder.php):

CActiveFinder.php

class CJoinQuery
{
  ...

  /**
   * @var array list of join element IDs (id=>true)
   */
  public $elements=array();

  /**
   * Patch begins: 
   */
  private $joinreorder = false; // the same new option
  private $postjoins; // the variable to store our custom joins
  ...

Now we can alter the CJoinQuery constructor:

CActiveFinder.php (continuation)

public function __construct($joinElement,$criteria=null)
{
  if($criteria!==null)
  {
    $this->joinreorder = $criteria->joinreorder;
    $this->selects[]=$joinElement->getColumnSelect($criteria->select);
    $this->joins[]=$joinElement->getTableNameWithAlias();
    if($this->joinreorder)                 //
    {                                      //
      $this->postjoins=$criteria->join;    // new lines
    }                                      //
    else                                   //
    {                                      //
      $this->joins[]=$criteria->join;
    }                                      //
    $this->conditions[]=$criteria->condition;
    $this->orders[]=$criteria->order;

If joinreorder is true we store custom joins in our new member variable postjoins. Otherwise, all should work as before.

And now the actual fix in CJoinQuery::createCommand:

CActiveFinder.php (continuation)

public function createCommand($builder)
{
  $sql=($this->distinct ? 'SELECT DISTINCT ':'SELECT ') . implode(', ',$this->selects);
  $sql.=' FROM ' . implode(' ',$this->joins);
  if($this->joinreorder)      //
  {                           //
    $sql .= $this->postjoins; // new lines
  }                           //
  ...

Finally we add the custom joins into SQL statement, and they are appended (not prepended as in standard implementation) to other joins generated from Yii's relations.

Now we can use it like so:

$criteria = new CDbCriteria;
$criteria->joinreorder = true;
$criteria->with = array('product', 'shop');
$criteria->join = 'LEFT OUTER JOIN `shop2address` `s2a` ON (`shop`.`id` =  `s2a`.`shop_id`)';

Without joinreorder = true this generates the error stating that shop.id is unknown column in ON clause, bacause the 'shop' table is not yet added into SQL-statement. With joinreorder = true it works as expected.

As for the cases when only with is used, and incorrect sequence of joins is generated, one should apply more complicated patch. It involves CJoinQuery::join method. It should, possibly, have an optional parameter $priority, which can be again populated via corresponding member added into CDbCriteria. Then in CJoinQuery::join change these lines:

$this->joins[$element->priority]=$element->getJoinCondition();
$this->joins[$element->priority]=$element->relation->join;

This allows for re-ordering joins in arbitrary manner according to specified priorities.

That's great. I haven't tested your solution but have you posted this in the Yii forum? It might get incorporated into a future release. — 111, Sep 24 '13 at 17:50
@glyph Probably I'll post a solution when it will be complete meaning reordering within `with`s. — Stan, Sep 24 '13 at 18:07

score 0 · Answer 2 · answered Jul 09 '13 at 20:24

0

This line doesn't look correct:

'tumorLibraryType'=>array(self::HAS_ONE,'LibraryTypes','','on'=>'tumor.library_type_id = tumorLibraryType.id'),

Maybe it should be

'tumorLibraryType'=>array(self::HAS_ONE,'LibraryTypes',array('id'=>'library_type_id'),'through'=>'tumor'),

answered Jul 09 '13 at 20:24

jborch

1,146
6
15

That's my workaround for the "not unique table alias" error when multiple relations are going "through" the same relation. – 111 Jul 11 '13 at 14:15
Ok. But you can specify the alias: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-options – jborch Jul 18 '13 at 09:25
That doesn't solve the not unique table alias problem because it creates an additional join with the same alias, or requires redundant joins and 'through' relations which slows things down. This is a documented issue and the above solution is the most efficient. – 111 Jul 18 '13 at 17:25

score 0 · Answer 3 · answered Oct 03 '17 at 21:29

guys, I believe I'm late to the party

I had similar problem

I've criteria with merges:

    $criteria = new CDbCriteria();

    $criteria->with = [
       'codebaseName' => [
          'alias' => 'cn'
       ],
       'codebaseProducer' => [
          'alias' => 'cp'
       ],
       'registrationDocumentLast' => [
          'alias' =>'rdl'
       ]

    ];

It ended up in such order by statement:

ORDER BY COALESCE(cn.name_our,cn.name_supplier), id DESC LIMIT 50

I didn't specify ordering by id DESC explicitly!

After playing for around, I discovered that it came from relation registrationDocumentLast , which was defined as

      'registrationDocumentLast' => [
          self::HAS_ONE, RegistrationDocument::class, 'codebase_product_pharm_id',
          'joinType' => 'LEFT JOIN',
          'order' => 'id DESC'
       ],

Look at order key. Changing it from

          'order' => 'id DESC'

to

          'order' => 't.id DESC'

solved the problem

How to control sql join order in Yii CDbCriteria "with"

3 Answers3