Yii 1.1: Using sub query for doubletts

8 followers

find doubletts by db fields. in this example i will check doublets for 3 tabelfields (col1,col2,col3). so i will get a subquery with the condition, select and grouping of the tablefields. the having with COUNT() > 1 means: find all records more then one result.

$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: the subquery is a part of the query i need.

$subQuery=$model->getCommandBuilder()->createFindCommand($model->getTableSchema(),$criteria)->getText();

the "real" criteria with the subquery. you can add some criteria you need.. important: the number of col and the result of the subquery must be the same size! add the subquery condition and order by tablefields:

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

Total 3 comments

#12929 report it
mb at 2013/04/21 08:34am
more details added

...sorry for my bad english ;) if you have any questions please let me know

#12928 report it
Shahcheraghean at 2013/04/21 07:55am
expalin

Thank you. It need more details.

#9463 report it
abajja at 2012/08/13 01:35pm
Context

Can you write a little context...?

Leave a comment

Please to leave your comment.

Write new article