We are using this plugin to construct a table and after that a graph based on a SQL query. We achieve the table perfectly but we can not achieve that filters in every field work and also the search doesnt work.
For us this is really important to work with this pluging.
I dont know if is important but I put here the query:
-----
SELECT `wp_users`.id AS idgeneral,`wp_users`.user_email, DATE_FORMAT(`wp_users`.user_registered, "%Y-%m-%d") AS 'Fecha de Registro', `Nombre`.`value` AS Nombre , `Apellidos`.`value` AS Apellidos, `prof`.`meta_value` AS Perfil, `NIFFarmacia`.`value` AS 'NIF Farmacia', `colFarmacia`.`value` AS 'Num colegiado' FROM `wp_users` LEFT JOIN `wp_bp_xprofile_data` AS Nombre ON (`Nombre`.field_id = '1') AND (`wp_users`.id = `Nombre`.`user_id`) LEFT JOIN `wp_bp_xprofile_data` as Apellidos ON (`Apellidos`.field_id = '2') AND (`wp_users`.id = `Apellidos`.`user_id`) LEFT JOIN `wp_bp_xprofile_data` as NIFFarmacia ON (`NIFFarmacia`.field_id = '7') AND (`wp_users`.id = `NIFFarmacia`.`user_id`) LEFT JOIN `wp_bp_xprofile_data` as colFarmacia ON (`colFarmacia`.field_id = '8') AND (`wp_users`.id = `colFarmacia`.`user_id`) LEFT JOIN `wp_usermeta` as prof ON (`prof`.meta_key = 'wp_capabilities') AND (`wp_users`.id = `prof`.`user_id`)
----
Other thing is: i bought powerfull filters for wpDatatables and i dont know how can I enable. I installed without problems and activate the license but i cant see any differences in the options that I have
If you can't filter through your server-side tables, the issue is probably with the query you're using. Any query that has JOIN, CONCAT, GROUP, UNION, etc. statements will cause issues with filtering, search and sorting.
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″.
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.
When it comes to Powerful filtering you won't have an additional tab in your settings like Master details has but instead you will get additional option in your Filtering tab - Cascade filtering and Hide table before it's fully loaded in the Display tab.
For more details please have a look at our documentation.
I hope this helps, do let us know if you need further assistance.
Honestly, we are not able to manage. We are trying everything and we can not see how.
Could yo make one example for us? We can create a backend user and you can convert our SQL query in the right way to provoke that search and filters works. Probably if we see how we can learn easily.
If you need further assistance on how to create a view, please browse through stack overflow, w3schools where you will be able to find more useful information.
I'm glad to hear you were able to resolve the previous issue.
Could you please give us some more details on this issue and if possible send us some screenshots or screen recording so we can understand it more clearly and provide you with the best solution?
Hi
We are using this plugin to construct a table and after that a graph based on a SQL query. We achieve the table perfectly but we can not achieve that filters in every field work and also the search doesnt work.
For us this is really important to work with this pluging.
I dont know if is important but I put here the query:
-----
SELECT `wp_users`.id AS idgeneral,`wp_users`.user_email, DATE_FORMAT(`wp_users`.user_registered, "%Y-%m-%d") AS 'Fecha de Registro', `Nombre`.`value` AS Nombre , `Apellidos`.`value` AS Apellidos, `prof`.`meta_value` AS Perfil, `NIFFarmacia`.`value` AS 'NIF Farmacia', `colFarmacia`.`value` AS 'Num colegiado' FROM `wp_users`
LEFT JOIN `wp_bp_xprofile_data` AS Nombre ON (`Nombre`.field_id = '1') AND (`wp_users`.id = `Nombre`.`user_id`)
LEFT JOIN `wp_bp_xprofile_data` as Apellidos ON (`Apellidos`.field_id = '2') AND (`wp_users`.id = `Apellidos`.`user_id`)
LEFT JOIN `wp_bp_xprofile_data` as NIFFarmacia ON (`NIFFarmacia`.field_id = '7') AND (`wp_users`.id = `NIFFarmacia`.`user_id`)
LEFT JOIN `wp_bp_xprofile_data` as colFarmacia ON (`colFarmacia`.field_id = '8') AND (`wp_users`.id = `colFarmacia`.`user_id`)
LEFT JOIN `wp_usermeta` as prof ON (`prof`.meta_key = 'wp_capabilities') AND (`wp_users`.id = `prof`.`user_id`)
----
Other thing is: i bought powerfull filters for wpDatatables and i dont know how can I enable. I installed without problems and activate the license but i cant see any differences in the options that I have
thank you in advance
Juan Antonio
Hi Juan
Thank you for reaching out to us.
If you can't filter through your server-side tables, the issue is probably with the query you're using. Any query that has JOIN, CONCAT, GROUP, UNION, etc. statements will cause issues with filtering, search and sorting.
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″.
Please note some this when working with the server-side processing feature:
When it comes to Powerful filtering you won't have an additional tab in your settings like Master details has but instead you will get additional option in your Filtering tab - Cascade filtering and Hide table before it's fully loaded in the Display tab.
For more details please have a look at our documentation.
I hope this helps, do let us know if you need further assistance.
Hi
Honestly, we are not able to manage. We are trying everything and we can not see how.
Could yo make one example for us? We can create a backend user and you can convert our SQL query in the right way to provoke that search and filters works. Probably if we see how we can learn easily.
What do you think?
Thank you in advance
Hi Juan
Please have a look at this video about using JOIN and creating views: https://www.youtube.com/watch?v=xCS6hlnwuZU
If you need further assistance on how to create a view, please browse through stack overflow, w3schools where you will be able to find more useful information.
Hi Juan Antonio
I'm glad to hear you were able to resolve the previous issue.
Could you please give us some more details on this issue and if possible send us some screenshots or screen recording so we can understand it more clearly and provide you with the best solution?