Reputation: 743
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
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