ActiveRecord Yii Subqueries

Question

Can I make subqueries in ActiveRecord in Yii?

I have a query like this:

select * from table1 where table1.field1 in (select table2.field2 from table2)

I am currently using the code:

object1::model()->findAll(array('condition'=>'t.field1 in (select table2.field2 from table2)'))

[Change]
I would like to know if there is a way to create a subquery without using SQL and without using joins.

Is there any solution?

and thanks in advance.

Answer 1

First find the doublets by the db fields:

 $model=new MyModel('search'); $model->unsetAttributes(); $criteria=new CDbCriteria(); $criteria->select='col1,col2,col3'; $criteria->group = 'col1,col2,col3'; $criteria->having = 'COUNT(col1) > 1 AND COUNT(col2) > 1 AND COUNT(col3) > 1'; 

Get subquery:

 $subQuery=$model->getCommandBuilder()->createFindCommand($model->getTableSchema(),$criteria)->getText(); 

Add a subquery condition:

 $mainCriteria=new CDbCriteria(); $mainCriteria->condition=' (col1,col2,col3) in ('.$subQuery.') '; $mainCriteria->order = 'col1,col2,col3'; 

How to use:

 $result = MyModel::model()->findAll($mainCriteria); 

Or:

 $dataProvider = new CActiveDataProvider('MyModel', array( 'criteria'=>$mainCriteria, )); 

Source: http://www.yiiframework.com/wiki/364/using-sub-query-for-doubletts/

Answer 2

No, there is no way to programmatically build a subquery using Yii CDbCriteria and CActiveRecord . This is not like Query Builder .

You can still make subqueries in several ways:

 $results = Object1::model()->findAll(array( 'condition'=>'t.field1 in (select table2.field2 from table2)') ); 

You can also make a join (which is likely to be faster, subqueries may be slow):

 $results = Object1::model()->findAll(array( 'join'=>'JOIN table2 ON t.field1 = table2.field2' ); 

You can also make a direct SQL query with findAllBySql :

 $results = Object1::model()->findAllBySql(' select * from table1 where table1.field1 in (select table2.field2 from table2)' ); 

However, you can at least provide a nice AR-style interface like this:

 class MyModel extends CActiveRecord { public function getResults() { return Object1::model()->findAll(array( 'condition'=>'t.field1 in (select table2.field2 from table2)') ); } } 

Called like this:

 $model = new MyModel(); $results = $model->results; 

One interesting alternative idea is to create your subquery using Query Builder CDbCommand or something else, and then just pass the resulting SQL query string to CDbCritera addInCondition() ? Not sure if this will work, but may:

 $sql = Yii::app()->db->createCommand() ->select('*') ->from('tbl_user') ->text; $criteria->addInCondition('columnName',$sql); 

You can always extend the base CDbCriteria class to process and assemble subqueries anyway. You can make a good extension that you could release! :)

I hope this helps!

Answer 3

I know this is an old thread, but maybe someone (like me) still needs an answer.

There are minor issues related to previous answers. So here is my boost:

 $model=new SomeModel(); $criteria=new CDbCriteria(); $criteria->compare('attribute', $value); $criteria->addCondition($condition); // ... etc $subQuery=$model->getCommandBuilder()->createFindCommand($model->getTableSchema(),$criteria)->getText(); $mainCriteria=new CDbCriteria(); $mainCriteria->addCondition($anotherCondition); // ... etc // NOW THIS IS IMPORTANT $mainCriteria->params = array_merge($criteria->params, $mainCriteria->params); // Now You can pass the criteria: $result = OtherModel::model()->findAll($mainCriteria);