Joomla: Filter items (show items between 2 dates)

Question

I would like to improve my filter in my custom joomla-component. I would like to create a filter that shows all items which have been created between 2 dates (MySQL-Table: creationdate).

At the moment I'm using

JLayoutHelper::render('joomla.searchtools.default', array('view' => $this));

and a xml-defined filter-set to create a search-form and a category-based filter.

Does anybody know how to solve my problem? Or maybe somebody knows a component which filters for dates?

As I read in the component creator's source files I could so something like this:

<field name = "creationdate_from" 
type = "calendar" 
label = "test" 
format = "%d.%m.%Y" 
filter = "user_utc" 
onchange = "this.form.submit();"/>
<field name = "creationdate_to" 
type = "calendar" 
label = "Test" 
format = "%d.%m.%Y" 
filter = "user_utc" 
onchange = "this.form.submit();"/>

Additionally I would have to change the populateState-method

/**
* populateState function.
* 
* @access protected
* @param mixed $ordering (default: null)
* @param mixed $direction (default: null)
* @return void
*/
protected function populateState($ordering = null, $direction = null) 
{
...
$creationdatefrom = $this->getUserStateFromRequest($this->context . '.filter.creationdate_from', 'filter_creationdate_from', '');
$this->setState('filter.creationdate_from', $creationdatefrom); 

$creationdateto = $this->getUserStateFromRequest($this->context . '.filter.creationdate_to', 'filter_creationdate_to', '');
$this->setState('filter.creationdate_to', $creationdateto); 
...
}

and extend the query itself:

// Filter by date 
$fromdate = $this->getState('filter.creationdate_from');
$todate = $this->getState('filter.creationdate_to');

if(!empty($fromdate))
{
$fromdate = $db->Quote($db->escape($fromdate, true)); 
$query->where('(DATE_FORMAT(a.creationdate, "%d.%m.%Y") >= ' . $fromdate . ')'); 
}

if(!empty($todate))
{
$todate = $db->Quote($db->escape($todate, true)); 
$query->where('(DATE_FORMAT(a.creationdate, "%d.%m.%Y") <= ' . $todate . ')'); 
} 

This works fine - but there are 2 problems:

First problem: If I choose a date, the page reloads (of course) and after that the searchbar is not extended as it would be if I filter by state. The "reset"-button does not clear the values from calendar-fields aswell.

Second problem: If I enter some strange stuff, e.g. ";" or "/" into the date field, this input is not filtered and results in a crash:

Answer 1

Component-creator has filters for dates, this might be just the thing for you. Make a component, add a table, then add a date field to the table. Check the Add a filter for this field - checkbox to display a date filter. The filter is not very sophisticated, consisting of two calendar-selectors, but it works good enough. I made a simple demo to test it worked like I remembered it. Below is the generated xml for the filter for my demo event-table:

<?xml version="1.0" encoding="utf-8"?>
<form>
  <fields name="filter">
    <field name="search" type="text"
      hint="COM_DATEFILTER_SEARCH_FILTER_SUBMIT"
      label="COM_USERS_FILTER_SEARCH_DESC"
      class="js-stools-search-string" />
    <!-- Keep "_dateformat" suffix for date validation on form submit -->
    <field name="eventdate_from_dateformat" type="calendar" label="" />
    <!-- Keep "_dateformat" suffix for date validation on form submit -->
    <field name="eventdate_to_dateformat" type="calendar" label="" />
    <field value="Submit" type="submit" for="eventdate" 
      onclick="this.form.submit();" />
  </fields>
</form>

You can probably make a component yourself and just cherry-pick the code you need.

I have been using component-creator a lot (I'm not associated with it in any way), and I like it a lot. Unfortunately the free version is very limited (but will do what I did above, I think), and the pro version is quite expensive... :(