Difference between #1 and #2 of Using sub query for doubletts

unchanged
Title
Using sub query for doubletts
unchanged
Category
Tips
unchanged
Tags
active record, cdbcriteria, subquery, doubletts
changed
Content
findat first 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(<colname>) > 1 means: find all
records more then one result.fields:

~~~
[php]
$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.
~~~
[php]
$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:condition:

~~~
[php]
$mainCriteria=new CDbCriteria();
$mainCriteria->condition=' (col1,col2,col3) in ('.$subQuery.') ';
$mainCriteria->order = 'col1,col2,col3';
~~~


 

how to use:

~~~
[php]
$result = MyModel::model()->findAll($mainCriteria);

or
$dataProvider = new CActiveDataProvider('MyModel', array(
        'criteria'=>$mainCriteria,
));
~~~