CDbCriteria join order

Yii 1.1 General Discussion
1

Hi,

I have the following complex sql request to select family with member ‘legal’ & member ‘partner’ through a table inter with role (legal or partner):


SELECT DISTINCT `t`.* FROM `family` `t` 

CROSS JOIN `inter` `interlegal` ON (`interlegal`.`family_id`=`t`.`id`) LEFT JOIN `membre` `legal` ON (`interlegal`.`membre_id`=`legal`.`id` AND `interlegal`.`role_id` = 1) 

CROSS JOIN `inter` `interpartner` ON (`interpartner`.`family_id`=`t`.`id`) LEFT JOIN `membre` `partner` ON (`interpartner`.`membre_id`=`partner`.`id` AND `interpartner`.`role_id` = 2)

This sql request is ok and its translation in Yii Family model is:


public function relations()

{

	return array(

		'Inter'=>array(self::HAS_MANY, 'Inter', 'family_id'),

		'interlegal'=>array(self::HAS_MANY, 'Inter', 'family_id', 'alias'=>'interlegal'),

		'interpartner'=>array(self::HAS_MANY, 'Inter', 'family_id', 'alias'=>'interpartner'),

		'Membre'=>array(self::HAS_MANY,'Membre','membre_id','through'=>'Inter'),

		'Role'=>array(self::HAS_MANY,'Role','role_id','through'=>'Inter', 'alias'=>'Role'),

		'legal'=>array(self::HAS_ONE,'Membre','membre_id','through'=>'interlegal', 'condition'=>'interlegal.role_id = 1'),

		'partner'=>array(self::HAS_ONE,'Membre','membre_id','through'=>'interpartner', 'condition'=>'interpartner.role_id = 2'),

	);

}



public function search()

{

	$criteria=new CDbCriteria();

	$criteria->join = "CROSS JOIN `inter` `interlegal` ON (`interlegal`.`family_id`=`t`.`id`) LEFT JOIN `membre` `legal` ON (`interlegal`.`membre_id`=`legal`.`id` AND `interlegal`.`role_id` = 1)";

	$criteria->join .= " CROSS JOIN `inter` `interpartner` ON (`interpartner`.`family_id`=`t`.`id`) LEFT JOIN `membre` `partner` ON (`interpartner`.`membre_id`=`partner`.`id` AND `interpartner`.`role_id` = 2)";

	$criteria->distinct = true;

	return new CActiveDataProvider($this, array(

		'criteria'=>$criteria,

	));

}

Now, I want to order this by legal member name, so I add the following lines in Family model:


public $legalName;

public function afterFind() {

	$this->legalName = $this->legal ? $this->legal->name." ".$this->legal->surname : ""; 

}

in search() function:


$sort = new CSort();

$sort->attributes = array(

	'legalName'=>array(

			'asc'=>'legal.name, legal.surname',

			'desc'=>'legal.name desc, legal.surname desc',

	),

	'*',

);

return new CActiveDataProvider($this, array(

	'criteria'=>$criteria,

	'sort'=>$sort,

));

80% of results are ok but the other 20% seem randomly order… I don’t understand the rule to order them :frowning:

Does someone see an error in this code???

Cheers