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