filter on CGridView with CActiveDataProvider based on two table

Hi,

After reading a lot and trying a lot too, i ask your help ::)

I have a CGridView based on a CActiveDataProvider based on two tables (one is User from yii user extension):


public function actionAdmin()

	{

                $sort = new CSort;

                $sort->defaultOrder = 'username ASC';

                $sort->attributes = array(

                    'username'=>'username',

                    'email'=>'email',

                    'createtime'=>'createtime',

                    'lastvisit'=>'lastvisit',

                    'subscription start' => array(

                            'asc'=>'subscription.subscriptionstart',

                            'desc'=>'subscription.subscriptionstart DESC',

                        ),

                    'subscription end' => array(

                            'asc'=>'subscription.subscriptionend',

                            'desc'=>'subscription.subscriptionend DESC',

                        ),

                    'amount' => array(

                            'asc'=>'subscription.amount',

                            'desc'=>'subscription.amount DESC',

                        ),

                );


		$dataProvider=new CActiveDataProvider('User', array(

          

                        //'criteria' => $criteria,

                        'criteria'=>array(

                        'with'=>array('subscription:isactiv'=>array('select'=>'{{subscription}}.*','joinType'=>'LEFT OUTER JOIN',

                             ),

                           ),

                       'together'=>true,

                       ),

                  

			'pagination'=>array(

				'pageSize'=>Yii::app()->controller->module->user_page_size,

			),

                        'sort' => $sort,

		));


		$this->render('index',array(

                        'dataProvider'=>$dataProvider,

		));


               

	}


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

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

         //'filter'=>$dataProvider,

         'dataProvider'=>$dataProvider,

	'columns'=>array(

		//array(

		//	'name' => 'id',

		//	'type'=>'raw',

		//	'value' => 'CHtml::link(CHtml::encode($data->id),array("admin/update","id"=>$data->id))',

		//),

		array(

			'name' => 'username',

			'type'=>'raw',

			'value' => 'CHtml::link(CHtml::encode($data->username),array("admin/view","id"=>$data->id))',

		),

		array(

			'name'=>'email',

			'type'=>'raw',

			'value'=>'CHtml::link(CHtml::encode($data->email), "mailto:".$data->email)',

		),

		array(

			'name' => 'createtime',

			'value' => 'date("d.m.Y",$data->createtime)',

		),


		array(

			'name' => 'lastvisit',

			'value' => '(($data->lastvisit)?date("d.m.Y",$data->lastvisit):UserModule::t("Not visited"))',

		),


            array(

                'name' => 'subscription start',

                'value' => 'isset($data->subscription->subscriptionstart)? date("d.m.Y",$data->subscription->subscriptionstart) : \'\'',

	    	),


             array(

                'name' => 'subscription end',

                'value' => 'isset($data->subscription->subscriptionend)? date("d.m.Y",$data->subscription->subscriptionend) : \'\'',

	    	),


             array(

                'name' => 'amount',

                'value' => 'isset($data->subscription->amount)? $data->subscription->amount : \'\'',

	    	),


             //array(

             //   'name' => 'activ',

              //  'value' => 'isset($data->subscription->activ)? $data->subscription->activ : \'\'',

	    //	),

		array(

			'class'=>'CButtonColumn',

		),


	),

));




?>



and it work fine (thanks antonio ;) )

