gjb
(Greg)
March 31, 2010, 12:26pm
1
I am trying to build the following SQL query into a CDbCriteria:
SELECT * FROM MyModel
WHERE Column1='SomeValue'
AND Column2 IN (SELECT Column2 FROM OtherModel WHERE Column3='SomeOtherValue');
This is what I’ve got, but I can’t work out how to get the sub-query as a paramater to addInCondition():
$criteria=new CDbCriteria;
$criteria->condition='Column1=:Column1';
$criteria->params=array(':Column1'=>'SomeValue');
$criteria->addInCondition('Column2', array( *** RESULT OF SUB-QUERY GOES HERE *** ));
$dataProvider=new CActiveDataProvider('MyModel', array('criteria'=>$criteria));
Any pointers or advice much appreciated.
mikl
(Mike)
March 31, 2010, 1:41pm
2
Why not use a JOIN instead? It’s easier to integrate. Your query should be equivalent to this join:
SELECT *
FROM MyModel m
INNER JOIN OtherModel o ON o.Column3='SomeOtherValue' AND o.Column2=m.Column2
WHERE m.Column1='SomeValue'
EDIT:
Might need some tuning . E.g. add a GROUP BY m.id or something, to not get all the joined rows in your result set.
amc
(Beemer3d)
March 31, 2010, 1:43pm
3
gjb:
I am trying to build the following SQL query into a CDbCriteria:
SELECT * FROM MyModel
WHERE Column1='SomeValue'
AND Column2 IN (SELECT Column2 FROM OtherModel WHERE Column3='SomeOtherValue');
This is what I’ve got, but I can’t work out how to get the sub-query as a paramater to addInCondition():
$criteria=new CDbCriteria;
$criteria->condition='Column1=:Column1';
$criteria->params=array(':Column1'=>'SomeValue');
$criteria->addInCondition('Column2', array( *** RESULT OF SUB-QUERY GOES HERE *** ));
$dataProvider=new CActiveDataProvider('MyModel', array('criteria'=>$criteria));
Any pointers or advice much appreciated.
There may be a better way to do this, but one way might be
$results = OtherModel::model()->findAll('Column3=:v',array(':v'=>'SomeOtherValue');
$values = array();
foreach($results as $r) $values[] = $r->Column2;
...
$criteria->addInCondition('Column2', $values);
Alternatively, you could just use straight sql (probably better performance if sub-query returns a lot of rows).
results = MyModel::model()->findAllBySql("SELECT * FROM MyModel WHERE Column1=:a AND Column2 IN
(SELECT Column2 FROM OtherModel WHERE Column3=:<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />",
array(':a'=>'SomeValue',':b'=>'SomeOtherValue'));
amc
gjb
(Greg)
March 31, 2010, 4:35pm
4
Thank you both for your answers.
If I use:
$results = MyModel::model()->findAllBySql("...");
How do I get the data into a CActiveDataProvider object (which is what CListView requires)?
amc
(Beemer3d)
April 1, 2010, 12:57pm
5
gjb:
Thank you both for your answers.
If I use:
$results = MyModel::model()->findAllBySql("...");
How do I get the data into a CActiveDataProvider object (which is what CListView requires)?
You can’t. You will have to use the first option I suggested and pass the criteria to CActiveDataProvider
amc
Thank so much AMC you really help me with your snippet example above.
I have tried many way to convert my query with sub-query from model findAllBySql to CDbCriteria since yesterday until I found and read through this post. And it works now ^^
Cannot you use below one?
$criteria=new CDbCriteria;
$criteria->addCondition(‘Column1=:Column1’);
$criteria->addCondition(‘Column2 IN (SELECT Column2 FROM tablename WHERE fieldname=somevalue)’);
$criteria->params=array(’:Column1’=>‘SomeValue’);
$dataProvider=new CActiveDataProvider(‘MyModel’, array(‘criteria’=>$criteria));