Search query using exists keywords

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

I’m confused. Are you trying to find all records in table_one that don’t have corresponding records in table_two?

Yes

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()

))

$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?

Maybe this … not tested.

TableOne::model()->findAll('(select count(*) from table_two t2 where t2.ref_id = t1.id) = 0');

Is this returns AR Model class, and can we use relations?

it returns an array,the relation you have in sql

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.