Yii 1.1: Adding a date range search for CGridView the easy way

10 followers

Introduction

After a lot of research, everything I found on adding date range searching to a CGridView advanced search form seemed to involve adding two new public variables (e.g. $date_from, $date_to), 'safe' rules for the new variables, and a rather chunky if/elseif/else check in the search() method. This probably isn't a hassle for most, but because many of the tables in my database contain two or three and sometimes four date columns (e.g. date_created, date_modified, date_deleted etc.), it meant I was having to add up to eight public variables, the corresponding safe rules, and modifying the search() criteria for each date attribute. So, I set about creating a better way and I thought I'd share my work with the community.

Overview

To be able to add date range searching functionality to your advanced search forms (not grid view filters sorry, I'm still working on that!) without having to add public attributes, 'safe' rules, and chunky search conditions for each date attribute.

  1. Create an Active Record Behavior that builds the criteria for the search() method and attach it to your model
  2. Modify the search() method of the model to merge the behavior's criteria in with the rest of the searchable attributes
  3. Modify the _search form view to add the date range inputs

Assumptions

  1. You already have a functioning advanced search form for your grid view that contains one or more date inputs.
  2. Your database date is in the MySQL format 'yyyy-mm-dd'. If not, change the $dateFromDefault and $dateToDefault values in the behavior.

Behavior

Create a file called EDateRangeSearchBehavior.php. Copy the following code in to the file and save it in to your components/behaviors folder.

/**
 * This model behavior builds a date range search condition.
 */
class EDateRangeSearchBehavior extends CActiveRecordBehavior
{
 
    /**
     * @param the default 'from' date when nothing is entered.
     */
    public $dateFromDefault = '1900-01-01';
 
    /**
     * @param the default 'to' date when nothing is entered.
     */
    public $dateToDefault = '2099-12-31';
 
    /*
     * Date range search criteria
     * public $attribute name of the date attribute
     * public $value value of the date attribute
     * @return instance of CDbCriteria for the model's search() method
     */
    public function dateRangeSearchCriteria($attribute, $value)
    {
        // Create a new db criteria instance
        $criteria = new CDbCriteria;
 
        // Check if attribute value is an array
        if (is_array($value))
        {
            // Check if either key in the array has a value
            if (!empty($value[0]) || !empty($value[1]))
            {
                // Set the date 'from' variable to the first value in the array
                $dateFrom = $value[0];
                if (empty($dateFrom))
                {
                    // Set the 'from' date to the default
                    $dateFrom = $this->dateFromDefault;
                }
 
                // Set the date 'to' variable to the second value in the array
                $dateTo = $value[1];
                if (empty($dateTo))
                {
                    // Set the 'to' date to the default
                    $dateTo = $this->dateToDefault;
                }
 
                // Check if the 'from' date is greater than the 'to' date
                if ($dateFrom > $dateTo)
                {
                    // Swap the dates around
                    list($dateFrom, $dateTo) = array($dateTo, $dateFrom);
                }
 
                // Add a BETWEEN condition to the search criteria
                $criteria->addBetweenCondition($attribute, $dateFrom, $dateTo);
            }
            else
            {
                // The value array is empty so set it to an empty string
                $value = '';
 
                // Add a compare condition to the search criteria
                $criteria->compare($attribute, $value, true);
            }
        }
        else
        {
            // Add a standard compare condition to the search criteria
            $criteria->compare($attribute, $value, true);
        }
 
        // Return the search criteria to merge with the model's search() method
        return $criteria;
    }
 
}

Model

Then, attach the behavior by adding it to the behaviors() array in your model

/**
 * Model behaviors
 */
public function behaviors()
{
    return array(
        'dateRangeSearch'=>array(
            'class'=>'application.components.behaviors.EDateRangeSearchBehavior',
        ),
    );
}

Next, modify the search() method in the same model and replace the $criteria->compare() line for every date attribute you want to range search.

public function search()
{
    $criteria = new CDbCriteria;
 
    $criteria->compare('id', $this->id);
    ...
 
    // Replace the standard date compare line...
    $criteria->compare('date_created', $this->date_created, true);
 
    // with the new mergeWith line...
    $criteria->mergeWith($this->dateRangeSearchCriteria('date_created', $this->date_created));  
    ...
 
    return new CActiveDataProvider($this, array(
        'criteria'=>$criteria,
        ));
}

View

And finally in your view, add the following code to your _search form to render your 'date from' and 'date to' inputs.

<?php
// Date range search inputs
$attribute = 'date_created';
for ($i = 0; $i <= 1; $i++)
{
    echo ($i == 0 ? Yii::t('main', 'From:') : Yii::t('main', 'To:'));
    $this->widget('zii.widgets.jui.CJuiDatePicker', array(
        'id'=>CHtml::activeId($model, $attribute.'_'.$i),
        'model'=>$model,
        'attribute'=>$attribute."[$i]",
    )); 
}
?>

PLEASE NOTE: If you currently have a column in your grid view that is displaying the same date attribute you are range-searching on, make sure you set the 'filter' setting to false. (thank you to yiimike for this tip!) I.e.

<?php
$this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'my-grid',
    'dataProvider'=>$dataProvider,
    'columns'=>array(
        'id',
        ...
        array(
            'name'=>'date_created',
            'value'=>"Yii::app()->dateFormatter->formatDateTime(\$data->date_created, 'medium', 'short')",
            'filter'=>false, // Set the filter to false when date range searching
        ),
        ...
    ),
)); 
?>

