MANY_MANY relationship condition in CGridView

Hi Folks,

Really hoping someone can help me with this - it’s driving me insane. :blink:

I’m using CGridView to display a list of vehicles but want to limit those dsplayed to those ‘owned’ by the logged in user. I’m attempting to use a condition in the CActiveDateProvider in my Vehicle model to supply the data to the admin view. I have a MANY_MANY relationship set up which works fine when used with a findAll method elsewhere.

Here’s the relevant relation in the Vehicle model:

'users' => array(self::MANY_MANY, 'User', 'tbl_user_vehicle(user_id, vehicle_id)')

The actionAdmin method in vehicleController is unchanged from the default:

public function actionAdmin()


	$model=new Vehicle('search');

	$model->unsetAttributes();  // clear any default values








Here’s the search method in the Vehicle model used by admin view via the vehicleController (I’ve removed all the criteria apart from those relevant to the condition I’m interested in for now).

public function search()


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

	// should not be searched.

	$criteria=new CDbCriteria;

	$criteria->with = array('users');

	$criteria->condition = 'users.user_id = :userId';

	$criteria->params = array(':userId' => Yii::app()->user->id);


	return new CActiveDataProvider('Vehicle', array(

		'criteria' => $criteria,



The problem is that with condition criteria set as above a CDbException is thrown (Column not found: 1054 Unknown column ‘users.user_id’ in ‘where clause’) which isn’t surprising since according to the SQL output in the log the associative table tbl_user_vehicle isn’t being referenced. Weirdly, if i change the condition above to:

$criteria->condition = 'user_id = :userId';

the associateve tabel is referenced but the exception thrown is ‘Column ‘user_id’ in where clause is ambiguous’. SQL in this case is:

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: 

Integrity constraint violation: 1052 Column 'user_id' in where clause is ambiguous. 

The SQL statement executed was: 

SELECT COUNT(DISTINCT `t`.`vehicle_id`) FROM `tbl_vehicle` `t` 

LEFT OUTER JOIN `tbl_user_vehicle` `users_users`

ON (`t`.`vehicle_id`=`users_users`.`vehicle_id`) LEFT OUTER JOIN `tbl_user` `users` 

ON (`users`.`user_id`=`users_users`.`user_id`) WHERE (user_id = :userId) 

If anyone can suggest a solution to this I’d be very grateful. Apologies if this is very basic but I have searched (for days) without finding a similar example.

Mmmm have you tryed with:

        $criteria->condition = 'users.user_id = ' . Yii::app()->user->id;

Thanks for the suggestion but I get the unknown column exception with that criteria and the SQL doesn’t reference the right table. I’ve just noticed that if I change the column to ‘’ (or anything which doesn’t actually exist in the DB) the SQL includes the join to the associative table. Seems like strange behaviour but maybe I’m interpreting the log wrong.

Use together to force a single query to be issued

$criteria->together = true;


That did it. Thanks so much!! :D