Hello Robert and Kevin,

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.

-

I can understand this might seem like a basic issue, but I can assure you it is not so simple.

We will do our best to clarify why a search/filtering or sorting issue might happen if you have a specific custom SQL Query combined with our server-side processing that has some certain functions, syntax, etc.

1. Firstly, the most important note : Our Plugin is not a Database Management Tool and is not meant to be used as one.

So some Query which might work perfectly in a DB Management Tool ( for example PHPMyAdmin) with searching/filtering/sorting;

but the same Query might not work perfectly when our server-side processing is enabled.

The SQL Query logic in our Plugin 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 combined with our server-side processing if you include:

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

-

For example, if the filtering works but just the search in the table doesn't work, first you can check 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.

-

- This fixes it if you just have a search issue while the column filters are already working.

Then check the other points mentioned above.

If none of that helps, then, as you saw from older tickets, 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 any user comes across this ticket and they are not sure how to use a VIEW in our Plugin, you can see our video, where we show an example of using an SQL View in our plugin.

2. Now, more details about the difference when you toggle our server-side processing in your SQL Query Tables :

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

Basically, when the server-side option in a Table 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, or if you use any of the functions/syntax we mentioned above, there can be errors or issues with filtering, sorting or search;

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 this helps to clarify everything, did my best to pass our developer Team's explanations.

So if you encounter any issue for filtering, search or sorting, there are three ways what you can do :

1.  You can try to somehow simplify your Query;

2. 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 and see if that helps.

Here is how you can 'raise' the limit of rows for "server-side processing" toggle.

 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.

3. Or the third option, as you guys did, to use an SQL VIEW from the Database and call it in our Table as simple SELECT.

Our developers will do their best to improve all this in the future, though, but we honestly can't say a realistic ETA on when or exactly what can be done to improve how our server-side processing works for filtering/sorting/search with our PHP & SQL Parser when certain SQL functions/syntax is used.

If you have ideas which improvements we might do in the future, you can make development suggestions.

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.

Thank you.