Summary

  • The View code will generate two search inputs, each having its own CJuiDatePicker widget. The two inputs will submit the data under the attribute name as an array, where the EDateRangeSearchBehavior attached to the model will create a BETWEEN condition.
  • Obviously, if the two date inputs are filled in, the search will find models between the two entered dates.
  • If only the first date input is filled in, the search will find models between the entered date and the '$dateToDefault' date set in the behavior.
  • If only the second date input is filled in, the search will find models between the '$dateFromDefault' date set in the behavior, and the entered date.
  • If, for example, you have an admin search form and a user search form that use the same model, and you only require date range functionality for the admin search form, you are still able to use a single date input. The behavior checks if the attribute submitted is an array (i.e. a date range), and if not it will revert to a standard 'compare' criteria.

Conclusion

I hope this works well for you. In my case, I actually use this for datetime/timestamp fields, and with some simple modifications (i.e. add the time to the $dateFromDefault and $dateToDefault values in the behavior, and change the view widget to a DateTimePicker) you can adapt it to your needs too. Also, if you have any enhancements, I'd love to hear.

Total 4 comments

#16963 report it
xJose at 2014/04/16 03:30pm
thanks

it really helped me a lot, I hope we get news from the enhancement to use this behavior in the grid.

#13775 report it
CrazyCat at 2013/06/25 04:55am
When DB date format is different

Edit : added a way to manage with timestamp in DB.

This tip really usefull, but I had to modify it. In my DB (SQLite), I've the datetime of events, and I wanted to search by date only. So, I add 2 variables: the date format in UI and the date format in DB. And small functions to convert the dates. Note that I'm with a system with PHP 5.2, so I can't use the DateTime::createFromFormat function and my formats use the strftime() format and not the date() one.

<?php
/**
 * This model behavior builds a date range search condition.
 */
class EDateRangeSearchBehavior extends CActiveRecordBehavior
{
 
    /**
     * @param the default 'from' date when nothing is entered.
     */
    public $dateFromDefault = '2013-01-01 00:00:00';
 
    /**
     * @param the default 'to' date when nothing is entered.
     */
    public $dateToDefault = '2099-12-31 00:00:00';
 
    /**
     * @param the default input date format
     */
    public $dateInput = '%d/%m/%Y';
 
    /**
     * @param the default db date format
     */
    public $dateDb = '%Y-%m-%d %H:%M:%S';
 
