I am struggling with trying to deal with sets of data and excluding sets that already exist.
I am pretty good with Oracle SQL but not sure how to do this in Yii.
Give 2 tables: Website, User_Website
Website
(id
,website
)
User_Website
(id
,website_id
,user_id
)
I want to insert into User_Website all the id’s from the Website table that do not already exist in the User_Website
table.
Seems simple enough…in SQL it would be no problem.
Can I create a scope to make this easier?
My code in my controller is
public function createUserSite($id)
{ // $id is from user
$user = User::model()->findByPK($id);
// set criteria for query on Website
$criteria = array(
'condition'=>'quality_id<='.$user->quality_id ,
'order'=>'id',
);
//' and website.id not in (select website_id from user_website where user_id = $id,
// with('usersites')->
[b]// get the websites that are not in sitereview table for that partner[/b]
[b] $sites = Website::model()->findAll($criteria);[/b]
foreach ($sites as $sid) {
// for each site insert a new record.
$reviewsite = new sitereview;
$reviewsite-> user_id=$id;
$reviewsite-> website_id=$sid->id;
$reviewsite->save();
}