Advanced filtering is a special wpDataTables feature that enables frontend table users to filter the data in the tables by the values of each individual column; e.g., to get only the records where a price is between two values; or where a name contains a substring. Multiple advanced filters can be combined, making it possible to use wpDataTables as a filterable catalog. An advanced filter provides multiple input types with different behaviors for end user convenience. Since filtering logic can be combined, you can filter by several columns at once, building complicated queries like: “Get all the rows where the price is between 1000 and 5000 and starting date is between 01/01/2014 and 01/03/2015, and the continent is either Asia or Africa”.
To enable advanced filtering for a wpDataTable, you need to tick the “Advanced filtering” checkbox in “Additional settings” of the table settings (it is preselected by default, it is also enabled by default for tables created with Table Constructor).
When this checkbox is checked, it is possible to define a filter type for each of the columns settings. Different filter types produce different filtering behavior. We will go through these in detail.
The Text filter type is a basic type: it will perform the search based on at fragment of text you enter. Only those rows where the text in the cell of this column contain the entered text will be shown in the table:
In this example the “City” column has a text filter type assigned. When we enter the “na” fragment, only the rows that contain this fragment in the city names will be shown (i.e., Nairobi, Varanasi, Vienna, Barcelona). All other rows are filtered out.
If you need an exact match, and do not want to show values containing a substring, enable the ‘Exact match‘ checkbox for this filter in the column settings on the ‘filtering’ tab.
The Number filter is another basic filter type. It allows you to enter a number in the filter, upon which only the rows in which this column has precisely the same number will be displayed:
In this example “Num. days.” Column has a number filter type, “12” is entered, and only one row is shown, because only this row has the “12” value of this cell.
The Number Range filter type should be used when you want table users to be able to filter the table by the values of some column within ranges of numbers: e.g., “values between 3 and 7″, or “greater than 3”, and so on. It will output two inputs: “from” and “to”.
In this example, the “Price” column has the number range filter enabled. 1000 is put as the lower limit, and 2000 as the higher limit; so only the rows that have the price values between these two limits are shown (limit values are included as well).
The Date range filter type should only be used when the column has a date type, and when you want your users to be able to filter the table data by ranges of dates, e.g., “later than 1st June”, or “between 1st May and 15th of August”, etc.:
In this example the “Starting date” column has a Date Range filter, where the “From” value is set to 01/05/2013, and the “To” value to “01/07/2013”. So, only the rows where cells in the starting date column have a date value between these 2 are shown.
The DateTime range filter type should only be used only when the column is a datetime type, and when you want your users to be able to filter the table data by some ranges of datetimes, e.g. “later then 1st June 14:00”, or “between 1st May 10:00 and 15th of August 21:00”, etc.
In this example the “DateTime” column has a Datetime Range filter type, where the “From” value is set to 22/10/2016 1:30 PM, and the “To” value to “22/10/2016 4:00 PM”. So, only the rows, where cells in the “DateTime” column have value between these 2 are shown.
The Time range filter type should be only used when the column is a time type, and when you would like your users to be able to filter the table data by some ranges of times, e.g., “later than 8:00 AM”, or “10:00 AM and 10:00 PM”, etc.
In this example the “Arrival” column has a Time Range filter type, where the “From” value is set to“5:40 AM”, and the “To” value to “5:45 AM”. So, only the rows where cells in the “Arrival” column have the time value between these 2 are shown.
The Selectbox filter type is a very convenient filter, that allows front-end users to select one of the pre-defined values for some of the columns, and the table data will be filtered based on this value.
If you want to define possible values list for select box, you must first go to the column settings for the desired column. In “Data” tab and for “Possible values for column” select “Define values list“.
Click on “Read from table” button, and you will see that all values from that column are added to the select box.
You can also enter the field you want to be displayed as selectbox values in the Values list.
For example, if you need to filter by the “Fruits” column, you can put this example in input field:
Apple, Peach, Lemon, Orange
You can also override the default generated values with this input field, if you want them to display in a different order from their order of appearance in the table.
This same field is used for possible cell options in editable tables.
The filter is rendered on the front-end as a dropdown selectbox:
When a user chooses one of the values in the dropdown, the table is filtered, and only the rows that contain the selected value in the column will be displayed.
In version 2.1 of our plugin, you will be able to choose MultiSelectBox as a filter type. This filter allows front-end users to select more than one of the pre-defined values for some of the columns, and the table data will be filtered based on these selected values.
The Checkbox filter is a convenient way to filter a table by more than one value of a column. If you want users to be able to filter the rows in the table with logic like “Show all the rows where the continent is either Asia or Africa or America”, the Checkbox filter is the right option to use.
For tables which do not have server-side processing feature enabled the possible values for this filter will be pre-filled automatically from the column “in the order of appearance”, i.e., it will pick all unique values from the cells in this column. For tables which have server-side processing enabled, you need to initialize possible values from the column by clicking on “Read from column values” in the column settings block, or define them manually, and separating them with a “|” sign – similarly to the selectbox filter type (See above). You can also override the default generated values with this input field manually if you want them to display in a different order from the order of appearance in the table. To use this filter, you need to set the “Filter type” setting as “Checkbox”, and optionally define the possible values (but you must do it only if you use the server-side processing feature for this table).
On the front-end, users will get a button under this column:
When users click on this button, a pop-up appears, in which all the possible values of this column are listed with checkboxes:
When a user ticks any of the checkboxes, the table will be rendered simultaneously, and only the rows that contain the selected values in the column will be displayed.
The “OK” button closes the popup, and the “Reset” button unticks all the checkboxes.
In version 2.1 of our plugin and on, the checkbox filter can appear in a popup, when the filter is rendered above the table, instead of rendering checkboxes directly on the page.
Sometimes, you will need to find the exact match for certain column in your table.
For example, if a multi-select field has possible values of Room 1, Room 2, Room3, … Room 10, Room 12, and someone selects filter value “Room 1” – it will return all records for Rooms that have number 1 in the first place.
To disable this, you can use Exact filtering option.
You can select Exact filtering if you want to allow an exact search to use exact match logic for filtering and disabling a partial match.
Sometimes, you may not want to allow your users to filter the table by all the columns, since some columns are less significant, and it makes sense to hide the advanced filter capability for them.
For this, you can click on the Filtering tab in the column settings and disable the filtering with the switch.
By doing so, this column will not have a filter on the front-end.
Having an advanced filter below each column is a convenience, although that is not necessarily true for all cases; if there are many columns, or if you would like to filter by invisible columns, it makes sense to have the filter separated from the table itself. This is supported by wpDataTables with the “filter in form” feature. To use it you need to go to the “Sorting and Filtering” tab in the table settings, first enable “Advanced column filters”, and then enable the “Filter in form”:
All the other settings for filters are the same. Keep in mind that you can also define filter types for invisible columns. The columns will not be visible in the table, but the filtering block will appear in the filtering form, so you can extend the filtering functionality without overloading the table. Also keep in mind that having too many invisible columns is not good for overall performance.
When this checkbox is checked, and the table is saved, the front-end look of the table will change and a filtering form will appear above the table. See the example:
|id||Date||Referral e-mail||Hits||Unique visitors||Pages per Session||Revenue||Reference type||Country|
WordPress has a built-in functionality of widgets and sidebars. wpDataTables uses this functionality for the filtering feature. This way, you can define the region of the page where to render the filtering form. To use this filtering function you need to follow these rules:
- Enable the checkbox “Filter in form” on the table settings:
- Place the wpDataTable in a page or post which has some of the registered sidebars defined in the template.
- In the “Appearance” -> “Widgets” area of the admin panel, drag the “wpDataTables filtering widget” widget to the sidebar block of the page you used to paste the shortcode. There, you can define the title for this widget:
Once this is done, the filter will be moved to the sidebar section of your website (or another region you have chosen for it):
Advanced filter in a widget also has a “Reset filters” button which can be used to refresh the filter set.
If you want to load the page with several predefined settings for the filters, you can make use of the “Predefined values” input. Values from this input will be used as the predefined filter values upon page load, and also in the front-end editor, if the table is editable.
For the checkbox filters, you can predefine several preselected values, by selecting them from the list.
You can use placeholders (%CURRENT_USER_ID%, %VAR1%, %VAR2%, %VAR3%) in this input. See this documentation section for more information on placeholders.
You can also predefine filter values for the table in the URL, which may come in handy when you would for example, want to send your client a deeplink with some info from your catalog that is already filtered for him. The URL key that will be parsed by the wpDataTables is “wdt_column_filter[KEY]” where the KEY is either a zero-based numerical index of the column you would like to filter, or an initial header of the column (header or the CSV or Excel table, or MySQL column header for MySQL-based column header). E.g.
(see the URL part after “?” and check the table output)
The values parsed from this URL will be used as default filter values.
- Creating editable tables and filling the table data manually
- Creating non-editable tables from Excel files
- Creating non-editable tables from Google Spreadsheets
- Responsive design
- Conditional formatting
- Sorting tables
- Row grouping
- Using Placeholders – Adjusting MySQL queries dynamically based on shortcode attributes
- Pre-filtering tables through URL
- Calculating totals for columns, sum row
- Table Layout, Word Wrap
- Table data export tools
- Visual Composer integration