Yii Framework Forum: Using addInCondition() with sub-query - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Using addInCondition() with sub-query Is this possible? Rate Topic: ***** 1 Votes

#1 User is offline   gjb 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 15
  • Joined: 08-March 10

Posted 31 March 2010 - 07:26 AM

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.
0

#2 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 31 March 2010 - 08:41 AM

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.
1

#3 User is offline   amc 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 46
  • Joined: 13-March 09

Posted 31 March 2010 - 08:43 AM

View Postgjb, on 31 March 2010 - 07:26 AM, said:

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=:B)",
                                          array(':a'=>'SomeValue',':b'=>'SomeOtherValue'));


amc
3

#4 User is offline   gjb 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 15
  • Joined: 08-March 10

Posted 31 March 2010 - 11:35 AM

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)?
0

#5 User is offline   amc 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 46
  • Joined: 13-March 09

Posted 01 April 2010 - 07:57 AM

View Postgjb, on 31 March 2010 - 11:35 AM, said:

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
0

#6 User is offline   Natch 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 05-February 11

Posted 29 June 2011 - 05:23 AM

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 ^^
0

#7 User is offline   Asanka Sri 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 14-October 11
  • Location:Singapore

Posted 19 June 2012 - 03:36 AM

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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users