Yii Framework Forum: Filter Date Range on CGridView ToolBar - Yii Framework Forum

Jump to content

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

Filter Date Range on CGridView ToolBar Rate Topic: ***** 6 Votes

#1 User is offline   riza_nurhadi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 15
  • Joined: 23-December 10

Posted 27 June 2011 - 04:36 AM

*
POPULAR

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.yiiframew...rt-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.yiiframew...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.
11

#2 User is offline   ppopara 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 26-October 10
  • Location:Serbia

Posted 20 August 2011 - 09:19 AM

Hi,
ty for you post, it was very useful to me and save me lots of time,
best regards.
0

#3 User is offline   mithila 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 116
  • Joined: 18-April 11

Posted 30 September 2011 - 06:49 AM

hi,


nice post!!!!!

thanks
0

#4 User is offline   enfield 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 288
  • Joined: 12-January 11

Posted 31 October 2011 - 03:12 PM

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

#5 User is offline   Tibor Katelbach 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 81
  • Joined: 11-April 11
  • Location:Reunion Island

Posted 23 December 2011 - 10:12 AM

@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 ?
0

#6 User is offline   ericsko 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 07-February 12

Posted 17 March 2012 - 11:05 AM

Great help! Thank you!
0

#7 User is offline   yiqing95 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 589
  • Joined: 27-December 10
  • Location:china

Posted 13 May 2012 - 10:04 AM

nice :lol:

useful to us , well done
0

#8 User is offline   deeptibaghel 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 13-February 12

Posted 24 May 2012 - 05:40 AM

View Postenfield, on 31 October 2011 - 03:12 PM, said:

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?


I also faced the same issue , the change as below solved the issue
$dateisOn = $this->widget('zii.widgets.jui.CJuiDatePicker', array(
'model'=>$model,
'attribute' => 'date_first',
// additional javascript options for the date picker plugin
'options'=>array(
'showAnim'=>'fold',
'dateFormat'=>'dd-mm-yy',
'changeMonth' => 'true',
'changeYear'=>'true',
),
'htmlOptions'=>array(
'style'=>'height:20px;width:70px;',
),
// DONT FORGET TO ADD TRUE this will create the datepicker return as string
),true) . ' To ' . $this->widget('zii.widgets.jui.CJuiDatePicker', array(
'model'=>$model,
'attribute' => 'date_last',
'value' => $model->date_last,
// additional javascript options for the date picker plugin
'options'=>array(
'showAnim'=>'fold',
'dateFormat'=>'dd-mm-yy',
'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);

So instead of name i have used model and attribute.
0

#9 User is offline   kiran sharma 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 492
  • Joined: 21-May 11
  • Location:India

Posted 27 June 2012 - 12:05 PM

View Postriza_nurhadi, on 27 June 2011 - 04:36 AM, said:

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



....
....
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
Thanks,
Kiran Sharma.
0

#10 User is offline   Nikolay Yanev 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 23-July 12

Posted 24 July 2012 - 02:04 AM

View Postdeeptibaghel, on 24 May 2012 - 05:40 AM, said:

I also faced the same issue , the change as below solved the issue
$dateisOn = $this->widget('zii.widgets.jui.CJuiDatePicker', array(
'model'=>$model,
'attribute' => 'date_first',
// additional javascript options for the date picker plugin
'options'=>array(
'showAnim'=>'fold',
'dateFormat'=>'dd-mm-yy',
'changeMonth' => 'true',
'changeYear'=>'true',
),
'htmlOptions'=>array(
'style'=>'height:20px;width:70px;',
),
// DONT FORGET TO ADD TRUE this will create the datepicker return as string
),true) . ' To ' . $this->widget('zii.widgets.jui.CJuiDatePicker', array(
'model'=>$model,
'attribute' => 'date_last',
'value' => $model->date_last,
// additional javascript options for the date picker plugin
'options'=>array(
'showAnim'=>'fold',
'dateFormat'=>'dd-mm-yy',
'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);

So instead of name i have used model and attribute.


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

#11 User is offline   Jonatas 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 31-October 12

Posted 13 February 2013 - 01:01 PM

Thank you very much!!!
You are the man! =)
0

#12 User is offline   cappadochian 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 147
  • Joined: 02-January 11

Posted 28 June 2013 - 05:41 AM

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
0

#13 User is offline   ncramer 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 15-October 09
  • Location:Ponte Vedra Beach, FL

Posted 17 September 2013 - 03:24 PM

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.yiiframew...Selector-detail

Cheers,
nc

www.remarc.com
Simple. Elegant. Solutions.
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