I am following your instructions to disable server-side processing to allow filters to work, but when I do that, it disables Editing. Now, with the latest version of wpdatatables, the filters do not work with Server-side processing disabled or enabled. So, now I am stuck, I need filters and editing to work. What is the status of the fix for this? It is making the plugin difficult to use. There are only so many workarounds...
Is anyone else having this problem? I am on wordpress 4.9 and wpdatatables 2.1.
I also experienced this. The solution is to simply create a VIEW in MySQL (phpmyadmin can be used for this). Then you use the VIEW to create your data like
SELECT * FROM `my_view` or SELECT `var1`, `var2` FROM `my_view`
The VIEW allows you to have server side processing turned on, no matter how complex the query is. Then you can enable editing, basically having your cake and eating it.
Unfortunately, the documentation is rather poor on these issues of server side processing and its implications. I have already asked the developers to update their documentation and I think server side processing should not disable anything at all
lethalmiko, thank you for the advice, but even with a view, filters do not work with server-side enabled. Editing does though, so I’m right back where I started.
I did used a selective query though, maybe ill try a Select * in my view instead.
I’m hoping the wpdatatables team can fix this. I’ve posted about this isssue 3 times now, and it seems like I am not the only one experiencing this. It cripples the functionality of the plugin.
So I decided to try setting the mysql view to just SELECT * FROM `table`... then in wpdatatabes limit the query using the view, but only choosing whichever columns I needed to show. It still does not work.
lethalmiko, do you have a specific way that you know works?
1. Create a view using a pure SELECT statement with no WHERE conditions.
eg SELECT `id`, `value1`, `value2` FROM `your_table` ORDER BY `value2` DESC
2. Create the table in the plugin and you can add WHERE conditions if you want.
eg SELECT `id`, `value1`, `value2` FROM `view_name` WHERE `value1` = '%VAR1%' AND `value2` = '%VAR2%'
3. Enable server side processing, sorting, advanced column filters, search block, show filters above table.
4. Allow front end editing, select the (original) table to be edited (NOT THE VIEW!) and select primary key ID column ("id" in this example) for editing (the primary key is supposed to be among the columns in the view).
After doing this, all the filters worked for me, as well as the main search block and I was also able to edit.
@Lethalmiko thank you for the walkthrough. Sadly, it still doesn't work. I tried clearing my cache, a different browser, recreating the view and wpdatatable with the exact options enabled you have, but no luck using the filters or search. I appreciate it though!
I'm on latest Wordpress and latest wpdatatables. Server is Debian Stretch running Apache 2.4, MariaDB and PHP 7.0.
I did:
1) create new view in phpmyadmin. In the view I didn't put any WHERE clause.
2) create new table in wpdatatables, using MySQL table and selecting the view from the table list. At this point I put my WHERE clause using wpdatatables interface.
NOTE: editing an existing table didn't work for me
3) enable server side and edit :)
4) Be aware: edit doesn't work if your view is a JOIN from two or more tables.
Examples:
view1: SELECT id, name, surname FROM users => server side OK + edit OK
view2: SELECT id, name, city FROM users, cities => server side OK + edit OK
view3: SELECT id, name, city FROM users INNER JOIN cities ON ... => server side OK but NO edit
Please note that there are five options at the beginning of creating a table. Two of these are "Create a table linked to an existing data source" (first option) and "Generate a query to MySQL database" (fifth option). Try the first one and see if it works. Then the fifth.
You could also post your query for creating the view and the query in the plugin so that we try to reproduce your steps.
I am following your instructions to disable server-side processing to allow filters to work, but when I do that, it disables Editing. Now, with the latest version of wpdatatables, the filters do not work with Server-side processing disabled or enabled. So, now I am stuck, I need filters and editing to work. What is the status of the fix for this? It is making the plugin difficult to use. There are only so many workarounds...
Is anyone else having this problem? I am on wordpress 4.9 and wpdatatables 2.1.
Hi,
I'm experiencing the same issue.
Edit needs Server side, but Server side blocks filters.
I also experienced this. The solution is to simply create a VIEW in MySQL (phpmyadmin can be used for this). Then you use the VIEW to create your data like
SELECT * FROM `my_view` or SELECT `var1`, `var2` FROM `my_view`
The VIEW allows you to have server side processing turned on, no matter how complex the query is. Then you can enable editing, basically having your cake and eating it.
Unfortunately, the documentation is rather poor on these issues of server side processing and its implications. I have already asked the developers to update their documentation and I think server side processing should not disable anything at all
lethalmiko, thank you for the advice, but even with a view, filters do not work with server-side enabled. Editing does though, so I’m right back where I started.
I did used a selective query though, maybe ill try a Select * in my view instead.
I’m hoping the wpdatatables team can fix this. I’ve posted about this isssue 3 times now, and it seems like I am not the only one experiencing this. It cripples the functionality of the plugin.
Please help!
So I decided to try setting the mysql view to just SELECT * FROM `table`... then in wpdatatabes limit the query using the view, but only choosing whichever columns I needed to show. It still does not work.
lethalmiko, do you have a specific way that you know works?
@jko5er,
Here is what I did that worked:
1. Create a view using a pure SELECT statement with no WHERE conditions.
eg SELECT `id`, `value1`, `value2` FROM `your_table` ORDER BY `value2` DESC
2. Create the table in the plugin and you can add WHERE conditions if you want.
eg SELECT `id`, `value1`, `value2` FROM `view_name` WHERE `value1` = '%VAR1%' AND `value2` = '%VAR2%'
3. Enable server side processing, sorting, advanced column filters, search block, show filters above table.
4. Allow front end editing, select the (original) table to be edited (NOT THE VIEW!) and select primary key ID column ("id" in this example) for editing (the primary key is supposed to be among the columns in the view).
After doing this, all the filters worked for me, as well as the main search block and I was also able to edit.
@Lethalmiko thank you for the walkthrough. Sadly, it still doesn't work. I tried clearing my cache, a different browser, recreating the view and wpdatatable with the exact options enabled you have, but no luck using the filters or search. I appreciate it though!
Hi,
I confirm that Lethalmiko solution worked for me.
Be aware of using the correct commas: `value2` and not 'value2'.
So I think we should talk about "lack of documentation" instead of "bug".
Thanks to all.
ferdibassy...
Are you on latest Wordpress and wpdatatables? Did you do this from scratch, or alter an existing wpdatatable?
Hi,
I'm on latest Wordpress and latest wpdatatables. Server is Debian Stretch running Apache 2.4, MariaDB and PHP 7.0.
I did:
1) create new view in phpmyadmin. In the view I didn't put any WHERE clause.
2) create new table in wpdatatables, using MySQL table and selecting the view from the table list. At this point I put my WHERE clause using wpdatatables interface.
NOTE: editing an existing table didn't work for me
3) enable server side and edit :)
4) Be aware: edit doesn't work if your view is a JOIN from two or more tables.
Examples:
view1: SELECT id, name, surname FROM users => server side OK + edit OK
view2: SELECT id, name, city FROM users, cities => server side OK + edit OK
view3: SELECT id, name, city FROM users INNER JOIN cities ON ... => server side OK but NO edit
Hope this help.
@jko5er,
Please note that there are five options at the beginning of creating a table. Two of these are "Create a table linked to an existing data source" (first option) and "Generate a query to MySQL database" (fifth option). Try the first one and see if it works. Then the fifth.
You could also post your query for creating the view and the query in the plugin so that we try to reproduce your steps.