Hi there, we just purchase the plugin and we already found issues. I notice that filters don't work on server side when the query has some ALIAS in there (your wizard make use of them). This happens in any size of query. You can test this:
1. Creating a table from database data
2. Get wp_post ID and title
3. Just try to use the filters on title
4. Remove ALIAS in query ("AS")
5. Check the filters now work
This is unacceptable if the plugin make use of ALIAS when creates the queries and if the user needs to make use of complex queries. So please, this is a very serious issue. I tried even creating a view with no success. The crazy thing is that you can filter with ALIAS on the query if you use the table on non server side. And that is another problem, the non server side works very slow.
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.
Hi there! Thanks for your reply. I ended using a VIEW. The problem was the the accent grave ( ` ). The thing is, your sql query generator adds the accent grave automatically. And also it makes use of the ALIAS. So you have a base issue there that you have to solve first. Then you can make ALIAS work. The query i'm using is kind of complex and I have to use ALIAS because I have to get data from wp_metada and wp_usermeta of many ids, so ALIAS are crucial. For now i'll be using VIEWS.
Another thing I notice, the export feature only exports the rows that are loaded. Isn't there a way to export all the rows at once? If the table is big you can export in bulk, but just exporting the rows you are seeing? That is strange.
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.
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.
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.
Hi Milos, that is an infortunate limitation. When you have a table like a report, you want to export all the rows so you need to export in limited bulk until all rows are exported. The solution you give is not a very good thing to do on server resources.
We need a solution to use server side to navigate the datatable and to be able to export all rows depending on the filters.
I see what you mean. You are right, currently we do not have an ideal solution for larger tables, to export all rows and have only the filtered ones, without having a performance impact on the server when hitting "show all rows" via the Table Tool export.
And with the report builder, if we set "follow table filtering", then it also exports only the visible rows when server side is enabled.
So at the moment we don't have a valid solution to cover all parameters needed for that use case.
You can suggest it to our developers, though - they will do their best to make a solution in the future.
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.
-
If you have coding skills and wish to try to make a custom solution now,
Hi there, we just purchase the plugin and we already found issues. I notice that filters don't work on server side when the query has some ALIAS in there (your wizard make use of them). This happens in any size of query. You can test this:
1. Creating a table from database data
2. Get wp_post ID and title
3. Just try to use the filters on title
4. Remove ALIAS in query ("AS")
5. Check the filters now work
This is unacceptable if the plugin make use of ALIAS when creates the queries and if the user needs to make use of complex queries. So please, this is a very serious issue. I tried even creating a view with no success. The crazy thing is that you can filter with ALIAS on the query if you use the table on non server side. And that is another problem, the non server side works very slow.
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.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hi there! Thanks for your reply. I ended using a VIEW. The problem was the the accent grave ( ` ). The thing is, your sql query generator adds the accent grave automatically. And also it makes use of the ALIAS. So you have a base issue there that you have to solve first. Then you can make ALIAS work. The query i'm using is kind of complex and I have to use ALIAS because I have to get data from wp_metada and wp_usermeta of many ids, so ALIAS are crucial. For now i'll be using VIEWS.
Another thing I notice, the export feature only exports the rows that are loaded. Isn't there a way to export all the rows at once? If the table is big you can export in bulk, but just exporting the rows you are seeing? That is strange.
Thanks in advance!
Hi Nicolas,
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.
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.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hi Milos, that is an infortunate limitation. When you have a table like a report, you want to export all the rows so you need to export in limited bulk until all rows are exported. The solution you give is not a very good thing to do on server resources.
We need a solution to use server side to navigate the datatable and to be able to export all rows depending on the filters.
Hi Nicolas,
I see what you mean. You are right, currently we do not have an ideal solution for larger tables, to export all rows and have only the filtered ones, without having a performance impact on the server when hitting "show all rows" via the Table Tool export.
And with the report builder, if we set "follow table filtering", then it also exports only the visible rows when server side is enabled.
So at the moment we don't have a valid solution to cover all parameters needed for that use case.
You can suggest it to our developers, though - they will do their best to make a solution in the future.
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.
-
If you have coding skills and wish to try to make a custom solution now,
you can check out our available hooks for Developers on this documentation and see if you can find any hook that might help.
We also added this new developer's handbook, which can allow users with coding skills to make custom add-ons.
Our developers will keep adding more custom hooks/examples there over time.
-
Please be advised that custom solutions with hooks are not included in our support.
You can also research resources such as Stack Overflow to see if any other user perhaps found a workaround.
( We do like to give examples for certain solutions, but for this use-case, we, unfortunately, don't have anything yet)
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables