Limiting CActiveDataProvider by relations?

Yii 1.1 General Discussion
1

Hi,

I’m a beginner here so am trying to get my head around a couple of issues.

I have a database set-up with let’s say 3 tables.

user (id, name, status)

user_skill (id, user_id, skill_id)

skill (id, name)

As you can I’m sure guess, a user can have many skills.

Now, I have set-up the User model with the following relations:




public function relations()

{

	return array(

		'skills' => array(self::HAS_MANY, 'UserSkill', 'user_id'),

	);

}

In a controller I am getting a list of all of the users with the following code:




	$criteria = array(

		'order'=>'id DESC',

		'condition'=>'status=1',		

	);

		

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

Which works fine.

What I want to now do is get all the users who have specific skills. So say I have a list of skills (1,2,3)

If I was going to do this in SQL I would do something like:

SELECT *

FROM user u

WHERE EXISTS(

SELECT * FROM user_skill s 


WHERE s.skill_id in (1,2,3) 


AND u.id = s.user_id

)

There’s probably a better way of doing that, but you get the drift.

How would I do that using Yii? Can I add an extra bit to the criteria?

I’ve been tearing my hair out trying to work this out! Any help would be gratefully appreciated.

2

try this


          $criteria = new CDbCriteria;

          $criteria->join = 'join user_skill on user_skil.user_id = t.id'; 

          $criteria->condition = 'user_skill = 1 or user_skill = 2 or user_skill = 3';

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

Excellent thanks.

I’ve actually decided to do it the opposite way - i.e. just interrogate the user_skill table. Then I can get the user details from the skills results.

But thanks - I now know how to put a join in a criteria. Ta.