Yii Framework Forum: filter on CGridView with CActiveDataProvider based on two table - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

filter on CGridView with CActiveDataProvider based on two table Filter and dataProvider Rate Topic: -----

#1 User is offline   guil182 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 07-January 11

Posted 27 January 2011 - 05:35 AM

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
0

#2 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 27 January 2011 - 09:18 AM

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
                ));
        }


1

#3 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 27 January 2011 - 09:19 AM

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.
0

#4 User is offline   guil182 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 07-January 11

Posted 27 January 2011 - 10:08 AM

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

Attached File(s)

  • Attached File  Yii.JPG (82.82K)
    Number of downloads: 224

0

#5 User is offline   oxigen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 05-February 09

Posted 28 January 2011 - 06:06 AM

Referene following document:
http://www.yiiframew...tributes-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...
0

#6 User is offline   guil182 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 07-January 11

Posted 28 January 2011 - 09:31 AM

View Postoxigen, on 28 January 2011 - 06:06 AM, said:

Referene following document:
http://www.yiiframew...tributes-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 ?
0

#7 User is offline   oxigen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 05-February 09

Posted 29 January 2011 - 09:49 AM

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) : \'\'',
),
0

#8 User is offline   guil182 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 07-January 11

Posted 29 January 2011 - 12:24 PM

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 ?

Attached File(s)

  • Attached File  Yii.jpg (145.68K)
    Number of downloads: 130

0

#9 User is offline   guil182 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 07-January 11

Posted 31 January 2011 - 11:24 AM

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

Thanks
0

#10 User is offline   edmund 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 19-January 11

Posted 18 February 2011 - 03:41 AM

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'];

1

#11 User is offline   jogasa21 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 27
  • Joined: 09-March 11
  • Location:Costa Rica / Cambodia

Posted 20 February 2012 - 04:25 AM

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.yiiframew...post__p__139186

I was taking a look to this as well:
http://www.yiiframew...dpost__p__70850

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

#12 User is offline   Nisanth thulasi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 66
  • Joined: 10-July 12
  • Location:Kochi

Posted 14 March 2013 - 08:28 AM

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]
		));





Thanks
Nisanth Thulasi
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users