Hi Folks,
Really hoping someone can help me with this - it’s driving me insane.
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
if(isset($_GET['Vehicle']))
$model->attributes=$_GET['Vehicle'];
$this->render('admin',array(
'model'=>$model,
));
}
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.