Multiple joins in yii and CDbCriteria

Yii 1.1 General Discussion
1

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 t.id=a.user_id and a.role='.Role::MANAGER;

	$criteria->distinct=true;

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

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

Thanks

2

Hello

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

http://www.yiiframework.com/doc/guide/1.1/en/database.arr

Thanks

3

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));
4

Thanks a lot