Advanced filtering is a special feature of wpDataTables which allows frontend table users to filter the data in the tables by the values of each individual column; e.g. get only the records where the price is between two values; or where name contains some substring. It is possible to combine multiple advanced filters. This makes it possible to use wpDataTables as a filterable catalog. Advanced filter provides multiple input types with different behavior for end user convenience. One more thing to notice is that filtering logic is combining, so 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 filter for a wpDataTable you need to tick the “Advanced filtering” checkbox in “Additional settings” of the table settings (it is preselected by default, also it is enabled by default for tables created with Table Constructor).
When this checkbox is checked, it will become possible to also define a filter type for each of the columns settings. Different filter types produce different filtering behavior, so now we will go through all of these in detail.
This filter type is a basic one: it will perform the search based on the fragment of text that you enter. Only the 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 are shown (Nairobi, Varanasi, Vienna, Barcelona), all other rows are filtered out.
If you need an exact match, and not to show the values which contain a substring, enable the ‘Exact match‘ checkbox for this filter in the column settings on ‘filtering’ tab.
This is another basic filter type. When some number will be entered in the filter, only the rows, where this column has exactly the same number, will be displayed:
E. g. 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 “12” value of this cell.
This filter type should be used when you want table users to be able to filter the table by the values of some column not strictly, but within some ranges of numbers: e.g. “values between 3 and 7″, or just “greater then 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).
Date range filter type should be used only when the column has a date type, and when you would like your users to be able to filter the table data by some ranges of dates, e.g. “later then first 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 the date value between these 2 are shown.
DateTime range filter type should be used only when the column is a datetime type, and when you would like your users to be able to filter the table data by some ranges of datetimes, e.g. “later then first 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.
Time range filter type should be used only 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 then 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.
Selectbox filter type is one of the most convenient filters, it allows the 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, first you must go to column settings for desired column. In “Data” tab and for “Possible values for column” select “Define values list“.
Now click on “Read from table” button and you will see that the all values from that column is added to the select box.
You can also enter your desired values in the Values list field that you want to be displayed as a select box values.
E.g. you if you need to filter by “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 order different from order of appearance in 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 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.
Checkbox filter is a convenient way to filter a table by more then one value of a column. If you want the 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”, this is the right option for you.
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 do have server-side processing enabled you would need to initialize possible values from the column (by clicking “Read from column values” in the column settings block), or define manually, separating them with a “|” sign – same as for 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 an order which is different from the order of appearance in table. To use this filter you would need to set “Fiter type” setting as “Checkbox”, and then optionally define the possible values (but you must do it only if you use server-side processing feature for this table).
Then on the front-end users will get a button under this column:
When users click on this button a pop-up appears, where all the possible values of this column are listed with checkboxes:
When 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.
“OK” button closes the popup, and “Reset” button unticks all the checkboxes.
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.
Here you can select if you want to enable exact search to use exact match logic for filtering and disabling the partial match.
Sometimes you don’t want to allow your users to filter the table by all of the columns, some columns are less significant, and it makes sense to hide advanced filter for them.
For this you can just click on Filtering tab in column settings and disable the filtering with the switch.
Then this column will not have a filter on the front-end.
Having an advanced filter below each column is convenient, but not for all cases; if there are a lot of 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 just need to go to “Sorting and Filtering” tab in table settings and first enable “Advanced column filters” then enable the “Filter in form”:
All the other settings for filters are the same. Please keep in mind just a couple of things:
- Checkbox filter will not appear in a popup, but all checkboxes will be displayed in a form.
- 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 – just keep in mind that having too many invisible columns is not good for performance.
When this checkbox is checked, and the table is saved, front-end look of the table will change – a filtering form will appear above the table. See the example:
Filter in form example
|id||Date||Referral e-mail||Hits||Unique visitors||Pages per Session||Revenue||ref_type||country|
The logic of the filters is the same as for the usual look of the advanced filter, when the filters are rendered in table’s footer.
WordPress has a built-in functionality of widgets and sidebars, so wpDataTables uses it for the filtering feature: this way you can define yourself the region of the page where to render the filtering form. To use it you need to follow these rules:
- Tick the checkbox “Filter in form” on the table settings:
- Place this 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 that you used to paste the shortcode. You can define there the title for this widget:
When this is done, the filter will be moved to the sidebar (or other region that 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 some 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 on 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 like to e.g. send your client a deeplink with some info from your catalog already filtered for him. The URL key that will be parsed by the wpDataTables is “wdtColumnFilter[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