Sub-queries ActiveRecord Yii
Solution 1
First find doublets by 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 the subquery:
$subQuery=$model->getCommandBuilder()->createFindCommand($model->getTableSchema(),$criteria)->getText();
Add the 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/
Solution 2
No, there is not a way to programmatically construct a subquery using Yii's CDbCriteria and CActiveRecord. It doesn't look like the Query Builder has a way, either.
You can still do subqueries a few different ways, however:
$results = Object1::model()->findAll(array(
'condition'=>'t.field1 in (select table2.field2 from table2)')
);
You can also do a join (which will probably be faster, sub-queries can be slow):
$results = Object1::model()->findAll(array(
'join'=>'JOIN table2 ON t.field1 = table2.field2'
);
You can also do a direct SQL query with findAllBySql:
$results = Object1::model()->findAllBySql('
select * from table1 where table1.field1 in
(select table2.field2 from table2)'
);
You can, however, at least provide a nice AR style interface to these like so:
class MyModel extends CActiveRecord {
public function getResults() {
return Object1::model()->findAll(array(
'condition'=>'t.field1 in (select table2.field2 from table2)')
);
}
}
Called like so:
$model = new MyModel();
$results = $model->results;
One interesting alternative idea would be to create your subquery using the Query Builder's CDbCommand or something, and then just pass the resulting SQL query string into a CDbCritera addInCondition()
? Not sure if this will work, but it might:
$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 build subqueries somehow as well. Might make a nice extension you could release! :)
I hope that helps!
Youcef04
Updated on July 21, 2022Comments
-
Youcef04 almost 2 years
Is it possible to make sub-queries in ActiveRecord in Yii?
i have a query like this:
select * from table1 where table1.field1 in (select table2.field2 from table2)
i'm currently using the fallowing code:
object1::model()->findAll(array('condition'=>'t.field1 in (select table2.field2 from table2)'))
[Edit]
i would like to know if there is a manner to construct the sub-query without using SQL, and without using joins.Is there any solution ?
and thanks in advance.
-
Youcef04 over 12 yearsThanks thaddeusmt, this is an example only, my query is even more complicated, and it's impossible to do it with joins, and i want to do it using active record, i would like to know if there is a manner in Yii to construct the sub-query without using SQL ?
-
thaddeusmt over 12 yearsThose are pretty much your options. All three return CActiveRecord objects, I believe, so you are technically still using AR. You can use AR "scopes" to wrap the queries and provide a nice API to your AR objects, as well. Since apparently the question you asked is not your actual question, perhaps you could edit it? Or ask a new one? So that answering it is possible?
-
Youcef04 over 12 yearsthe first option is my solution, it was clear i was looking for another, the second option uses joins, that can't help me, the third one is not interesting. i edited the question, and i hope it's more clear now ?