CODEWITHSUNDEEP
sqlyii
Ivan Manzhos
Ivan Manzhos

Reputation: 743

Getting data with CActiveDataProvider in yii

I have 3 tables, standart relation MANY-TO-MANY Users(id,...) -> Users_Has_Courses(Users_id, Courses_id) -> Courses(id,...)

Courses Model has next relation

'users' => array(self::MANY_MANY, 'Users', 'users_has_courses(Courses_id, Users_id)')

Users Model has next relation

'courses' => array(self::MANY_MANY, 'Courses', 'users_has_courses(Users_id, Courses_id)')

Please, say how I can get list of courses, on which user with specified "id" hasn't been subscribed with CActiveDataProvider ? Otherwords, I need an analogue of this plain SQL query

select * from Courses where id not in (select Courses_id from users_has_courses where Users_id = 2)

thanks for the help

Upvotes: 3

Views: 9368

Answers (1)

thaddeusmt
thaddeusmt

Reputation: 15600

Instead of a regular "relation", try a parametrized Named Scope to encapsulate the query. In your Courses model, add this scope function to get a list of all the courses the user is not in:

public function userNotIn($user_id)
{
  $criteria=new CDbCriteria();
  $criteria->condition .= 't.id NOT IN (SELECT users_has_courses.Courses_id FROM users_has_courses WHERE users_has_courses.Users_id = :userid)';
  $criteria->params[':userid'] = $user_id;
  $this->getDbCriteria()->mergeWith($criteria);
  return $this;
}

Then you should be able to do this:

$coursesNotIn=new CActiveDataProvider(Courses::model()->userNotIn($user->id));

This code is completely untested, but it should work in principle. I do this sort of thing often when I have a complex query but I still want to use the AR features, like CActiveDataProvider. Read more about "named scopes" here:

http://www.yiiframework.com/doc/guide/1.1/en/database.ar#parameterized-named-scopes

Good luck!

Upvotes: 5

Related Questions