Hi, Jouke.

Sorry for a late reply.

I see what you mean from the screenshots, and thank you for sending the SQL Query used. ( the two versions)

They don't look extremely complex, but due to some specific limitations of our SQL Parser , 

depending on the way how we construct the Query and SQL functions we add in it, you might have filtering/search issues.

As we pointed out on this Documentation,

please be advised that preparing or debugging custom MySQL queries for you is not included in the plugin support,

but I will do my best to try to advise what you could try/check to modify to get filtering working in our SQL Parser.

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 double-check for accent graves around the table name, if you have this, remove it. ( As far as i see, you don't have that)

Then, doucle-check if you used CONCAT to create any column. ( I don't see that as well in your Queries, but good to double-check)

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.

But, I realise you mentioned that creating VIEWs will be a problem for your hosting, sorry to hear that.

-

Other than all of this, you can also try to remove all the ALIAS ( AS) from the column names ,

see if it is possible to call each column by its original header name,

and then later in our plugin's column settings,  you can change the Displayed Header Name as needed.

-

Lastly, check how many rows you have in the table.

If it has less than two thousand rows, try to disable servers-side processing in the table settings.

This can help with filtering, but it will increase the server load, depending on the number of rows and the server performance.

There is a workaround If you need to increase/force a larger row count while still having the "toggle" to disable server-side,

Please go to this PHP file :

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

But please be advised, as mentioned before, this can increase hosting server load, depending on the size of the table data set.

I hope this helps.

Hi, Roberto.

Sorry for the waiting time.

I am not 100% sure what is the main root of the issue with the filtering in your SQL Table,

the Query does look much simpler, to be honest.

Maybe the best will be if we can take a closer look at the back-end setup of your table.

If you can please make a new ticket, and send me access to an Administrator account,

and please confirm the table ID in question.

When you send credentials please mark either the entire ticket as PRIVATE or the reply as PRIVATE,

for safety reasons so that only we can see it.

Thank you.

Hi, Jouke.

I am happy to hear that the VIEW fixed the filtering for your SQL table.

Yes. We can completely understand that this is not ideal,

but our plugin is not a database management tool and is not meant to be used as one.

As stated in our SQL Table documentation, our logic is ran through an SQL Parser which has certain limitations.

And we stated the important note for these limitations on the page, that the filtering/search won't work if you include certain SQL functions :

That being said, we always appreciate feedback from users, so we will do our best to improve our SQL Feature in the future.

If you have any idea or suggestion what you would like to see improved, please add suggestions for our development Team.

Please feel free to search on our suggestions page, 

 to see if someone may be already suggested this feature. If you can't see it, feel free to add your suggestion there,  and as more people vote, the feature will move higher on the priority list.

You can certainly follow our changeLog page if you'd like ( it is also available in the plugin dashboard), where we state any changes/new features/bug fixes during updates;

and our newsletter, so you're informed about new features, bug fixes, freebies, etc.