Yii Framework Forum: sql command - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

sql command Rate Topic: -----

#1 User is offline   nemo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 142
  • Joined: 24-February 10

Posted 07 June 2010 - 12:54 PM

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

#2 User is offline   frantic 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 172
  • Joined: 01-March 10
  • Location:Saint-Petersburg

Posted 07 June 2010 - 12:58 PM

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

0

#3 User is offline   nemo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 142
  • Joined: 24-February 10

Posted 07 June 2010 - 01:12 PM

View Postfrantic, on 07 June 2010 - 12:58 PM, said:

$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,

));
}
0

#4 User is offline   frantic 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 172
  • Joined: 01-March 10
  • Location:Saint-Petersburg

Posted 07 June 2010 - 01:22 PM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users