sql command

Hello guys,

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

my controller

public function actionReports()

{

$this->processAdminCommand();

$criteria=new CDbCriteria;

$models=TestExecution::model()->findAll($criteria);

$this->render(‘reports’,array(

 'models'=>$models,


 'models1'=>$models1,

));

}


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

Hello,

I don’t need to connect the database again.

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

public function actionReports()

{

$this->processAdminCommand();

$criteria=new CDbCriteria;

$models=TestExecution::model()->findAll($criteria);

$this->render(‘reports’,array(

‘models’=>$models,

));

}

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.