Date range picker for SilverStripe model admin search

After reading this awesome post by Aaron Carlino I decided a date range filter was just what I needed to filter search results in my ModelAdmin class. Then I read the comment by Ingo Schommer and that date range widget looked pretty badass so I incorporated that instead of having two fields like Aaron's solution.

I didn't make a module, but here is (briefly) what I did to alter Aaron's code and use a date range picker for filtering search results in ModelAdmin.

Get the date range picker

First get the date range picker code and include the necessary files in your ModelAdmin extension class:

function init() {
  parent::init();
  Requirements::css('sapphire/thirdparty/jquery-ui-themes/base/jquery.ui.all.css');
  Requirements::css('sapphire/thirdparty/jquery-ui-themes/base/jquery.ui.datepicker.css');
  Requirements::css('somepath/css/libs/ui.daterangepicker.css');

  Requirements::javascript('sapphire/thirdparty/jquery-ui/jquery.ui.core.js');
  Requirements::javascript('sapphire/thirdparty/jquery-ui/jquery.ui.datepicker.js');
  Requirements::javascript('somepath/javascript/libs/daterangepicker.jquery.js');
  Requirements::javascript('somepath/javascript/yourjavascript.js');
}

The files from the date picker widget are 'ui.daterangepicker.css' and 'daterangepicker.jquery.js'. You attach the date range picker in 'yourjavascript.js' using something like:

(function($) {
    $(document).ready(function() {
      //Date picker
      $('#Form_SearchForm_SomeObject_Created').daterangepicker({
        arrows: false,
        dateFormat: 'yy-m-d'
      });
})(jQuery);

The field with ID #Form_SearchForm_SomeObject_Created doesn't exist in the search form fields yet, to add that and the date range search context you need to edit the class for the DataObject that is being managed by ModelAdmin.

So, to borrow the code from Aaron's tutorial:

class SomeObject extends DataObject {

  static $searchable_fields = array (
  'Title', // example field
  'Created' => array (
    'filter' => 'DateRangeFilter'
  )
   );

  public function getDefaultSearchContext() {
  return new DateRangeSearchContext(
    $this->class,
    $this->scaffoldSearchFields(),
    $this->defaultSearchFilters()
  );
  }
}

Add the date range search context

Modify Aaron's date range search context a little bit because we don't need 2 fields for date range but rather just one text field which should have the ID #Form_SearchForm_SomeObject_Created.

Here's my modified class, its not perfect but it does the trick:

<?php
 
class DateRangeSearchContext extends SearchContext {
 
  /**
   * Replace the default form fields for the 'Created' search 
   * field with a single text field which we can use to apply 
   * jquery date range widget to.
   * 
   * @see SearchContext::getSearchFields()
   * @return FieldSet
   */
  public function getSearchFields() {
 
    $fields = ($this->fields) ? $this->fields : singleton($this->modelClass)->scaffoldSearchFields();
    if($fields) {
 
      $dates = array ();
      foreach($fields as $f) {
        $type = singleton($this->modelClass)->obj($f->Name())->class;
        if($type == "Date" || $type == "SS_Datetime") {
          $dates[] = $f;
        }
      }
 
      foreach($dates as $d) {
        $fields->removeByName($d->Name());
        $fields->push(new TextField($d->Name(), 'Date Range'));
      }
    }
    return $fields;
  }
 
  /**
   * Alter the existing SQL query object by adding some filters for the search
   * so that the query finds objects between two dates min and max
   * 
   * @see SearchContext::getQuery()
   * @return SQLQuery Query with filters applied for search
   */
  public function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null) {
 
    $query = parent::getQuery($searchParams, $sort, $limit, $existingQuery);
    $searchParamArray = (is_object($searchParams)) ?$searchParams->getVars() :$searchParams;
 
    foreach($searchParamArray as $key => $value) {
 
      if ($key == 'Created') {
 
        $filter = $this->getFilter($key);
        if ($filter && get_class($filter) == "DateRangeSearchFilter") {
 
          $filter->setModel($this->modelClass);
 
          preg_match('/([^\s]*)(\s-\s(.*))?/i', $value, $matches);
          $min_val = (isset($matches[1])) ?$matches[1] :null;
 
          $max_val = null;
          if (isset($matches[3])) {
            $max_val = date('Y-m-d', strtotime("+1 day",strtotime($matches[3])));
          }
          elseif (isset($min_val)) {
            $max_val = date('Y-m-d', strtotime("+1 day",strtotime($min_val)));
          }
 
          if($min_val && $max_val) {
            $filter->setMin($min_val);
            $filter->setMax($max_val);
            $filter->apply($query);
          }
        }
      }
    }
    return $query;
  }
 
}

Add the date range search filter

Just one little tweak to Aaron's date range search filter and you're pretty much good to go:

<?php
 
class DateRangeSearchFilter extends SearchFilter {
 
  /**
   * Minimum date
   * 
   * @var String
   */
  protected $min;
 
  /**
   * Maximum date
   * 
   * @var String
   */
  protected $max;
 
  /**
   * Setter for min date value
   * 
   * @param String $min
   */
  function setMin($min) {
    $this->min = $min;
  }
 
  /**
   * Setter for max date value
   * 
   * @param String $max
   */
  function setMax($max) {
    $this->max = $max;
  }
 
  /**
   * Apply filter query SQL to a search query
   * Date range filtering between min and max values
   * 
   * @see SearchFilter::apply()
   */
  function apply(SQLQuery $query) {
 
    if ($this->min && $this->max) {
      $query->where(sprintf(
        "%s >= '%s' AND %s < '%s'",
        $this->getDbName(),
        Convert::raw2sql($this->min),
        $this->getDbName(),
        Convert::raw2sql($this->max)
      ));
    }
  }
 
}

Sorry about the formatting of those last two pastes, I usually have my IDE set to 2 space tabs but I think those files might have a mixture of tabs and spaces now.

I don't think I've missed anything, you should now have an awesome date range filter widget that allows admin users to easily search for results from last 7 days, month to date, previous month, any date range, days before and after a date etc.

Cheers Aaron Carlino for the awesome tute and Ingo Schommer for pointing out the widget.

Get the date range picker

Add the date range form field to the search form

Add the date range search context

Add the date range search filter