Now, I would like to add a filter on the grid, and the problems begin… :(

In the user class, I add a search function :


public function search()

	{

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

		// should not be searched.

                //$model=$this->loadModel();


		$criteria=new CDbCriteria;


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

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

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


		return new CActiveDataProvider('get_class($this)', array(

			'criteria'=>$criteria,

		));

	}

First question, I must replace ‘get_class($this)’ by 'User whereas It don’t understand !, why ?

in my controller :


public function actionAdmin()

	{

            //$model=$this->loadModel();

            //$search = User::model()->search();

            

            $model = new User('search');

            // HERE GET YOUR SEARCH PARAMETERS IF ANY

            $model->unsetAttributes();


            //if(Yii::app()->getRequest()->getParam('name')) $model->name = Yii::app()->getRequest()->getParam('name');

            

                $sort = new CSort;

                $sort->defaultOrder = 'username ASC';

                $sort->attributes = array(

                    'username'=>'username',

                    'email'=>'email',

                    'createtime'=>'createtime',

                    'lastvisit'=>'lastvisit',

                    'subscription start' => array(

                            'asc'=>'subscription.subscriptionstart',

                            'desc'=>'subscription.subscriptionstart DESC',

                        ),

                    'subscription end' => array(

                            'asc'=>'subscription.subscriptionend',

                            'desc'=>'subscription.subscriptionend DESC',

                        ),

                    'amount' => array(

                            'asc'=>'subscription.amount',

                            'desc'=>'subscription.amount DESC',

                        ),

                );


        //$criteria = new CDbCriteria;

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

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

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

        //$criteria->compare('subscription.subscriptionstart', 'subscription.subscriptionstart', true);

        //$criteria->compare('subscription.subscriptionend', 'subscription.subscriptionend', true);

        //$criteria->compare('subscription.amount', 'subscription.amount', true);

        //$criteria->with = array('subscription:isactiv'=>array('select'=>'{{subscription}}.*','joinType'=>'LEFT OUTER JOIN',

        //                     ),

        //                   );

        //$criteria->together = true;




		$dataProvider=new CActiveDataProvider($model, array(

          

                        //'criteria' => $criteria,

                        'criteria'=>array(

                        'with'=>array('subscription:isactiv'=>array('select'=>'{{subscription}}.*','joinType'=>'LEFT OUTER JOIN',

                             ),

                           ),

                       'together'=>true,

                       ),

                  

			'pagination'=>array(

				'pageSize'=>Yii::app()->controller->module->user_page_size,

			),

                        'sort' => $sort,




                    //'pagination'=>array( 'pageSize'=>30),

		));


		$this->render('index',array(

			'model'=>$model,

                        'dataProvider'=>$dataProvider,

		));


               

	}

and in my view :


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

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

         'filter'=>$dataProvider,

         //'dataProvider'=>$dataProvider,

	'columns'=>array(

		//array(

		//	'name' => 'id',

		//	'type'=>'raw',

		//	'value' => 'CHtml::link(CHtml::encode($data->id),array("admin/update","id"=>$data->id))',

		//),

		array(

			'name' => 'username',

			'type'=>'raw',

			'value' => 'CHtml::link(CHtml::encode($data->username),array("admin/view","id"=>$data->id))',

		),

		array(

			'name'=>'email',

			'type'=>'raw',

			'value'=>'CHtml::link(CHtml::encode($data->email), "mailto:".$data->email)',

		),

		array(

			'name' => 'createtime',

			'value' => 'date("d.m.Y",$data->createtime)',

		),


		array(

			'name' => 'lastvisit',

			'value' => '(($data->lastvisit)?date("d.m.Y",$data->lastvisit):UserModule::t("Not visited"))',

		),

		//array(

		//	'name'=>'status',

		//	'value'=>'User::itemAlias("UserStatus",$data->status)',

		//),

		//array(

		//	'name'=>'superuser',

		//	'value'=>'User::itemAlias("AdminStatus",$data->superuser)',

		//),




            array(

                'name' => 'subscription start',

                'value' => 'isset($data->subscription->subscriptionstart)? date("d.m.Y",$data->subscription->subscriptionstart) : \'\'',

	    	),


             array(

                'name' => 'subscription end',

                'value' => 'isset($data->subscription->subscriptionend)? date("d.m.Y",$data->subscription->subscriptionend) : \'\'',

	    	),


             array(

                'name' => 'amount',

                'value' => 'isset($data->subscription->amount)? $data->subscription->amount : \'\'',

	    	),


             //array(

             //   'name' => 'activ',

              //  'value' => 'isset($data->subscription->activ)? $data->subscription->activ : \'\'',

	    //	),

		array(

			'class'=>'CButtonColumn',

		),


	),

));




?>



