Remodeling ModelAdmin, Part II (Date Range Filtering)

15 Mar

Remodeling ModelAdmin, Part II (Date Range Filtering)

The Remodel Goes On

A couple of weeks ago, we talked about breaking down some of the seemingly impassable walls of ModelAdmin to create “RemodelAdmin.” I’ve received some great feedback from SilverStripe developers and users alike, so I’ve decided to take the remodeling a step further. Today, in a decidedly shorter post, we’ll talk about how to add another arrantly lacking feature of ModelAdmin — date range filtering.

A Tough Starting Point

When I say “arrantly lacking,” that may sound slightly slanted, but I believe SilverStripe’s provision of a range filter speaks for itself:

/sapphire/search/filters/WithinRangeFilter.php

/**
 * Incomplete.
 * 
 * @todo add to tests
 * 
 * @package sapphire
 * @subpackage search
 */
class WithinRangeFilter extends SearchFilter {
	
	private $min;
	private $max;
	
	function setMin($min) {
		$this->min = $min;
	}
	
	function setMax($max) {
		$this->max = $max;
	}
	
	function apply(SQLQuery $query) {
		$query->where(sprintf(
			"%s >= %s AND %s <= %s",
			$this->getDbName(),
			Convert::raw2sql($this->min),
			$this->getDbName(),
			Convert::raw2sql($this->max)
		));
	}
	
}

It certainly doesn’t give us much to go on. To the credit of the SilverStripe team, this is actually a gnarly problem to tackle, and I can see why it was never finished. For the first time, we’re asking that a search filter be controlled by multiple form fields in the search form. To work around that, we’ll start one level above the SearchFilter, and create a custom SearchContext class. We’ll use this class to not only add the two fields (minimum and maximum), but also to adjust the search query to use their values properly.

remodeladmin/code/DateRangeSearchContext.php

class DateRangeSearchContext extends SearchContext {

	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($a = new DateField($d->Name().'_min',$d->Title()." ("._t('DateRange.START','start').")"));
				$fields->push($b = new DateField($d->Name().'_max',$d->Title()." ("._t('DateRange.END','end').")"));
				$a->setConfig('showcalendar',true);
				$b->setConfig('showcalendar',true);
				$a->setConfig('dateformat','yyyy-MM-dd');
				$b->setConfig('dateformat','yyyy-MM-dd');
			}
		}
		return $fields;
	}

In the first function, we basically overload the standard getSearchFields() function and sniff out all the date fields. For those fields, we’ll pull out the default scaffolding and replace each one with min/max fields. Setting the date format on the DateField here is crucial, because dates are stored in the database as yyyy-mm-dd format. If we want to use something more user friendly, we can reformat the date in the next function, but for now, let’s keep it simple.

Before handling the data from the new search fields, let’s create the search filter itself.

remodeladmin/code/DateRangeFilter.php

class DateRangeFilter extends SearchFilter {

	protected $min;
	protected $max;
	
	function setMin($min) {
		$this->min = $min;
	}
	
	function setMax($max) {
		$this->max = date('Y-m-d',strtotime("+1 day",strtotime($max)));
	}
	

	function apply(SQLQuery $query) {
		$query->where(sprintf(
			"%s >= '%s' AND %s < '%s'",
			$this->getDbName(),
			Convert::raw2sql($this->min),
			$this->getDbName(),
			Convert::raw2sql($this->max)
		));
	}

}

A really simple class, not unlike the half-baked version that SilverStripe provides. Why not just use or extend the one that we already have? A couple reasons, both of which are fairly academic. First, the WithinRangeFilter class doesn’t put the date values in quotes, and in my testing, that was causing problems. That is:

CREATED >= 2011-03-03

was not returning results, but the following did:

CREATED <= '2011-03-03'

I don't know if dates always have to be passed as literals in MySQL. I'm not enough of a databaser to explain this one, but using the literal value was the only approach that worked for me.

Second, another minor detail -- our setMax() function needs to be a bit smarter than your average accessor. We need to bump up the value to the next day. Why? Because we'll be passing only a date value and no time, the time is assumed 00:00:00. Records that have the same date as our _max value will be excluded from the result set unless they have a time of 00:00:00. So we'll bump up the _max comparison to the next day, non-inclusive, so we get everything up to 23:59:59 of the selected day.

Another approach to this problem would be to run DATE() functions in the comparison at the database level, but we want to keep the code platform agnostic.

Now, let's handle the form data and update the search query in RemodelAdmin.

