Ok a bit of background: I AM A N00B. both at web development, SQL, and yii (thats three!). None the less I am a programmer …
I am trying to find nodes that are not tagged like so:
$dataProvider = new CActiveDataProvider(Node::model()->notTagged());
more details are at the end of the post …
What I get from yii is 3 queries:
2011/11/17 16:58:07 [trace] [system.db.CDbCommand] Querying SQL: SELECT COUNT(DISTINCT `t`.`id`) FROM `tbl_nodes` `t` LEFT OUTER JOIN `tbl_tags` `taggedBy` ON (`taggedBy`.`n2id`=`t`.`id`) AND ((taggedBy.uid=2) AND (n1id IS NULL)) WHERE (t.uid=2)
2011/11/17 16:58:07 [trace] [system.db.CDbCommand] Querying SQL: SELECT `t`.`id` AS `t0_c0`, `t`.`uid` AS `t0_c1`, `t`.`data` AS `t0_c2` FROM `tbl_nodes` `t` WHERE (t.uid=2) LIMIT 10
2011/11/17 16:58:07 [trace] [system.db.CDbCommand] Querying SQL: SELECT `t`.`id` AS `t0_c0`, `taggedBy`.`id` AS `t1_c0`, `taggedBy`.`uid` AS `t1_c1`, `taggedBy`.`n1id` AS `t1_c2`, `taggedBy`.`n2id` AS `t1_c3` FROM `tbl_nodes` `t` LEFT OUTER JOIN `tbl_tags` `taggedBy` ON (`taggedBy`.`n2id`=`t`.`id`) AND ((taggedBy.uid=2) AND (n1id IS NULL)) WHERE (`t`.`id` IN (61, 63, 64, 65, 66, 67, 68, 69, 70, 71))
The first query correctly manages to count all nodes that are not tagged. It does an outer join on the nodes and tagges and then checks for thoses that are null. But the second query, which I persume is fetching the keys used in the third query does not perform the join. Is this a bug? I am lost.
part of the class definitions are as follows:
class Node extends CActiveRecord
{
public function relations()
{
return array(
'taggedBy'=>array(self::HAS_MANY,'Tag','n2id','joinType'=>'LEFT OUTER JOIN'),
);
}
public function defaultScope()
{
$t=$this->getTableAlias(false,false);
return array(
'condition'=>"$t.uid=".Yii::app()->user->id,
);
}
public function notTagged(){
$this->getDbCriteria()->mergeWith(array(
'with'=>array('taggedBy:isNull'),
));
return $this;
}
and the Tag class:
class Tag extends CActiveRecord
{
public function scopes()
{
return array(
'isNull'=>array(
'condition'=>'n1id IS NULL',
),
);
}
public function defaultScope()
{
$t=$this->getTableAlias(false,false);
return array(
'condition'=>"$t.uid=".Yii::app()->user->id,
);
}
[/code]