Cdbcriteria Multi Relation Between Two Table

Yii 1.1 General Discussion
1

Hi

this is my models :




class Entry extends CActiveRecord

{

public function relations()

	{

		return array(

			'user' => array(self::BELONGS_TO, 'Users', 'user_id'),

			'transfree0' => array(self::BELONGS_TO, 'Users', 'transfree' ),

			'entryItems' => array(self::HAS_MANY, 'EntryItem', 'entry_id'),

		);

	}

}







class Users extends CActiveRecord

{

public function relations()

	{

		return array(

			'entries' => array(self::HAS_MANY, 'Entry', 'user_id'),

			'entries1' => array(self::HAS_MANY, 'Entry', 'transfree'),

			'profiles' => array(self::HAS_ONE, 'Profiles', 'user_id'),

		);

	}

}







class Profiles extends CActiveRecord

{

public function relations()

	{

		return array(

			'user' => array(self::BELONGS_TO, 'Users', 'user_id'),

		);

	}


}

and this is my controler cod :




$criteria=new CDbCriteria;

				

	$criteria->with=array('entryItems'=> array('with'=>array('product'))

			, 'user'=> array('with'=>array('profiles' ) )

			,'transfree0'=>array('with'=>array( array('profiles'   , 'alias'=>'pp'))  )

								);

	$criteria->compare('user.profiles.name',$model->username,true);

	$criteria->compare('product.name',$model->productName,true);

	$criteria->compare('transfree0.profiles.pp.name',$model->transfree, true);

           

	$criteria->together= true;

				

	$dataProvider = new CActiveDataProvider('Entry' ,  array('criteria' => $criteria));

i got this error :




 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.name LIKE '%asd%')' at line 1. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`entry_id`) FROM `entry` `t` LEFT OUTER JOIN `entry_item` `entryItems` ON (`entryItems`.`entry_id`=`t`.`entry_id`) LEFT OUTER JOIN `product` `product` ON (`entryItems`.`product_id`=`product`.`product_id`) LEFT OUTER JOIN `tbl_users` `user` ON (`t`.`user_id`=`user`.`id`) LEFT OUTER JOIN `tbl_profiles` `profiles` ON (`profiles`.`user_id`=`user`.`id`) LEFT OUTER JOIN `tbl_users` `transfree0` ON (`t`.`transfree`=`transfree0`.`id`) WHERE (transfree0.profiles.pp.name LIKE :ycp0)

whats my wrong ?

im new in YII , i did 1 day google , but nothing …

ty

2

When you add conditions using $criteria->compare you should only use the table (relation) alias and column name, not whole relation path.

After specifying relations in the ‘with’ param and enabling ‘together’ it will make them available in the query.

You got the whole SQL query in the error message. Try looking at it to see what’s wrong.

3

Just for a supplement to nick’s good answer:




	...

	// $criteria->compare('user.profiles.name',$model->username,true);

	$criteria->compare('profiles.name',$model->username,true);

	...

	// $criteria->compare('transfree0.profiles.pp.name',$model->transfree, true);

	$criteria->compare('pp.name',$model->transfree, true);

	...

I saw many people stumbled over it … so once I did. :)