Computed column and Issue with select in CDbCriteria

Yii 1.1 General Discussion
1

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;

	

		$criteria->compare('id',$this->id);

		$criteria->compare('name',$this->name,true);

		$criteria->compare('value',$this->value);

		$criteria->compare('hierarchy',$this->hierarchy);


		//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 skill.id = student_skills.skill_id group by skill.name 


		$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 skill.id = student_skills.skill_id';

		$criteria->group = "skill.name";


		$criteria->compare('successRate',$this->successRate);

	

		return new CActiveDataProvider($this, array(

				'criteria'=>$criteria,

				'pagination'=>array(

						'pageSize'=>25,

				),

				'sort'=>array(

						'defaultOrder'=>array(

								'hierarchy'=>CSort::SORT_ASC

						)

				),

		));

	}

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

2

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');
3

@Keith

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 t.id = student_skills.skill_id';

		$criteria->group = "t.name";

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(

									'id'=>'skills-grid',

									'dataProvider'=>$model->with('problem_skill')->searchWithHierarchy(),

									'filter'=>$model,

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

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

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

									'columns'=>array(

											array(

													'name'=>'name',

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

													'filterHtmlOptions'=>array('id'=>"title"),

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

													

											),

											array(

													'name'=>'value',

													'filterHtmlOptions'=>array('id'=>"max_score_filter"),

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

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

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

											),

											array(

													'name'=>'successRate',

													'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()."%"',

											),

											array(

													'name'=>'hierarchy',

													'filterHtmlOptions'=>array('id'=>"max_score_filter"),

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

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

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

											),

											array(

													'class'=>'CButtonColumn',

													'template'=>'{deleteSkill}{editSkill}',

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

													'buttons'=>array

													(

															'deleteSkill' => array

															(

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

																'imageUrl'=>Yii::app()->request->baseUrl.'/themes/'.Yii::app()->theme->name.'/images/delete.png',

																'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'),

																'imageUrl'=>Yii::app()->request->baseUrl.'/themes/'.Yii::app()->theme->name.'/images/edit.png',

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

															)

													),

											),

									),

							));

Plus filtering and sorting is still not working.

4

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

    '*',

);
5

@Kieth

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 t.id = student_skills.skill_id LEFT OUTER JOIN `problem_skill` `problem_skill` 

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

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.

6

hi

use

$criteria->compare(‘t.value’,$this->value);

7

@n-r

Thanks it worked.

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

Suggestions anyone? Still can’t figure it out.

8

you must configure ‘attributes’ in ‘sort’ section of CActiveDataProvider. You can find hints here: http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/

9

@redguy

Thanks, I got it working now