Hi,
$dataReader =
Yii::app()->db->createCommand()
->select('*')
->from("table_one AS t1")
->where("NOT EXISTS (SELECT * FROM table_two AS t2 WHERE t1.id = t2.ref_id)")
->query();
This code I’m still using to find existing record referenced by foreign key.
Here I’ve posted this because I want a another solution.
Is there any alternate solution to query like above using model class? Because in this case I can’t concatenate two fields, but in model class I can do this by adding a method inside.
So, please if any solution you have please reply!
Thanks
Keith
(Kburton)
March 2, 2012, 3:05pm
2
I’m confused. Are you trying to find all records in table_one that don’t have corresponding records in table_two?
I want an alternate to do this with model class instead like this (above post).
Here I’ve used CDbCommand to query an statement, and this doesn’t returns an object like CActiveRecord as AR-Object.
See an expected script, which I want,
TableOne::model()->findAll(array(
'condition' => TableTwo::model()->exists( 't1.id = t2.ref_id')->findAll()
))
zipzapduo
(Zipzapduo)
March 19, 2012, 9:52am
5
$sql = "SELECT * FROM t1 WHERE t1.id not in (SELECT t2.id_fk FROM t2)";
$command = Yii::app()->db->createCommand($sql);
return $records=$command->queryAll();
have you tried this?
softark
(Softark)
March 19, 2012, 10:46am
6
Maybe this … not tested.
TableOne::model()->findAll('(select count(*) from table_two t2 where t2.ref_id = t1.id) = 0');
$sql = "SELECT * FROM t1 WHERE t1.id not in (SELECT t2.id_fk FROM t2)";
$command = Yii::app()->db->createCommand($sql);
return $records=$command->queryAll();
have you tried this?
Is this returns AR Model class, and can we use relations?
zipzapduo
(Zipzapduo)
March 19, 2012, 11:32am
8
it returns an array,the relation you have in sql
luc
(Luc Pfister)
March 20, 2012, 1:59pm
9
maybe override CARecord class to define a new relation class "CHasNoRelation" more or less like "CHasManyRelation"
I think most of the job is done by getRelated function.
Easy to say but to code it is far away from my basic knowledge.