Using addInCondition() with sub-query

I am trying to build the following SQL query into a CDbCriteria:


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->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:


FROM MyModel m

INNER JOIN OtherModel o ON o.Column3='SomeOtherValue' AND o.Column2=m.Column2

WHERE m.Column1='SomeValue'


Might need some tuning ;). E.g. add a GROUP BY 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='' class='bbc_emoticon' alt='B)' />",



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


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(‘Column2 IN (SELECT Column2 FROM tablename WHERE fieldname=somevalue)’);


$dataProvider=new CActiveDataProvider(‘MyModel’, array(‘criteria’=>$criteria));