Multiple joins in yii and CDbCriteria

I’m starting with yii.

I have the following DB structure:

    Table:             Rows:

    user               [id,login,password,name,email]

    userToProject      [user_id,project_id,role]

    project            [id,name,status]

And I want to retrieve all the users working in a project with status=3 as role=manager. And this is my code by the way, I need to make the second join to reach the project status.

	$criteria=new CDbCriteria;

	$criteria->join='INNER JOIN {{userToProject}} a ON and a.role='.Role::MANAGER;


	return User::model()->findAll($criteria);

Can I make it with a criteria or should I implement an SQLcommand and run it?



You can use relation (IN model) here as well.


Yeah relations are the way to go:

Model User:

'usertoproject' => array(self::HAS_MANY, 'UserToProject', 'user_id')

Model UserToProject:

'project' => array(self::BELONGS_TO, 'Project', 'project_id')

Getting your values:

$m = User::model()->with(array('usertoproject', 'usertoproject.project'=>array('alias'=>'project')))->findAll('project.status=3 AND usertoproject.role=?', array(Role::MANAGER));

Thanks a lot