How To Make Custom Column Of Cgridview Filterable ?

Hi guys I have been searching for the solution to this problem from more than a week.

I saw many posts , but none of them seems to work for me.

Problem :

I have a column in CGridView which calls a function in my model and make some queries and return a value

Example:

View:




'columns'=>array(

		'UserGroupName',

                 array( 'name'=>'Attendence', 

                   'value'=>'$data->getAttendence()',

                   'header'=> 'Attendence',

                    

                   ),



Model:




 public function getAttendence(){

            //some math

            $userIDs = $this->getUserIDs();

            $totalDeliveredToThisGroup= $this->getDeliveredCount($userIDs);

            $totalSeen = $this->getSeenByCount($userIDs);

           

            if($totalDeliveredToThisGroup==0)

                  return '0%';

              else

                  return round(floatval(($totalSeen/$totalDeliveredToThisGroup)*100),2).'%';

        }




Note: I can get the values for corresponding rows in CGridView. How ever I am not able to (is there a way ?) sort or filter by this column.

Help !!!

CGridView performs sorting/filtering based on the output of ‘name’, and not ‘value’. The ‘value’ is used for display purposes only. In your case, you could get your controller to perform getAttendence() for every row and then pass the output to the ‘Attendence’ in your dataProvider.

I beg your pardon, But I dint get you. Can you please explain if you dont mind.

Note : I am able to get all the information into CGridView but not able to filter :(

Hi riyazMuhammed,

CGridView’s sorting and filtering are handled by its data provider. Your function ‘getAttendance’ does calculate the values of some CGridView’s column, but it is after the data provider has done its job of providing the array of AR objects. Sorting and filtering has been completed before ‘getAttendance’ is called.

In order to do sorting and filtering, you have to use ‘name’ to tell the data provider on what data you want to do sorting or filtering. Roughly, a ‘name’ should either be a column name of the table, or a public variable in the model (virtual attribute that has no corresponding column).

Assuming you are using CActiveDataProvider for your grid’s data provider, the first thing you have to do will be declaring a virtual attribute (e.g. ‘public $attendance’) in your model class. And then you have to modify your ‘search’ function to do the filtering and sorting using that attribute.

The following wiki is for ‘Searching and Sorting’ by relation, but it’s quite helpful. You will understand how to use a virtual attribute to achieve your goal. :)

http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview

This is how your code in the view should look like:




'columns'=>array(

                'UserGroupName',

                 array( 'name'=>'Attendence', 

                   'header'=> 'Attendence',

                   ),



Notice I’ve removed the ‘value’ from here. Here’s why:

  1. The ‘value’ is NOT used for sorting or filtering. Whatever you have in your value will be ONLY displayed in the table, nothing else.

  2. The output of name(in this case, ‘Attendence’) is used for sorting & filtering.

That’s the reason why your sorting/filtering do not work even though the right information is displayed.

What you’ll now need to do next is, set the getAttendence() to your ‘Attendance’ variable in your dataProvider. What softark said will take you in the right direction.

B

Hi Softark,

Thanks so much for the help.

But I have no problem to workout between two models having relations. But my problem is quite different. Since I could get the values from a function (not from any related model), I am bit confused about taking care of this scenario.

Hi BCR,

Thanks so much for your info.

Now I have declared a public $Attendence in my model, but how (Or when Or where) do I assign my "getAttendence()" to this variable ?

Also Softark’s information points tells how to take care of sorting / searching if you have column from a model related to “this” model.

So I hope my problem is quite different.

Really appreciate your help :)

Well, sorry for your confusion. But, as I said, I wanted to talk about a virtual attribute, rather than a relation. And I thought the wiki mentioned will be a great help to understand the usage of a virtual attribute.

Your are displaying a calculated value in a column of a CGridView, and it has no corresponding database column. You get that value using a method called ‘getAttendance’. Am I right so far? Then, when you want to sort/filter by that value, you will have to use a virtual attribute.

P.S.

After all, you have to come up with some SQL which does exactly the same calculation as your getAttendance method does. As long as you want to use CActiveDataProvider, you have to do that calculation in the database level (not in AR level) in order to sort/filter by its result. :)

Dear Ryaz

This is Seeni from chennai.

As our friend Softark suggested whatever the logic you have done to customize a field in CGrid,

You have to apply that logic at database level in the form of sql.

I tried to simulate your scenario.

Your requirements and demands may be complex.I may not be able to meet them.

Any way I hope this will help you.

I have two tables.

1.Table:group, Model:Group, attributes:id,name.

2.Table:worker, Model:Worker, attributes:[b]id,name,assigned/b,[b]completed/b,[b]g_id/b

