multiple select statement

Hey guys, I need multiple select statement in one table. here is my controller

problem is

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

not working and not displaying data. Can you tell me where is the problem or any good solution?

public function actionReports()

{

$this->processAdminCommand();

$criteria=new CDbCriteria;

$criteria1=new CDbCriteria;

$criteria->select = ‘T_id,C_id, S_id, Te_Id, COUNT(Te_id) as TC’;

$criteria->addCondition("L_id LIKE ‘%$_GET[L_id]%’ AND T_id LIKE ‘%$_GET[T_id]%’ AND Test_i_id LIKE ‘%$_GET[Test_i_id]%’");

$criteria->distinct = TRUE;

$criteria->group = ‘User_id’;

$criterial->select = ‘T_id,C_id, S_id, Te_Id, COUNT(Te_id) as P’;

$criteria1->addCondition(“status=‘P’ AND L_id LIKE ‘%$_GET[L_id]%’ AND T_id LIKE ‘%$_GET[T_id]%’ AND Test_i_id LIKE ‘%$_GET[Test_i_id]%’”);

$criteria1->distinct = TRUE;

$criteria1->group = ‘User_id’;

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

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

‘models’=>$models,

	));

}

What SQL would you like to get generated?

And BTW you should never do this:

$criteria->addCondition("`L_id` LIKE '%$_GET[L_id]%' AND T_id LIKE '%$_GET[T_id]%' AND Test_i_id LIKE %$_GET[Test_i_id]%'");

as this opens the door for SQL injection. Always use parameter binding for unsafe data from outside ($_GET).

I like to generate three SQL command in one statement

1. SELECT Te_id,Ca_id, S_id, User_id, COUNT(Te_id) as T FROM Test WHERE L_id =1 AND Te_id =1 AND Test_i_id =1 AND TestC_id =2 GROUP BY User_id

2. SELECT Te_id,Ca_id, S_id, User_id, COUNT(Te_id) as P FROM Test WHERE status=‘P’ AND L_id =1 AND Te_id =1 AND Test_i_id =1 AND TestC_id =2 GROUP BY User_id

3. SELECT Te_id,Ca_id, S_id, User_id, COUNT(Te_id) as N FROM Test WHERE status=‘N’ AND L_id =1 AND Te_id =1 AND Test_i_id =1 AND TestC_id =2 GROUP BY User_id

That’s not possible. ActiveRecord’s find*() methods always only use 1 query per find call. You could do 3 separate queries and merge the results in PHP.

can you give me an idea how to do this in php? I need that.

You can do even one query, you have just to use UNION:

SELECT User_id, sum(T) as T, sum(P) as P, sum(N) as N 

FROM 

(

         SELECT User_id, COUNT(Te_id) as T, 0 as P, 0 as N FROM `Test` WHERE $condition GROUP BY User_id

   UNION SELECT User_id, 0 as T, COUNT(Te_id) as P, 0 as N FROM `Test` WHERE $condition GROUP BY User_id

   UNION SELECT User_id, 0 as T, 0 as P, COUNT(Te_id) as N FROM `Test` WHERE $condition GROUP BY User_id 

)

Unluckily ActiveRecrod doesn’t allow to set the ‘from’ clause, so you if you want to do use activeRecord you have to joint the table in FROM with some other table.

Doing one query has the great advantage of avoiding to merge in php the solutions, so is possible to sort, page and filter in DB

I have been fighting with this limitation of ActiveRecord as well. Instead of [FROM user, user_group], I ended up doing a hokey JOIN statement like so:

LEFT JOIN user_group ON true

That way I have access to the user_group table in my conditionals, but without using a multiple "FROM".

I have been trying to figure out a better way to do it:

Nothing as worked so far, since I can’t use a UNION or an EXISTS clause with AR, I am still using the hokey JOIN.

Have you tried something like this?

$connection = Yii::app()->db;

		

$query = <<< END_SQL

	SELECT [blah blah blah]] FROM table1 t WHERE t.user_id = :userId

	UNION

	SELECT [blah2 blah2 blah2] FROM table2 t2 WHERE t2.user_id = :userId

	ORDER BY [blah, blah] DESC

END_SQL;

		

$command = $connection->createCommand($query);

		

$command->bindParam(":userId", $this->id, PDO::PARAM_INT);

		

$result = $command->query();

Just a thought.