There is critical problem with filtering in sql table when there is JOIN in query. See screens.
Query was generated with your Generate a query to the MySQL database.
Of course filtering starts to work when I disable Server Side Processing.
Please do something with that! And PLEASE don't send me this response:
"if your MySQL-query based wpDataTable doesn’t work correctly
with server-side processing, probably this is happening because
wpDataTables server has problems with parsing of the
query and building new queries dynamically (happens rarely, but does
sometimes). To avoid this please prepare a MySQL view (a stored query),
which will return the data that you need, call it e.g. “view1” and then
build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
This is not enought. My query is simple like this:
SELECT wp_wpdatatable_3.`wdt_ID`,
wp_wpdatatable_3.`time`,
wp_users.`user_email`,
wp_users.`user_nicename`,
wp_users.`display_name`
FROM wp_wpdatatable_3
INNER JOIN wp_users
ON wp_wpdatatable_3.`userid` = wp_users.`ID`
WHERE 1=1
AND wp_wpdatatable_3.`groupid` = '5'
so if I want to filter this by email your PARSER only need to add AND to the and of the query!!!
Please don't mistake wpDataTables for a database management tool. It can create a table using an SQL query, but it is quite limited when it comes to queries that involve JOIN, CONCAT, UNION, etc.
The fact is that a query (although simple in our eyes) like yours is not going to have valid filtering, sorting, and search. It's a limitation of the plugin, and at the moment there is no workaround for it.
If it was that easy to resolve, it would've been resolved a long time ago. So, unfortunately, until our developers figure out a way how you can create queries more complex than SELECT * FROM table and use filtering in them, creating views is the only solution.
IF you show me, where in your files those filter queries are created then I will extend this functionality for tables with JOIN (only for JOIN for now) and I will return you results for everyone's benefit.
There is critical problem with filtering in sql table when there is JOIN in query. See screens.
Query was generated with your Generate a query to the MySQL database.
Of course filtering starts to work when I disable Server Side Processing.
Please do something with that! And PLEASE don't send me this response:
This is not enought. My query is simple like this:
so if I want to filter this by email your PARSER only need to add AND to the and of the query!!!
You need to make it work. Only on our site we need to create 45! views to make it work. This is not possible! And for you this is really small effort.
PS.
Search block don't work at all (it work only when SELECT select only fieldsfrom first table)
Column search fields seems to be working only for first table (from ex. wp_wpdatatable_3)
Hello Piotr
Please don't mistake wpDataTables for a database management tool. It can create a table using an SQL query, but it is quite limited when it comes to queries that involve JOIN, CONCAT, UNION, etc.
The fact is that a query (although simple in our eyes) like yours is not going to have valid filtering, sorting, and search. It's a limitation of the plugin, and at the moment there is no workaround for it.
If it was that easy to resolve, it would've been resolved a long time ago. So, unfortunately, until our developers figure out a way how you can create queries more complex than SELECT * FROM table and use filtering in them, creating views is the only solution.
Sorry for the inconvenience.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
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
IF you show me, where in your files those filter queries are created then I will extend this functionality for tables with JOIN (only for JOIN for now) and I will return you results for everyone's benefit.
I don't know the answer to that question, Piotr, so I forwarded it to our development team.
As soon as I hear back from them I will let you know.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
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
Hello again Piotr
Our developer suggested taking a look at the filter wpdatatables_filter_mysql_query:
wpdatatables_filter_mysql_query( $query, $tableId )
This filter is applied to the MySQL query before it is sent to MySQL server.
$query is the query text
$tableId is the table identifier from the MySQL table (wp_wpdatatables).
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
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