Hi Chris,

When you make an SQL VIEW - you will not be able to use any Dynamic placeholder in a WHERE statement for that kind of SQL Query table in our Plugin.

That is why, if you make an SQL VIEW - it is recommended to call it in our table with a very simple Query like :

SELECT * FROM VIEWName

In that case, filtering should work.

You said :

When the table is unfiltered, this returns the rows as expected, however as soon as ANY filter is applied no rows are returned at all.

I presume this is the case, when you add the WHERE as :

SELECT * FROM view_UmbrellaPaymentDetails p
WHERE
     p.WP_Parent_UserID = %VAR9%
    OR %CURRENT_USER_ID% = 3

 I am not sure what the letter 'p' in the SELECT is for, but please avoid using space as part of the VIEW name.

So, see if you can rename the View in your Database, if the name was "view_UmbrellaPaymentDetails p", rename it to 'view_UmbrellaPaymentDetails_p' for example.

And, avoid using dynamic placeholders as part of the Query such as "%VAR9%" , %CURRENT_USER_ID% and others.

2. For this other Query, which is more complex :

SELECT * 
FROM materialized_view_UmbrellaPaymentDetails p
INNER JOIN 
    veriPAYE_Constants.WP_Users w 
    ON p.umbrella = w.veriPAYE_Name
WHERE 
    %VAR9% = w.WP_Parent_UserID;
    OR %CURRENT_USER_ID% = 3

It seems you are calling data from a VIEW, then joining that VIEW with another SQL table and adding dynamic placeholders in the WHERE statement.

All of that combined is not going to work.

I will do my best to try and provide additional advice about all this, why it works like that :

On this documentation, there are more details which explain how our server-side processing works :

https://wpdatatables.com/documentation/table-features/server-side-processing/.

Basically, when the server-side is enabled, the wpDataTables will give the search results through the Query;

So, our Plugin sends the Query to the database.

If that Query is formatted as

 "SELECT ...

 FROM ... 

WHERE... "

but after the "FROM" if it has any complex Query, there can be errors with sorting/filtering/table search;

Our SQL Query feature does not work in the same way as a Database Management Tool ( such as PHPMyAdmin and similar),

and is not meant to be used as one;

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.

-

To summarize, it can happen that a specific complex Query might work in PhpMyAdmin but struggles to work perfectly for sorting/filtering through our server-side processing ( and the SQL Parser);

So when the Server-Side is enabled, our plugin sends a more complex SQL Query which in this case is too complicated for our Parser to handle,

instead of when the server-side is disabled, it just simply filters the data just by the values already seen in the column.

I hope that this helps to clarify everything.

So to solve this particular issue, there can be multiple ways :

1. You can try to simplify your SQL Query in order for our server-side processing to work;

2. Or try to make an SQL VIEW in your Database, then call it in our SQL Table like : SELECT * FROM ViewName

3. Or if you can make it work without server-side processing, if your data/number of rows of  the table does not become too large, let's say above 4, 5, 6 thousand rows,  and if the hosting server performs well,

you can 'get away' with disabling server-side on the table.

 If your SQL query based tables are not bigger than 2.000 rows, 

you can disable server-side on SQL tables, and it will work like it does for Excel tables. ( loads all rows regardless of pagination)

If you need to increase the row count while still having the "toggle" to disable server-side,

Please go to ../wp-content/plugins/wpdatatables/source/class.wpdatatable.php and around line 2176 you'll see this:

if (count($res_dataRows) > 2000) {

You can change that number to a value bigger than the number of rows in your table.

Same should be applied in ../wp-content/plugins/wpdatatables/source/class.wdtconfigcontroller.php on lines 53:

if (count($wpDataTable->getDataRows()) > 2000) {

And line 100:

if (count($wpDataTable->getDataRows()) > 2000) {

That will increase the server-side automatic limit.

I hope this helps.

Hi Chris,

I am glad to see you found this workaround, that is awesome.

We will make note of it, it will be very helpful for other users who need a similar use-case.

Thanks again.

If anything else comes up, please don't hesitate to reach out to us.

Hi Chris,

I see what you mean. Yes, at the moment this is how our PHP & SQL Parser works and for the time being we don't have a way to change/improve that logic, but I will pass the feedback to our developers. They will do their best to work on improvements in the future.

That will be the best way, to keep a lookout on our ChangeLog and you can subscribe to our newsletter, where we will keep all our users informed on new improvements/features/bug fixes, etc.

Thank you.