How To Add Where In Yii Model Search Method?

I have four tables in my DB as shown(user, user_test, test, status). There is a M:M to relation between user and test and therefore user_test is a Gerund between them.

The status table has statuses for both user_test and test tables. The tablename field in status table shows which table the status belongs to as shown in the lower image.

I want to show table user_test in a CGridView with the related data in all the three tables user, test and status. All is well as the relations are correct.

Problem: When I want to show status.name in my CGrid like:

array(


	'header'=>'Status',


	'value'=>'$data->status->name',


), 

It gives me name ‘completed’ but it is the status of table ‘test’ and the correct one should be ‘confirmed’ as status_id in user_test is 2.

Any help?

!Database design

!Database design

public function search()


{


	// Warning: Please modify the following code to remove attributes that


	// should not be searched.





	$criteria=new CDbCriteria;


	$criteria->with=array('user','test','status');


	


	$criteria->compare('id',$this->id);


	$criteria->compare('user_id',$this->user_id);


	$criteria->compare('test_id',$this->test_id);


	$criteria->compare('status.id',$this->status_id);


	$criteria->compare('bonus',$this->bonus);


	$criteria->compare('user.signum',$this->signum, FALSE);


	$criteria->compare('user.email',$this->email, FALSE);


	$criteria->compare('test.seats',$this->seats, FALSE);


	


	return new CActiveDataProvider($this, array(


		'criteria'=>$criteria,


		'sort'=>array(


			'attributes'=>array(


				'signum'=>array(


					'asc'=>'user.signum',


					'desc'=>'user.signum DESC',


				),


				'email'=>array(


					'asc'=>'user.email',


					'desc'=>'user.email DESC',


				),


				'seats'=>array(


					'asc'=>'test.seats',


					'desc'=>'test.seats DESC'


				),


				'*',


			),


		),


	));


}

Sounds to me as if relations were not correct, although you state otherwise. Did you turn on logging and check the actual queries. Should be easy to track where this value is coming from.

btw. The proper type for status.tablename would probably be ENUM("user_test", "test"), instead of VARCHAR.

How to turn on logging and check queries? thanks

Set




'db'=>array(

  //....

  'enableProfiling'=>true,

  'enableParamLogging'=>true,

  //....

),

//..

in your configuration. And then add a log route, for example CWebLogRoute (shows logs directly on website):




    'log' => array(

            'class' => 'CLogRouter',

            'routes' => array(

               array(

                    'class'=>'CWebLogRoute',

                ),


            ),

       ),



Querying SQL: SELECT t.id AS t0_c0, t.user_id AS t0_c1,

t.test_id AS t0_c2, t.status_id AS t0_c3, t.bonus AS

t0_c4, t.hash AS t0_c5, t.timeslots_id AS t0_c6, user.id

AS t1_c0, user.signum AS t1_c1, user.email AS t1_c2,

user.pnr AS t1_c3, user.birth_year AS t1_c4, user.gender AS

t1_c5, user.ericsson_employee AS t1_c6, user.name AS t1_c7,

user.work_profile AS t1_c8, user.registration_date AS t1_c9,

user.de_registration_date AS t1_c10, user.available AS t1_c11,

user.test_type_id AS t1_c12, user.password AS t1_c13,

user.roles AS t1_c14, user.username AS t1_c15, test.id AS

t2_c0, test.name AS t2_c1, test.seats AS t2_c2,

test.timeslots_id AS t2_c3, test.status_id AS t2_c4,

test.test_type_id AS t2_c5, test.publish_date AS t2_c6,

test.publish_time AS t2_c7, status.id AS t3_c0, status.name

AS t3_c1, status.value AS t3_c2, status.sequence AS t3_c3,

status.tablename AS t3_c4 FROM user_test t LEFT OUTER JOIN

user user ON (t.user_id=user.id) LEFT OUTER JOIN test test

ON (t.test_id=test.id) LEFT OUTER JOIN status status ON

(t.status_id=status.id) LIMIT 10

Thanks

UPDATED: The sql I want to generate is:

             SELECT * FROM user_test AS UserTest


             INNER JOIN user ON user.id=UserTest.user_id


             INNER JOIN test on test.id=UserTest.test_id


             INNER JOIN (SELECT status.value, name from status where status.tablename='user_test') AS Status ON (Status.value = UserTest.status_id)

use addincondition,in search method for where clause.

I have given the SQL can you please write the addincondition for it?