…and it don’t works at all…i hope i’m not really far away from the solution but i don(t find and i turn around, so if you could help me.

Thanks a lot

You are doing lot of work in the controller, better to move all a bit:

action admin:




public function actionAdmin()

        {

            //$model=$this->loadModel();

            //$search = User::model()->search();

            

            $model = new User('search');

            // HERE GET YOUR SEARCH PARAMETERS IF ANY

            $model->unsetAttributes();


              $this->render('index',array(

                        'model'=>$model,

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

                ));


               

        }




in the model:




public function search()

        {


                $criteria=new CDbCriteria;


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

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

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

               

                $criteria->with =>array('subscription:isactiv'=>array('select'=>'{{subscription}}.*','joinType'=>'LEFT OUTER JOIN',

                             ),

                           ),

                       'together'=>true,

                       ),




$sort = new CSort;

                $sort->defaultOrder = 'username ASC';

                $sort->attributes = array(

                    'username'=>'username',

                    'email'=>'email',

                    'createtime'=>'createtime',

                    'lastvisit'=>'lastvisit',

                    'subscription start' => array(

                            'asc'=>'subscription.subscriptionstart',

                            'desc'=>'subscription.subscriptionstart DESC',

                        ),

                    'subscription end' => array(

                            'asc'=>'subscription.subscriptionend',

                            'desc'=>'subscription.subscriptionend DESC',

                        ),

                    'amount' => array(

                            'asc'=>'subscription.amount',

                            'desc'=>'subscription.amount DESC',

                        ),

                );




                return new CActiveDataProvider('get_class($this)', array(

                        'criteria'=>$criteria,

                         'sort'=>$sort

                ));

        }




So, after moving the code like that all search operation are done in the search function of the model.

Now, if you have to do some more search on other fields, maybe of related table, add a new parameter in the model, add to safe attributes and write a new line like $criteria->compare(‘newRelatedParameter’, $this->newRelatedParameter, true); in the search function.

Thanks a lot zaccaria

Now :


 public function search()

        {


                $criteria=new CDbCriteria;


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

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

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

                //$criteria->compare('subscription.subscriptionstart', $this->subscription->subscriptionstart, true);

                $criteria->with = array('subscription:isactiv'=>array('select'=>'{{subscription}}.*','joinType'=>'LEFT OUTER JOIN',

                             ),

                           );

                $criteria->together = true;




                $sort = new CSort;

                $sort->defaultOrder = 'username ASC';

                $sort->attributes = array(

                    'username'=>'username',

                    'email'=>'email',

                    'createtime'=>'createtime',

                    'lastvisit'=>'lastvisit',

                    'subscription start' => array(

                            'asc'=>'subscription.subscriptionstart',

                            'desc'=>'subscription.subscriptionstart DESC',

                        ),

                    'subscription end' => array(

                            'asc'=>'subscription.subscriptionend',

                            'desc'=>'subscription.subscriptionend DESC',

                        ),

                    'amount' => array(

                            'asc'=>'subscription.amount',

                            'desc'=>'subscription.amount DESC',

                        ),

                );




                return new CActiveDataProvider('User', array(

                        'criteria'=>$criteria,

                         'sort'=>$sort

                ));

        }


public function actionAdmin()

        {

            //$model=$this->loadModel();

            //$search = User::model()->search();


            $model = new User('search');

            // HERE GET YOUR SEARCH PARAMETERS IF ANY

            $model->unsetAttributes();


              $this->render('index',array(

                        'model'=>$model,

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

                ));




        }


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

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

         'filter'=>$model,

         //'dataProvider'=>$dataProvider,

	'columns'=>array(

		//array(

		//	'name' => 'id',

		//	'type'=>'raw',

		//	'value' => 'CHtml::link(CHtml::encode($data->id),array("admin/update","id"=>$data->id))',

		//),

		array(

			'name' => 'username',

			'type'=>'raw',

			'value' => 'CHtml::link(CHtml::encode($data->username),array("admin/view","id"=>$data->id))',

		),

		array(

			'name'=>'email',

			'type'=>'raw',

			'value'=>'CHtml::link(CHtml::encode($data->email), "mailto:".$data->email)',

		),

		array(

			'name' => 'createtime',

			'value' => 'date("d.m.Y",$data->createtime)',

		),


		array(

			'name' => 'lastvisit',

			'value' => '(($data->lastvisit)?date("d.m.Y",$data->lastvisit):UserModule::t("Not visited"))',

		),

		//array(

		//	'name'=>'status',

		//	'value'=>'User::itemAlias("UserStatus",$data->status)',

		//),

		//array(

		//	'name'=>'superuser',

		//	'value'=>'User::itemAlias("AdminStatus",$data->superuser)',

		//),




            array(

                'name' => 'subscription.subscriptionstart',

                'value' => 'isset($data->subscription->subscriptionstart)? date("d.m.Y",$data->subscription->subscriptionstart) : \'\'',

	    	),


             array(

                'name' => 'subscription.subscriptionend',

                'value' => 'isset($data->subscription->subscriptionend)? date("d.m.Y",$data->subscription->subscriptionend) : \'\'',

	    	),


             array(

                'name' => 'subscription.amount',

                'value' => 'isset($data->subscription->amount)? $data->subscription->amount : \'\'',

	    	),


             //array(

             //   'name' => 'activ',

              //  'value' => 'isset($data->subscription->activ)? $data->subscription->activ : \'\'',

	    //	),

		array(

			'class'=>'CButtonColumn',

		),


	),

));




