0

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.

Search bar

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:

enter image description here

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... :(

  • Hi and thank you very much for your answer. I inserted the following: d.pr/AxrX But if I change the values the form reloads but does not show only the items that have a datetime-value between the given dates. Do I have to change the model, too? – MyFault Feb 18 '16 at 11:57
  • Yes, you will have to change the model as well. I would recommend making a test component at component-creator and review the generated model code... – jonasfh Feb 18 '16 at 13:06
  • Okay - that works for now :-) But there are 2 problems left - I described them by editing the question. Would be glad to get some help here. – MyFault Feb 18 '16 at 13:23
  • Not too crazy about the way you extend the question to something it was not. Try working with what you have now, make sure the states are set correctly and retrieved by the filter form. Bad input can be filtered in populateState with a regex, or simply by using date('Y-m-d',strtotime($dateinput)) to get a valid date. – jonasfh Feb 19 '16 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.