I have a tough situation now, here is my sql command and I want to use this in my controller. Can any one help me how to do that
select x.p_id, (select count(*) from Process y where y.p_id = x.p_id
and y.Status = ‘P’) as “Count of P”, (select count() from Process y where y.p_id = x.p_id and y.Status = ‘F’) as “Count of F”, (select count() from Process y where y.p_id = x.p_id and y.Status = ‘U’) as “Count of U” from Process x group by p_id
$connection = new CDbConnection(Yii::app()->db->connectionString, Yii::app()->db->username, Yii::app()->db->password);
$connection->active = true;
$sql = '
select
x.p_id,
(select count(*) from Process y where y.p_id = x.p_id and y.Status = 'P') as "Count of P",
(select count(*) from Process y where y.p_id = x.p_id and y.Status = 'F') as "Count of F",
(select count(*) from Process y where y.p_id = x.p_id and y.Status = 'U') as "Count of U"
from Process x
group by p_id
';
$command = $connection->createCommand($sql);
$model = $command->queryAll();
select x.p_id, (select count(*) from Process y where y.p_id = x.p_id
and y.Status = ‘P’) as “Count of P”, (select count() from Process y where y.p_id = x.p_id and y.Status = ‘F’) as “Count of F”, (select count() from Process y where y.p_id = x.p_id and y.Status = ‘U’) as “Count of U” from Process x group by p_id
If you are trying realize it with Active Records then you need to configure the relations in your model TestExecution. I think you should use self::STAT type of relations, but in your case but in your case it also specified by Status field and I don’t know how the AR query may looks like.