Hi Jouke and Rudi,

We have first sent a couple of older Plugin versions to Jouke, just so he can try to reroll and check if it might be a version issue,

if the filters were working on the same table before.

-

But generally speaking, we are very limited in how much we can debug when it comes to Custom SQL Tables such as this one,

especially since you have some calculated columns wrapped inside a VIEW as you shown in the Video,

and you are also calling the SELECT statement not as "SELECT * FROM ViewName",

but you are adding a dynamic placeholder SELECT * FROM %CURRENT_USER_FIRST_NAME%__Dagverbruik,

which adds another layer of complexity to our SQL Parser, 

with a WHERE statement as well,

so we are not sure exactly where our SQL Parser seems to fail, so we will try to advise what you can do to try and isolate this more.

-

Of course, you can first try to reroll the Plugin version to those couple old versions i sent you,

that were updated approximately 9 months ago,

and see if the filtering is still the same, let me know.

If it is the same, then it is not a version issue, but our SQL Parser is probably struggling to filter with your specific data, the Query combined with the VIEW.

-

Since the table has 2300 rows , you can try a test with disabling server-side processing in this table,

but you just need this workaround to increase the allowed server-side toggle for more than 2 thousand rows.

When 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.

Let me know if you can increase this to a bit higher than 2300, just to ensure that you can toggle the Server-Side processing option in this specific Table,

then try to do the filters, let us know if that makes any difference?

-

2. Rudi, in regards to your use case, we would need to see more details,

and please open a new/separate ticket,

but i will advise you first here in general about how our SQL Parser works and check if you also have server-side processing enabled in your SQL Table,

try to disable it - see if the filters work then;

If not, can you please open a separate ticket, and send us the SQL Query you used there,

are you also already using an SQL VIEW as Jouke is or not, etc?

-

If you can show us the SQL Query that was used for this table on your ticket - and I can advise if maybe some small modification/correction is needed to get the filtering to work?

-

But you can also check these points to save time if you wish.

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.

-

Let me know If that helped, and i can take a look at the Query, as well.

But please open a new ticket, because we need to focus on both of your individual Tables,

if we go in more details about both cases on same ticket,

it will be impossible for our developers to read everything if it comes to any escalation at some point.

Thank you.

Hi Jouke,

Sorry i just noticed at the end that you actually do not have our latest Plugin version, 5.8.1,

you have 5.6.1,  so see if you can first try with the old versions,

then also try with the latest version - check if there is any difference with the filtering and let us know,

but our final goal is to help you with the latest version, since our Support always pushes with the developers to fix any issues for future versions/ not to reroll,

but you can do this just as a test to check.

-

If none of that helps, then this is most probably a limitation coming from our SQL Parser not being able to filter with server-side processing with your specific Custom SQL Table and the Query.

So it will be good if you can also try that workaround and disable server-side processing, see if then the filters work in that case.

If that is the case,

In 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, as in your case, there can be errors;

as we mentioned, 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;

it is based on an SQL Parser which has limitations;

so it can happen that a specific Query might work in PhpMyAdmin but struggles to work 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, you would need to try and somehow simplify your SQL Query in order for our server-side processing to work;

Such as just calling an SQL VIEW like :  "SELECT * FROM ViewName",

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.

I hope this helps.

Hi Rudi,

I am happy to hear that the advice helped you solve your use case with your Query,

thank you for letting us know.

If you encounter any other issue, please don't hesitate to open a new ticket and we will always do our best to help.

-

@Jouke,  we will still be on stand by for your case, of course, let us know how it goes,

we will do our best to advise as much as our Support can cover.

Thank you.