remodeladmin/code/DateRangeSearchContext.php

	public function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null) {
		$query = parent::getQuery($searchParams, $sort, $limit, $existingQuery);
		if (is_object($searchParams)) {
			$searchParamArray = $searchParams->getVars();
		} 
		else {
			$searchParamArray = $searchParams;
		}		
		
 		foreach($searchParamArray as $key => $value) {
 			$min = (stristr($key,"_min") !== false);
			if($min) {
				$date_field = str_replace('_min','', $key);			
				if($filter = $this->getFilter($date_field)) {
					if(get_class($filter) == "DateRangeFilter") {
						$filter->setModel($this->modelClass);
						$min_val = $searchParamArray[$date_field."_min"];
						$max_val = $searchParamArray[$date_field."_max"];
						if($min_val && $max_val) {
							$filter->setMin($min_val);
							$filter->setMax($max_val);
							$filter->apply($query);
						}
					}
				}
			}
		}
		return $query;
	}

Calling the parent function gives us a nice SQLQuery object to work on. We can manipulate this as needed to respond to the _min / _max keys in the post data. Because the _min and _max values always come in pairs, we only need to detect the first one (_min), and update the query from there. It will be safe to assume _max exists if we find a _min. From there, it's pretty straightforward -- we do a sanity check to make sure we have a DateRangeFilter, and apply the min/max values contained in the post data.

Implementation

Now that we've built the tools, we can pull them into the DataObject subclass that we're managing in RemodelAdmin.

your_project/code/YourDataObject.php

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

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

In this example, we're using Created as the subject of the date range filter, which is probably a common use case, but feel free to change this to any Date or SS_DateTime fieldtype you have defined in your model. Finally, we overload the getDefaultSearchContext() function to return our new DateRangeSearchContext object, and we're done!

See, was that so hard?

Umm, don't answer that.

Enjoy!

7 Responses to “Remodeling ModelAdmin, Part II (Date Range Filtering)”

  1. Ingo 16. Mar, 2011 at 5:58 am #

    Its indeed quite tricky to have this type of composite field. Your example does a good job of encapsulating this complexity, but ideally we wouldn’t need to extend SearchContext for this aspect of searching.

    Here’s two alternative suggestions for your implementation:

    1. Use a single date picker which produces a ” – ” string. There’s a promising plugin out there, created by one of the main sponsors behind jQuery UI, so thats probably as good as it gets without being officially in the jQuery UI codebase :)
    http://www.filamentgroup.com/lab/jquery_interactive_date_range_picker_with_shortcuts/
    With this field, you’d only need a custom CombinedDateRangeFIlter implementation and a simple regex. New module, anyone?

    2. Combine two DateField instances into a new DateRangeField, similiar to the existing DateTimeField. The setValue() and getValue() methods would have to deal with splitting up the values. Its probably easiest to combine the value for SearchContext and use the same CombinedDateRangeFilter idea from above for the querying aspect.

  2. Ingo 16. Mar, 2011 at 5:59 am #

    Damn HTML stripping – that should’ve read “produces a ‘(startdate) – (enddate)’ string :)

  3. Lars 08. Jun, 2011 at 3:27 pm #

    Brilliant stuff again UncleCheese !! It’s guys like you that make open source work …

  4. Frank 27. Jun, 2011 at 8:49 pm #

    Cheers UC another really useful tutorial. I did actually modify it to incorporate the date range picker and wrote it up briefly here: http://deadlytechnology.com/silverstripe/date-range-filter/

  5. Lobek 11. Dec, 2011 at 7:59 am #

    A is able to rewrite this so that you can filter on specific numbers?
    Specifically, the point is that the example you provide your date of birth (as the number, not a specific date – it will be easier – i think) and then you can look for this person from a given range.
    eg ‘user is 23 years in the search type is a 15 to 28 (years old) and older people are sophisticated in this range.

    Another example is the example price.
    Example: $ 25
    Price is given and the search by typing the interval 20 – 27 ($) is traced with a given product price.
    I sit for a few days and do not know how to solve it.

    Sorry for my bad English (i was helped the translator)

  6. Lobek 11. Dec, 2011 at 12:30 pm #

    Is it possible to rework the code so that it would search and show one specific number?

    We have few users who fill in their age (eg. 23, 25, 18),
    I would like the users to be able to search for a range of ages – similarly to the thing you

    did here, they would not give the date just put in the range – min and max. number
    Another example would be a price range.
    We would search for items priced, for instance, from $25 to $55.
    I have been trying to do that for a couple of days and I don’t know how to do it.
    I have tried to find some info in “The Complete Guide SilverStripe” but I coudn’t do it.
    Your post gave me hope that this is even posssible :)
    I thought of using
    *GreaterThanFilter
    and
    *LessThanFilter, but I dont’ know how to use it with

    input and as I imagine, it might not work with combinerd results of the search.
    You can ignore the previous post, my friend has translated my question to understandable

    English.

Trackbacks and Pingbacks

  1. Date range picker for SilverStripe model admin search | Deadly Technology - 27. Jun, 2011

    [...] reading this awesome post by Aaron Carlino I decided a date range filter was just what I needed to filter search results in [...]

Leave a Reply