Subquery With Yii Active Record

Is it possible to write sub queries with Yii active record?

I need to add a subquery in the FROM clause. Below is the SQL with the subquery but I want to specifically write it using Active Record.

In SQL, Policy has a many to many relationship with policy_vehicles.

SQL Query:




select * from (SELECT Name , GROUP_CONCAT(PV.VehicleId SEPARATOR ',') as VehicleIds 

FROM `policies` `t` 

LEFT JOIN policy_vehicles PV USING(PolicyId)

WHERE (Type IN ('C', 'E')) AND (Status='Active')

GROUP BY t.PolicyId 

) k where VehicleIds like '%1%' AND VehicleIds like '%2%'



This is how I want to use it in the Controller class:




$model = Policy::model();

$model->Status = array('Active');

$model->VehicleIds = array(1,2);

$model->Type = array('C', 'E');

$datas = $model->search()->getData();

print_r($datas);



Search function in the model:




public function search() {


    $joins = array();

    $criteria = new CDbCriteria;

    $criteria->compare('Type', $this->Type);

    $criteria->compare('Status', $this->Status);


    array_push($joins, 'LEFT JOIN policy_vehicles PV USING(PolicyId)');

    $criteria->join = implode(' ', $joins);


    return new CActiveDataProvider($this, array(

        'criteria' => $criteria,

    ));

}



CDbCriteria has ‘with’ property to set other joins.