?>

and I have the filter behind the grid :)

BUT…the filter not work !

And a try to add this $criteria->compare(‘subscription.subscriptionstart’, $this->subscription->subscriptionstart, true); in the search function and it reply : Trying to get property of non-object

Thanks a lot

Referene following document:

http://www.yiiframework.com/doc/api/1.1/CSort#attributes-detail

Your problem is: you don’t create virtual attribute for column of table 2 which you want to sort/filter.

Example:

  • add ‘subscriptionStart’ new property for User class.

  • in model we have filter by:

$criteria->compare(‘subscription.subscriptionstart’, $this->subscriptionStart);

  • in views:

array(

‘name’ => ‘subscriptionStart’,

‘value’ => ‘isset($data->subscription->subscriptionstart)? date(“d.m.Y”,$data->subscription->subscriptionstart) : \’\’’,

),

Do same for another columns of table 2, table 3…

Thanks a lot.

But really a try to find on the forum but i don’t find what a want…can you help me to add property for user class ?

I see that, you get data from User model and left join with ‘subscription’, you want filter/sort ‘subscription.subscriptionstart’.

OK, in User model class create new property ‘subscriptionStart’(or another name if you want)

class User extend CActiveRecord {

public $subscriptionStart;

}

and now, in search function of User model we have:

$criteria->compare(‘subscription.subscriptionstart’, $this->subscriptionStart);

and in view file, we have

array(

‘header’=>‘Subscription Start’,//or define in function attributeLabels of User model

‘name’ => ‘subscriptionStart’,

‘value’ => ‘isset($data->subscription->subscriptionstart)? date(“d.m.Y”,$data->subscription->subscriptionstart) : \’\’’,

),

Yes, it’s work for the presentation, thanks a lot… :)

but I don’t know why, the filter don’t work… :( have you an idea ?

Excuse me, but no one has an idea ? :(

Thanks

You just unset the attributes, but you don’t set them at requested values from filter.


$model->unsetAttributes();

It should be something like this:




$model->unsetAttributes();

if(isset($_GET['User']))

    $model->attributes=$_GET['User'];

guil182

Any luck with your approach? I’d like to know how you got your filters to work properly.

I have the same problem, but in my case I am trying to filter only one table using a CActiveDataProvider instead of the $model variable (AR) that comes from the controller.

You can see my case here:

http://www.yiiframework.com/forum/index.php/topic/28925-setting-cgridview-page-size/page__view__findpost__p__139186

I was taking a look to this as well:

http://www.yiiframework.com/forum/index.php/topic/14302-using-cactivedataprovider-in-cgridview-with-supporting-filtering/page__view__findpost__p__70850

But I haven’t implemented this solution as yet. I will try it tomorrow to see if it works for me.

In model search function 'please look bold fields







	[b]	[/b][b]$sort = new CSort;[/b]

[b]                $sort->defaultOrder = 'date_time DESC';[/b]


		$criteria=new CDbCriteria;


	

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

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

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




		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria, 

                        [b]'sort'=>$sort,[/b]

		));