    /**
     * Makes a timestamp from a date and a format
     * @param string $strptimeFormat strftime format
     * @param string $date date to parse
     * @return timestamp
     */
    private function date_create_from_strfimeformat($format, $date) {
        $arrDate = strptime($date, $format);
        if (!$arrDate) return 0;
        $arrDate['tm_year'] += 1900;
        $arrDate['tm_mon']++;
        $ts = mktime($arrDate['tm_hour'], $arrDate['tm_min'], $arrDate['tm_sec'], $arrDate['tm_mon'], $arrDate['tm_mday'], $arrDate['tm_year']);
        return $ts;
    }
 
    /**
     * Transforms an input (IHM) formated date
     * into an output (DB) formated date
     * @param string $in the IHM date
     * @return $string the DB date
     */
    private function formatDate($in) {
        $ts = $this->date_create_from_strfimeformat($this->dateInput, $in);
        if ($this->dateDb!='')
            $out = strftime($this->dateDb, $ts);
        else
            $out = $ts;
        return $out;
    }
 
    /*
     * Date range search criteria
     * public $attribute name of the date attribute
     * public $value value of the date attribute
     * @return instance of CDbCriteria for the model's search() method
     */
    public function dateRangeSearchCriteria($attribute, $value)
    {
        // Create a new db criteria instance
        $criteria = new CDbCriteria;
 
        // Check if attribute value is an array
        if (is_array($value))
        {
            // Check if either key in the array has a value
            if (!empty($value[0]) || !empty($value[1]))
            {
                // Set the date 'from' variable to the first value in the array
                $dateFrom = $value[0];
                if (empty($dateFrom))
                {
                    // Set the 'from' date to the default
                    $dateFrom = $this->dateFromDefault;
                }
                else
                {
                    $dateFrom = $this->formatDate($dateFrom);
                }
                 // Set the date 'to' variable to the second value in the array
                $dateTo = $value[1];
                if (empty($dateTo))
                {
                    // Set the 'to' date to the default
                    $dateTo = $this->dateToDefault;
                }
                else
                {
                    $dateTo = $this->formatDate($dateTo);
                }
                // Check if the 'from' date is greater than the 'to' date
                if ($dateFrom > $dateTo)
                {
                    // Swap the dates around
                    list($dateFrom, $dateTo) = array($dateTo, $dateFrom);
                }
                if (strlen($dateTo)==19)
                {
                    $dateTo = str_replace('00:00:00', '23:59:59', $dateTo);
                }
                elseif ($this->dateDb=='')
                {
                    $dateTo += (23*3600)+(59*60)+59;
                }
                // Add a BETWEEN condition to the search criteria
                $criteria->addBetweenCondition($attribute, $dateFrom, $dateTo);
            }
            else
            {
                // The value array is empty so set it to an empty string
                $value = '';
                // Add a compare condition to the search criteria
                $criteria->compare($attribute, $value, true);
            }
        }
        else
        {
            // Add a standard compare condition to the search criteria
            $criteria->compare($attribute, $value, true);
        }
        // Return the search criteria to merge with the model's search() method
        return $criteria;
    }
}

You can pass the dates formats using this syntax:

public function behaviors()
{
   return array(
      'dateRangeSearch'=>array(
         'class'=>'application.components.behaviors.EDateRangeSearchBehavior',
         'dateDb' => '%Y-%m-%d %H:%M:%S',
         'dateInput' => '%d/%m/%Y'
      ),
   );
}

If $dateDb is an empty string, it assumes the field is an unix timestamp.

Hope it'll help :)

#13029 report it
yiimike at 2013/04/29 08:09am
thanks

Thanks for the post! Also just remember to set filter to false in gridview since it'll be an array not value, ( ie 'filter'=>false for the CGridView column)

#12639 report it
SomethingWicked at 2013/04/03 03:37am
thanks for the wiki

I'm more interested in the grid "version" filter, but as you said you're working on that. I'll wait to check you solution :)

Leave a comment

Please to leave your comment.

Write new article