Hi Régis,

If it works in PHPMyAdmin with exactly the same Query - This is because our Plugin's SQL Query is not the same as a database management tool and is not meant to be used as one.

Our SQL Queries are running through a PHP & SQL Parser, which has certain limitations, especially if you combine it with active server-side processing.

You can first try disabling the server-side processing option in the Data settings, check if that returns the data you need?

If you have to use server-side processing, we will advise what you can try :

As mentioned, our SQL Feature is not the same as a Database Management Tool, so for example - a specific Custom Query might work in your SQL Database directly but not in our SQL Feature of the Plugin, because we run everything through our PHP & SQL Parser, which has certain limitations.

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, 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);

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.

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;

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.

I hope one of these methods might solve the issue.

Hi Régis,

Sorry, I just re-read the details of your Query and thought of something else you could try.

I believe you can try a very similar Query, just when you call data from more than one table, you should either use an SQL JOIN or UNION function.

You will find various tutorials on this online, for example check this one from W3Schools on how to use SQL JOIN;

And here you can check how you should use UNION function.

Both of these are used when you merge/call data from multiple SQL Tables.

You can also try our 'MySQL query generator' helper tool.  It can help to try to 'guess' the right Query for you, so you can just pick your tables and columns  you need from the Database in the interface;

then add any conditions such as relations, etc.

Please note that this generator is not a perfect tool - it only tries to 'guess' the right Query, so sometimes you need to experiment/modify the resulting Query to achieve your goal.

I hope it helps, maybe this can be the easiest solution first - then you can try the other points mentioned from the previous reply.

Let me know how it goes.