Hello Marek & Bartek.

The SQL query based table sometimes doesn’t work correctly with server-side processing, yes. Most likely, this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically. MySQL view would need to be prepared to work around the issue.

Please note that MySQL query constructor is not an ‘ultimate generator’, it should be treated as helper for constructing a suggestion of a query, by trying to ‘guess’ what you want. We are constantly working to improve it, but SQL is such a complicated and flexible language that fully automating the process for constructing queries is hardly possible. Consequently, the more complicated your request is, the higher is the probability that it will not return exactly what you need.

Please note some this when working with the server-side processing feature:

• Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
• Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.

We have users that have prepared views for tables that consist of around million of entries, and both search and filters are working fine. But please note that MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID.

Best regards.

Hello again guys.

MySQL is most commonly used, yes. And I have had most experience working with it. There were some customers I came across that had very complicated queries that worked great with MySQL. On the other hand, I had others that had to create a VIEW for simpler queries. 

Unfortunately, SQL is such a complicated and flexible language that fully automating the process for constructing queries is hardly possible. Consequently, the more complicated your request is, the higher is the probability that it will not return exactly what you need.

Our plugin requires minimum version of PHP 5.6, but we recommend updating to 7 or more.

Best regards.

Good news, Marek, so to speak

Thanks for the update.

If you run into anything else, please feel free to reach out to us.

Best regards.