One user belongs to a group through g_id.

Every worker is given some tasks(assigned).

How many of tasks they have completetd is known from completed.

Now we want to know the overall efficiency of a group.Of all the tasks assigned for a particular group,

we need to know ,how many have been completed.We want to dispaly it as efficiency in Group CGrid.

Group.php




class Group extends CActiveRecord

{  

     public $efficiency;        //declared a virtual property efficiency.

     public function rules()

	{

		return array(

			array('name', 'required'),

			array('name', 'length', 'max'=>64),

			array('id, name,efficiency', 'safe', 'on'=>'search'),//Made that property safe in search.

		);

	}




     public function search()

     {


         $sql="SUM(worker.completed)/SUM(worker.assigned)*100 "; //leave a space before the end of quotes.

/**

	 * We are using join directly .No need to bother about "with" or "together".

	 * We are grouping by group.id. So calculation done in $sql is applied within the group only.

	 * There is regExp check for user input. which is going to accept integer or float with prefixed operator signs.

 */

	 $criteria=new CDbCriteria;

	 $criteria->select="t.id,t.name,".$sql."AS efficiency";

         $criteria->join="LEFT JOIN worker ON worker.g_id=t.id";

     

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

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

	 $criteria->group='t.id';


	 if(isset($this->efficiency)&&preg_match('/^(>|<|<>|>=|<=|=|){1}(\d)*(\.)?(\d)*$/',$this->efficiency))

         {

		if(is_numeric($this->efficiency))

			$criteria->having=$sql."=".$this->efficiency;

		else $criteria->having=$sql.$this->efficiency;

	 }

		

	return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'sort'=>array("attributes"=>array("t.id","t.name","efficiency"=>array(

			'asc'=>$sql."ASC",

			'desc'=>$sql."DESC",

			

			)),

		)));

    }



views/group/admin.php




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

	'id'=>'group-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model

	'columns'=>array(

		'id',

		'name',

		'efficiency',

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>



I may be doing very simple thing. Situation in your hand may be complex.

Any way we can customize the situation playing with sql and also passing parameters to search function.

Regards.

Most of the time anyone who is comfortable with php rather than sql like me , things can be easily done

by CArrayDataProvider.But many may question the robustness of CArrayDataProvider, when dealing with large amount of data.

I initially planned to respond to this thread implementing CArrayDataProvider in the example I mentioned in the previous thread.

But then dropped.

Anyway I want to share the code.

Group.php




class Group extends CActiveRecord

{  

     public $efficiency;        //declared a virtual property efficiency.

     public function rules()

        {

                return array(

                        array('name', 'required'),

                        array('name', 'length', 'max'=>64),

                        array('id, name,efficiency', 'safe', 'on'=>'search'),//Made that property safe in search.

                );

    }


    public function search()

    {

	$criteria=new CDbCriteria;

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

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

	$objects=Group::model()->findAll($criteria);


	if(isset($this->efficiency) &&  preg_match('/^(>|<|<>|>=|<=|=|){1}(\d)*(\.)?(\d)*$/',$this->efficiency))

	    $objects=self::filterEfficiency($objects,$this->efficiency);


	return new CArrayDataProvider($objects, array(

		'sort'=>array('attributes'=>array("id",'name','efficiency'=>array(

		'asc'=>'efficiency ASC',

		'desc'=>'efficiency DESC',

		)))

		));

	}


    public function getEfficiency()

    {

	$assigned=0;

	$completed=0;

	foreach($this->workers as $worker) //using relations

        {

		$assigned+=$worker->assigned;

		$completed+=$worker->completed;

        }

	return ($assigned>0)?$completed/$assigned*100:null;

    }


    public function afterFind()

    {

	$this->efficiency=$this->getEfficiency();

	return parent::afterFind();		

    }


    public static function filterEfficiency($objects,$search)

   {

	$arr=array();

	foreach($objects as $object)

        {

	    if(is_numeric($search) || substr($search,0,1)=="=")

	        $expression=$object->getEfficiency()."==".$search;

	    else $expression=$object->getEfficiency().$search;

	    eval('$result=('.$expression.');'); //here using the devil eval .I could not make use of CComponent::evaluateExpression.

	    if($result)

		$arr[]=$object;

        }

	return $arr;

   }



views/group/admin.php




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

	'id'=>'group-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

		'id',

		'name',

		array(

		'name'=>'efficiency',

		),

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>



Regards.

Thanks so much Softark.

But taking all my complex calculations to DB level is almost impossible or very hectic task.

Thanks so much Sreenivasan.

I think CArrayDataprovider is the only solution for my problem.

Thanks really very much again :)