Hi Nicolas,
You are right, I can reproduce this issue, if we try our first SQL Query helper tool, the 'Generate a query to the WordPress database'.
If I just choose the posts table, and columns "post ID' and 'post title' - it does try to 'guess' a wrong/bad looking Query along with the ALIAS.

I will pass this to our developers. They will do their best to work on improving this in the future, but we can't promise an ETA on it.

If you can please try our other SQL query Helper Tool, the "Generate a query to the MySQL database". This one is a more 'generic/classic' SQL Query helper and it has access to all your DB tables, while the "WPDB helper" has access to only the WPDB structured tables like Posts, etc.

If we try to choose the same table and columns, it creates a much better/simpler-looking Query.


And it works for filtering when the server-side processing is enabled, too.
You can download my short Video demonstration here.

We just wish to clarify that these tools are not an 'ultimate query generator'.

They are made to simply construct a suggestion of a query. We are constantly working to improve it, but SQL is such a complicated and flexible language that full automation for constructing queries is next to impossible. 

Consequently, the more complicated the query, the higher the probability that it will not return exactly what you need. So, you will often need to play around with the resulting query.

That being said, we completely understand this report, since the use-case you tried is indeed a very simple table and column selection, so our devs will work on improving the first WPDB Query helper in the future for specific Queries like that.

2. The difference when our server-side processing is enabled, and how our SQL parser filters the data :

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.

So in some cases, if you have a smaller table, less than ~ 2 thousand rows, you can disable the server-side option without any performance loss in the loading speed, etc.

3. You mentioned that you tried creating a VEIW and this did not work for filtering?
Can you check, did you possibly add accent graves around the VIEW name when calling it in our SQL table?

For the MySQL engine, we are dynamically adding the accent grave ( ` ), so there's no need to use it around the table name in the query. If you had it around the table/or View name - please delete them, and searching and filtering should work just fine.

Also, avoid adding the semi colon at the end of the Query (;).  Our server-side is also adding this dynamically.

If you create any VIEW again and the Query works in your PHPMYadmin to return the data as needed, but filtering won't work in our Table - please send me the Query you used and we can take a look what the issue could be.

4. Generally speaking, we were able to use the ALIAS in some Queries with server-side processing and we did not encounter any filtering issue just because of that detail, so you can certainly show us the exact Query that came up and we can take a closer look to advise.

I hope this helps, let me know if you have any additional questions or concerns on this subject.

Thank you.

Hi Nicolas,

1. Thank you for this feedback. You are right, that is the behaviour of that first "WPDB SQL helper Tool", I tried choosing some Posts data from it - and it does add those ALIAS, and as you said, if it also adds the accent grave around the Table name like "FROM `tableName`", we will certainly do our best to improve that one;
   but as you saw from my example test - the second Query helper Tool seems to be much better in 'guessing' the correct Query with filtering to work combined with server-side processing.
   
   Thanks again for the feedback and this report.
   
2. In regards to the Export Tools - yes, this is their current limitation. It depends if you enable or disable the server-side processing option.
   
   
   When the server-side is enabled, then it only exports the visible rows - it can not 'look' through any hidden rows in the table's pagination.
   
   So the only way with server-side is if you hit "Show ALL rows" in the "Show X entries" dropdown.
   But we advise caution, if your table is very large ( more than several thousand rows) - it can freeze the browser's Page when it tries to load all those rows at the same time.
   
   - Or, when you have server-side disabled ( for smaller tables), then it exports all the current rows of the table in its entirety.
   
   
   There is a way to 'increase the toggle threshold' for toggling of the server-side option.
   

    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 3188 you'll see this:

   if (is_array($res_dataRows) && count($res_dataRows) > 2000) {

   

    

   The lines of code can change during Updates because our Developers add new lines from time to time, so it is best to search for the line in any Code Editor with CTRL+F to get the right line.

    

   You can change the number 2000 to a value bigger than the number of rows in your table in order to still have that 'toggle option' for server-side processing.

    

   Another PHP file in this path :

   ../wp-content/plugins/wpdatatables/source/class.wdtconfigcontroller.php

   In this file, please search for this, there should be two occurrences, first one around line 82 as :

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

   

    

   And second one around line 143:

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

   

    

   That will increase the server-side processing option automatic limit.

   We just advise caution not to increase that number 'too high' to avoid going over the limit what your Hosting Server's PHP Limitations can handle.

   Usually, up to around 5 to 6 thousand rows should pose no significant performance issues, but if you go higher than that data size without server-side processing, you could experience slow loading times.

   You can certainly test to see how a Table behaves on front-end, and if it loads too slow/or freezes the browser's Page, you can go back in the Table's Back-End and enable server-side processing for that Table.

    

   3. The only other way - how you can export the entire table via server-side processing can be with our Report Builder integration.
   If you make a Report to export Excel or Word Document from the Table, just disable it's 'follow table filtering' option and you will be able to export tables through it without a row limit and with server-side processing.
   
   I hope that helps.