Adrime Adrime - 6 months ago 30
MySQL Question

Retrieve a CActiveDataProvider with a criteria in yii

I'm starting with yii and I have some trouble when trying to filter an CActiveDataProvider with a criteria.

This is my database model:

Table Columns
Project [id, name, status]
userToProject [user_id,project_id,role]
User [id , ....]


And I'd like the index action to show all the projects with status=finished or where the user is currently assigned to that project.

So hard-coding the user id for testing purposes. This is my code:

$criteria=array(
'order'=>'status desc',
'with'=>array(
'userToProject','userToProject.user'=>array('alias'=>'user')),
);
$criteria['condition']='status=='.Project::STATUS_FINISHED;
$criteria['condition'].=' OR user.id = 6';
$dataProvider=new CActiveDataProvider('Project', array(
'criteria'=>$criteria,
));
$this->render('index',array(
'dataProvider'=>$dataProvider,
));


But it throws an Exception saying Unknown column 'user.id. What am I missing? Thanks

Edit: The error code is:

Column not found: 1054
Unknown column 'users.user_id' in 'where clause'. The SQL statement executed was:
SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`, `t`.`description` AS `t0_c2`,
`t`.`status` AS `t0_c3`, `t`.`creation_date` AS `t0_c4` FROM `pgp_project` `t` WHERE
(status=4 OR users.user_id=6) ORDER BY status desc LIMIT 10

Answer

Try this

$criteria=new CDbCriteria(array(                    
    'order'=>'status desc',
    'with'   => array('userToProject'=>array('alias'=>'user')),
    'condition'=>'status='.Project::STATUS_FINISHED.' OR user.id = 6',
));

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

$this->render('index',array(
    'dataProvider'=>$dataProvider,
));
Comments