Limiting CActiveDataProvider by relations?


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',




	$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:


FROM user u


SELECT * FROM user_skill s 

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

AND = 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.

try this

          $criteria = new CDbCriteria;

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

          $criteria->condition = 'user_skill = 1 or user_skill = 2 or user_skill = 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.