CDbCriteria join order


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


		'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(




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(


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

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




return new CActiveDataProvider($this, array(




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