Hi primalforce,

When working with SQL query based tables, please note:

1. 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 like DROP and DELETE, though, are disabled for security reasons.
2. Using variables ( @ ), stored procedures and nested queries is not supported, but you can prepare a MySQL view directly in your Database Management Tool(which will return the data you need); call it “view1” for example, and then build a wpDataTable based on a simple query like “SELECT * FROM view1”.
3. For MySQL engine we are dynamically adding the accent grave ( ` ), so there’s no need to use it around the table name in the query.

I read your old ticket in which you also additionally described what was happening before.

First to point out the most important detail to be aware of.

Our Plugin's SQL Query Feature is not the same as a Database Management Tool and is not intended to be used as one.

Our SQL Query functionality is going through our PHP&SQL Parser, which has certain limitations.

For example - a specific Custom Query might work in your SQL Database directly ( For example if you run a Query through a PhPMyAdmin or any other DB Management Tool);

 but the same Query might not work in our SQL Feature of the Plugin, because we run everything through our PHP & SQL Parser, which has certain limitations.

We will do our best to advise further on it.

On 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 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, 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 complex 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, did my best to pass our developer Team's explanations.

So to solve this particular issue, you would need to either try and somehow simplify your SQL Query in order for our server-side processing to work;

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.

Let us know if you manage to simplify the Query for our Parser, or if disabling Server-Side processing helps;

But if I understood, you tried to add some SQL syntax that is simply not possible with our Plugin's SQL Parser.

If that is the case, the only advice we can give is to try with an SQL VIEW, as a colleague mentioned on your other ticket.

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.

I hope one of these methods might solve the issue.

I can see that you created a completely custom solution with adding the filter/hook.

Please be advised, our Support does not cover custom solutions with hooks;

and we don't cover writing or debugging Custom SQL Queries.

If you wish to see a built-in improvement for our SQL Query feature where we could include the additional syntax/SQL function you need, you can make a development suggestion, but I can't say an ETA if or when that might be possible.

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.

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)

Hi primalforce,

My apology, I just forgot to share one custom workaround idea that might be useful, for example if you need to dynamically change an SQL Query from our Table via PHP or via API.

You can take a look at our available filters for developers here ;

 https://wpdatatables.com/documentation/information-for-developers/filters/

-

They pointed out this one : 

wpdatatables_filter_mysql_query( $query, $tableId )

because you can use that to manipulate the Query.

There is also an option with Placeholders,

but combining these two or perhaps even just by using that hook/filter,

you might be able to achieve this use-case.

We do like to give examples for certain solutions, but for this use-case, we, unfortunately, don't have anything yet, so at this time you can only try to make a custom solution to make this work.

That is just an additional idea which i forgot to mention, but please also go through the other details from my previous reply.

I hope that helps.