Computed column and Issue with select in CDbCriteria

I have this search function in my Skill model(used for CGridView in one of my views):

public function searchWithHierarchy()


		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;






		//original query - select IFNULL(Round(((SUM(ROUND((student_skills.value/skill.value)*100,0)))/(select COUNT(*) from user)),0),0) as successRate from skill left JOIN student_skills on = student_skills.skill_id group by 

		$criteria->select = 'IFNULL(Round(((SUM(ROUND((student_skills.value/skill.value)*100,0)))/(select COUNT(*) from user)),0),0) as successRate';

		$criteria->join = 'left JOIN student_skills on = student_skills.skill_id';

		$criteria->group = "";



		return new CActiveDataProvider($this, array(












I wanted to have computed column ‘success rate’ in CGridView (along with other skill columns like name, value…) but I also wanted to sort and filter it like all the other columns.

But $criteria->select is throwing exception:

Active record "Skill" is trying to select an invalid column "IFNULL(Round(((SUM(ROUND((student_skills.value/skill.value)*100". Note, the column must exist in the table or be an expression with alias.

I think it’s because of the comma that’s there but I can’t change the query.

Can anyone suggest how I should change my search function so that I can have in CGridView column ‘success rate’ that is sortable and filterable?

Note: the query is correct

Try providing the columns as an array instead, which should prevent Yii from splitting on the comma:

$criteria->select = array('IFNULL(Round(((SUM(ROUND((student_skills.value/skill.value)*100,0)))/(select COUNT(*) from user)),0),0) as successRate');


by your suggestion I changed select, join, group part to this:

		$criteria->select = array('IFNULL(Round(((SUM(ROUND((student_skills.value/t.value)*100,0)))/(select COUNT(*) from user)),0),0) as successRate');

		$criteria->join = 'left JOIN student_skills on = student_skills.skill_id';

		$criteria->group = "";

No exception this time but now in my CGridView all my columns are empty except ‘success rate’

My view looks like this:

$this->widget('zii.widgets.grid.CGridView', array(




									'template' => '{items}{pager}',

									'cssFile'=>Yii::app()->request->baseUrl. '/themes/'. Yii::app()->theme->name.'/css/table.css',

									'htmlOptions'=>array('class'=>'datagrid', 'style'=>'width:740px;'),




													'header' => Yii::t('MainTrans', 'Name '),


													'filter'=>CHtml::tag('div', array('class'=>'input_background'), CHtml::activeTextField($model, 'name')),






													'htmlOptions'=>array('style'=>'width: 60px;'),

													'header' => Yii::t('MainTrans', 'Value'),

													'filter'=>CHtml::tag('div', array('class'=>'input_background'), CHtml::activeTextField($model, 'value')),




													'header' => Yii::t('MainTrans', 'Global Success rate'),

													'htmlOptions'=>array('style'=>'width: 150px;'),

													'filter'=>CHtml::tag('div', array('class'=>'input_background'), CHtml::activeTextField($model, 'successRate')),

													//'value' => '$data->getGlobalSuccessRate()."%"',





													'htmlOptions'=>array('style'=>'width: 50px;'),

													'header' => Yii::t('MainTrans', 'Hierarchy'),

													'filter'=>CHtml::tag('div', array('class'=>'input_background'), CHtml::activeTextField($model, 'hierarchy')),





													'htmlOptions'=>array('style'=>'width: 40px;'),



															'deleteSkill' => array


																'label'=>Yii::t('MainTrans', 'Delete'),


																'url'=>'Yii::app()->createUrl("site/deleteSkill", array("id"=>$data->id))',

																'options' => array('confirm' => 'Do you really want to delete this problem?'),


															'editSkill' => array


																'label'=>Yii::t('MainTrans', 'Edit'),


																'url'=>'Yii::app()->createUrl("site/newSkill", array("id"=>$data->id))',






Plus filtering and sorting is still not working.

I can’t comment on the filtering and sorting, but you need to add the columns into your select query using the * wildcard:

$criteria->select = array(

    'IFNULL(Round(((SUM(ROUND((student_skills.value/t.value)*100,0)))/(select COUNT(*) from user)),0),0) as successRate',




Thanks, your solution worked. Now all columns are displaying all values correctly.

But filtering column value in my CGridView I get this exception:

Column 'value' in where clause is ambiguous. The SQL statement executed was: SELECT COUNT(*) 

FROM (SELECT IFNULL(Round(((SUM(ROUND((student_skills.value/t.value)*100,0)))/(select COUNT(*) from user)),0),0) as successRate, 

`t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`, `t`.`value` AS `t0_c2`, `t`.`hierarchy` AS `t0_c3`, 

`problem_skill`.`id_problem` AS `t1_c0`, `problem_skill`.`id_skill` AS `t1_c1` FROM `skill` `t` 

left JOIN student_skills on = student_skills.skill_id LEFT OUTER JOIN `problem_skill` `problem_skill` 

ON (`problem_skill`.`id_skill`=`t`.`id`)  WHERE (value=:ycp0) GROUP BY 

I realise that this is because I have column ‘value’ in both skill and student_skills. And I could fix it by renaming one of them in db but I don’t really want to do that.

Is there any way to fix this so that filter specifies which table get value from?

Note: all other original columns (name, hierarchy) are filtered without problems.

Also sorting (don’t need filtering it anymore) is still not working so if someone could help me with that I would be very grateful.





Thanks it worked.

Now only the successRate sorting (in CGridView) that I need to get working.

Suggestions anyone? Still can’t figure it out.

you must configure ‘attributes’ in ‘sort’ section of CActiveDataProvider. You can find hints here:


Thanks, I got it working now