Filter Date Range on CGridView ToolBar


(Riza Nurhadi) #1

after few hours searching for how to create date range filter using datepicker on CGridView toolbar. there is no specific tutorial or thread on how to create it or maybe I’m not searching long enough.

i found one good thread it’s here http://www.yiiframework.com/forum/index.php?/topic/9322-filter-cgridview-with-datestart-and-dateend/

anyway i think i want to create my own version on how to do filter date using date range on cgridview searchbar / toolbar

so here goes nothing :

first lets modified the cgridview :




<?php 

// this is the date picker

$dateisOn = $this->widget('zii.widgets.jui.CJuiDatePicker', array(

					// 'model'=>$model,

				    'name' => 'Event[date_first]',

				    'language' => 'id',

					'value' => $model->date_first,

				    // additional javascript options for the date picker plugin

				    'options'=>array(

					'showAnim'=>'fold',

					'dateFormat'=>'yy-mm-dd',

					'changeMonth' => 'true',

					'changeYear'=>'true',

					'constrainInput' => 'false',

				    ),

				    'htmlOptions'=>array(

					'style'=>'height:20px;width:70px;',

				    ),

// DONT FORGET TO ADD TRUE this will create the datepicker return as string

				),true) . '<br> To <br> ' . $this->widget('zii.widgets.jui.CJuiDatePicker', array(

					// 'model'=>$model,

				    'name' => 'Event[date_last]',

				    'language' => 'id',

					'value' => $model->date_last,

				    // additional javascript options for the date picker plugin

				    'options'=>array(

					'showAnim'=>'fold',

					'dateFormat'=>'yy-mm-dd',

					'changeMonth' => 'true',

					'changeYear'=>'true',

					'constrainInput' => 'false',

				    ),

				    'htmlOptions'=>array(

					'style'=>'height:20px;width:70px',

				    ),

// DONT FORGET TO ADD TRUE this will create the datepicker return as string

				),true);


?>


<?php 

	

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

	'id'=>'event-grid',

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

// DONT FORGET TO TURN ON afterAjaxUpdate Or after the first search the Datepicker won't Run

	'afterAjaxUpdate'=>"function() {

						jQuery('#Event_date_first').datepicker(jQuery.extend({showMonthAfterYear:false}, jQuery.datepicker.regional['id'], {'showAnim':'fold','dateFormat':'yy-mm-dd','changeMonth':'true','showButtonPanel':'true','changeYear':'true','constrainInput':'false'}));

						jQuery('#Event_date_last').datepicker(jQuery.extend({showMonthAfterYear:false}, jQuery.datepicker.regional['id'], {'showAnim':'fold','dateFormat':'yy-mm-dd','changeMonth':'true','showButtonPanel':'true','changeYear':'true','constrainInput':'false'}));

						}",

	'filter'=>$model,

	'columns'=>array(

		'id',

		'publish_on',

		'publish_off',

		//'publish',

		 array(

		'name'=>'publish',

		'filter'=>getYesNo(),

		'value'=>'getYesNoText($data->publish)'

		), 

		 array(

		'name'=>'date',

		'filter'=>$dateisOn,

		'value'=>'$data->date'

		), 

		 array(

		'name'=>'id_category',

		'filter'=>CHtml::listData( CategoryLang::model()->findAllByAttributes(array("lang"=>"en")), 'id_category', 'name'),

		'value'=>'CategoryLang::model()->findByAttributes(array("lang"=>"en","id_category"=>$data->id))->name'

		), 

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>



Note : don’t forget to add the afterAjaxUpdate. the code on afterAjaxUpdate actually already generated by the cjuidatepicker. if you create the datepicker and view the page source you’ll see the code at the bottom of page. I’m just paste the code into afterAjaxUpdate.

thanks to ClaCS for afterAjaxUpdate. you can read here http://www.yiiframework.com/forum/index.php?/topic/14377-search-cgridview-related-tables-dates-and-datepicker/

after you’ve done with this lets modified the model Class :

declare the date_first and date_last




class Event extends CActiveRecord

{


	public $date_first;

	public $date_last;



add date_first and date_last on method rules() :




public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('publish', 'numerical', 'integerOnly'=>true),

			array('publish_on, publish_off,date', 'safe'),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('id, publish_on, publish_off, publish,id_category,date,date_first,date_last', 'safe', 'on'=>'search'),

		);

	}



change the search method so the date will be search using BETWEEN sql statement.




public function search()

	{

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

		// should not be searched.


		$criteria=new CDbCriteria;




		if((isset($this->date_first) && trim($this->date_first) != "") && (isset($this->date_last) && trim($this->date_last) != ""))

			$criteria->addBetweenCondition('date', ''.$this->date_first.'', ''.$this->date_last.'');

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

			'criteria'=>$criteria,

		));

	}



so the query should be like

where date between date_first and date_last

