Filters:

Conceptional Wonderful Relations Filters are “where” conditions for an underlying query of a datatable. 

Filters are prepared before query execution, so only the selected data will be transfered to the client. See section serverside processing. Think about having an index on the column which is filtered.

 

Parameters:

  • Type
  • Label
  • Filtered Field
  • Query
  • Defaultvalue
  • Sort Order

Type:

  • daterange-forquery

 

  • daterange
  • dropdown

 

 

  • multidropdown

 

 

 

 

Add a new date range filter:

If you add a new date range filter you have to modify the data table query. Example given:

An event has a specific date where the event will take place, we call this date the start_date.
To make the date range filter work we have to add the following where condition to our data table query.

event.start_date between ‘##date_start##’ and ‘##date_end##’

Wonderful Relations will automatically replace the placeholders with the values from the date fields.

Filtered Field:

Filtered Fields must not be displayed, but must be a present field in the query. This fields could be calculated. Filtered fields are the conceptional where condition, where date range filters use between, dropdown equals and multidropdown the in codition.

Query:

 The selected query provides the displayed filter options, also the selected data or displayed start and end date.

Notation:
DateRange Queries needs field start_date and end_date

DropDown needs value and text and optional selected column 

Example Query for daterange:

SELECT
DATE_ADD(DATE_ADD(LAST_DAY(now()),
            INTERVAL 1 DAY),
        INTERVAL - 1 MONTH) as start_date  ,
LAST_DAY(DATE_ADD(NOW(), INTERVAL 24-MONTH(NOW()) MONTH)) as end_date  
FROM 
DUAL

 

Example: Query provides Data

SELECT 1 value, 'Option 1' text, 1 selected 
union 
select 0 value, 'Option 2' text, null selected  
FROM 
DUAL

Default value:

If the query do not provide any default value, you can set a default value here.

Examples: ##current_year##

##default_project##

Sort Order:

Filters are Displayed above the DataTable, this sortorder defines the order of the filters.

CSS Class:

Value Caching:

Value Caching Entity:

.

Multivalues:

If a cell is present as a commasparated multivalues like (1,2,3,4) a filter use “in”. If multidropdown is selected a filter uses “or”. So if we have multidropdown (1,2) we get a multivalue (1,3) and (2,4) but not (3,4).