Hi Adam,

Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

1. In regards to your initial question, Jens gave some interesting ideas, thank you for that @Jens.

We will first show you some general ways how you can predefine filters with our Plugin, it can be through the SQL Query itself with a WHERE statement, like Jens wrote, whether that was a direct/fixed manual value;

or if you try our Placeholders as VAR/variables, then insert a different variable via table shortcode etc;

or via the Advanced Column filters in general.

Here are some basic examples :

If you wish to try Pre-filtering tables through URL,  please check out this page with examples.

-

You can also use placeholders for table generation.

1. For example, if you have a MySQL query-based table, you can use this query:

SELECT * FROM yourDatabaseTable
WHERE columnName > '%VAR1%'

This would, of course, show only the values higher than the one set via the VAR placeholder.

%VAR1% can be defined in the Placeholders tab when you're creating a table, 

or you can pass a different VAR1 value in the table's shortcode.

-

2. Or, if you don't add the condition in an SQL query ( or don't use an SQL Table),

you can add %VAR1% in column settings -> Filtering, under "Predefined filtering value".

Then, you can have different versions of the same table, with the VAR1 value passed in the shortcode,

let's say if we had a "Regions" column and we wish to filter a different region per each page.

If we want page 1 for example only filtered for "Region A" , you can set predefined filter to this column as %VAR1% (filter type has to be set as text) :

Then for a page where we need only Region 1 filtered, use shortcode [wpdatatable id=1 var1='Region A'] ,

 page 2 for only "Region B", shortcode would be [wpdatatable id=1 var1='Region B'] and so on, 

to apply the values defined in the shortcode to the filter.

2. To be more specific for your use-case, I did not fully understand your question, where you wrote :

This is a very complex query (with 6 subqueries), I was hoping there was a way to have the table only rows matching certain criteria.  Please let me know if such a filter exists and, if so, where the documentation is for it.

We have advanced column filters available, as explained in this Documentation.

If you have one( or more columns) which you could filter on, you can add multiple column filters as part of the Query or directly in the Columns as predefined filters;

So, when it comes to filters - our Plugin can only filter per each column;

you can, of course combine multiple filters from multiple columns; but I did not fully understand that part, so if you can try to clarify it - add some screenshots or a Video, then we can advise?

Of course, if you are skilled in SQL, you can also try any kind of SQL operator and that should work;

Just be advised that our Plugin is not the same as a Database Management Tool;

We use an SQL Parser, so some complex Query might work in the DB but if you encounter an issue in our SQL Table;

such as if it returns the data as needed, but a filter/search does not work :

Our logic is based on a PHP SQL parser which has full support for the SQL dialect for the following statement types

SELECT, INSERT, UPDATE, DELETE, REPLACE, RENAME, SHOW, SET, DROP, CREATE INDEX, CREATE TABLE, EXPLAIN and DESCRIBE.

Some of them are disabled for security reasons.

Filtering, sorting, and search may not work properly if you include:

• Accent graves ( ` ) around the table name
• JOIN functions
• UNION functions
• CONCAT functions
• sub-queries

-

So, first you can check for accent graves around the table name, if you have this, remove it...

Then, see if you used CONCAT to create any column.

If so, go into this column setting, and disable it from "global search" in the Filtering tab.

-

-

If none of that helps,

you can try preparing a MySQL view (which will return the data that you need, call it e.g. “view1” and then build a wpDataTables based on a simple query like "SELECT * FROM view1″.

 If you need help with that, you can see our video, where we show an example of using View in our plugin.

3. Just to additionally comment on Jenses approach with the Number Range and the predefined filter, but then to disable the filtering for the table;

That is a nice idea, thanks for that, Jens.

This can work - if you wish to completely disable filtering on this Table, just note that then users will not be able to use any kind of column filters on your table.

If you wish to allow them to use other column filters, but only hide this one - then you need to activate the Filters above the Table, and use CSS to hide only this specific column filter.

Here is my example, if we have this table and want to set predefined MIN value as a Number Slider filter for "price" column.

If we want it to only load values from 50 and upwards ( max value in my table is 120 in that field) :

Now, the users can still move the MIN slider and see other values.

If you enable the filters above the table like this :

And use custom CSS to select only the Column you need and hide its filter :

#table_1_10_filter_sections {
    display: none !important;
}

You will notice in the HTML, all these DIV containers have a similar name for all column filters, they start like :

#table_1_0_filter_sections

#table_1_1_filter_sections

#table_1_3_filter_sections

and so on... So you just need to isolate the right filter number and insert it in the CSS selector for this to work.

We hope this helps, thanks to Jens for these additional workaround ideas again.

Let us know how it goes and if you have any additional questions about that.