Using addInCondition() with sub-query

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.

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.

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

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