i hope I’m not confusing anyone because my English is not very good.


(Maectpocmpt) #2

Hi,

ty for you post, it was very useful to me and save me lots of time,

best regards.


(Mithila) #3

hi,

nice post!!!!!

thanks


(Swisscheese2122) #4

I tried using this but when I select a data with datepicker the value does not hold in the search field therefore nothing is filtered. Anyone have any ideas as to why this would be?


(Oceatoon) #5

@riza_nurhadi

Thanks for your very interesting post

I see in you CgridView you have definied your own filters

would you mind sharing them with us ?

I’m asking this because I need to build a complex filter

with some SQL searches based on dates

there isn’t much out there

do you think I can do something like ‘filter’=>getNextOccurence()

where is getNextOccurence() declared ?


(Web) #6

Great help! Thank you!


(Yiqing 95) #7

nice :lol:

useful to us , well done


(Deeptibaghel) #8

I also faced the same issue , the change as below solved the issue

$dateisOn = $this->widget(‘zii.widgets.jui.CJuiDatePicker’, array(

                                 'model'=&gt;&#036;model,


                                 'attribute' =&gt; 'date_first',


                                // additional javascript options for the date picker plugin


                                'options'=&gt;array(


                                    'showAnim'=&gt;'fold',


                                    'dateFormat'=&gt;'dd-mm-yy',


                                    'changeMonth' =&gt; 'true',


                                    'changeYear'=&gt;'true',


                                ),


                                'htmlOptions'=&gt;array(


                                    'style'=&gt;'height:20px;width:70px;',


                                ),

// DONT FORGET TO ADD TRUE this will create the datepicker return as string

                            ),true) . '  To  ' . &#036;this-&gt;widget('zii.widgets.jui.CJuiDatePicker', array(


                                'model'=&gt;&#036;model,


                               'attribute' =&gt; 'date_last',


                                 'value' =&gt; &#036;model-&gt;date_last,


                                // additional javascript options for the date picker plugin


                                'options'=&gt;array(


                                    'showAnim'=&gt;'fold',


                                    'dateFormat'=&gt;'dd-mm-yy',


                                    'changeMonth' =&gt; 'true',


                                    'changeYear'=&gt;'true',


                                    'constrainInput' =&gt; 'false',


                                ),


                                'htmlOptions'=&gt;array(


                                    'style'=&gt;'height:20px;width:70px',


                                ),

// DONT FORGET TO ADD TRUE this will create the datepicker return as string

                            ),true);

So instead of name i have used model and attribute.


(Sharmakiran71) #9

It filters between two dates,not including that the two dates

for this you can use following code in search function of model class,


if(!empty($this->from_date) && empty($this->to_date))

        {

            $criteria->condition = "date >= '$this->from_date'";  // date is database date column field

        }elseif(!empty($this->to_date) && empty($this->from_date))

        {

            $criteria->condition = "date <= '$this->to_date'";

        }elseif(!empty($this->to_date) && !empty($this->from_date))

        {

            $criteria->condition = "date  >= '$this->from_date' and date <= '$this->to_date'";

        }

You can also use following wiki article if you want to filter by button click…

Filter CGridView by FromDate , ToDate on button click


(Yanev93) #10

Hello I tried this but the problem is still there.

This is my code:


$dateisOn = $this->widget('zii.widgets.jui.CJuiDatePicker', array(

	//'model'=>$filter,

    //'name'=>'PolicyFilter[startDate]',  // name of post parameter

	'model'=>$filter,

	'attribute' => 'startDate',

	'value'=>$filter->startDate,

     'options'=>array(

        'showAnim'=>'fold',

        'dateFormat'=>'yy-mm-dd',

),

    'htmlOptions'=>array(

        'style'=>'height:20px;width:70px;'

        ),

        ), true).'	'.$this->widget('zii.widgets.jui.CJuiDatePicker', array(

	'model'=>$filter,

	'attribute' => 'endDate',

     'value'=>$filter->endDate,

     'options'=>array(

        'showAnim'=>'fold',

        'dateFormat'=>'yy-mm-dd',


        ),

    'htmlOptions'=>array(

        'style'=>'height:20px;width:70px;'

        ),

        ), true);

How can I fix this issue?


(Scjonatas) #11

Thank you very much!!!

You are the man! =)


(Cappadochian) #12

very nice, thank you!

how can we prevent loading if only the first widget is filled? I would like to make it to load only when both widgets are filled.

Thanks!

BR

c


(Ncramer) #13

I don’t want to erase anyone’s hard work, but do want to point out the new CGridView filterSelector attribute in 1.1.14 that allows including external inputs in the standard filter logic, and saves quite a bit of implementation work based on the code snippets required in this thread.

Simply reference the id of the jQuery selector (id) and it should work like magic.

Linky: http://www.yiiframework.com/doc/api/1.1/CGridView#filterSelector-detail

Cheers,

nc