I am trying to execute rank per group query in yii framework to show results in a CGridview, but it is giving error, Here is my code in controller.
$sql = "SELECT t.ID as ID,
t.count as Count
FROM (
SELECT t.*,
@rank:= CASE WHEN @group = t.ID THEN @rank +1 ELSE 1 END rank ,
@group:= t.ID
FROM
( SELECT p1.emp_id as ID , p1.project_id as item,
(select count(s.project_id)
from EmployeeStats s
where s.project_id = p1.project_id
and s.action = 'START'
and s.time BETWEEN '$from' AND '$to'
) as count
FROM ManagerEmployee p1
where p1.manager_id in(select p.id from Manager p where p.type = 'X')
group by p1.project_id
ORDER BY p1.manager_id
)as t
ORDER BY t.ID ,t.count DESC
) t WHERE t.rank <=3";
$rawData = Yii::app()->db->createCommand($data);
return $perGenre = new CSqlDataProvider($rawData,array(
'keyField'=>'ID',
'pagination'=>array(
'pageSize'=>10,
)
));
I have tested the above query($sql) in Mysql workbench it is working and giving desire results, but when i execute the above code in yii framework it is giving me following error
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]:
Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near
‘FROM (\n\t\t\t\tSELECT t.*,\n\t\t\t\t\t\t@rank:= CASE WHEN @group = t.genre THEN @rank +1 EL’ at line 5
i have also added following below lines at the top of $sql query string .
"SET @rank=0; SET @group=’’;
But it is giving
CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error
Please help me to figure out a, why rank per group is not working in framework?