PLEASE NOTE: I was disallowed uploading more than 4 images to the ticket. So I have uploaded them elsewhere and reference them by link in the problem description.
Table Description
We utilize wpDataTables to render a table of user data based on a SQL query
For the query we utilize placeholder values (VAR1 and VAR2) to select for the data we want to display (Users and music xml files)
As necessary in the table edit screen, we enter some default values here to populate the 'example' table rendering (VAR1 VAR2 Placeholder Entries)
This correctly populates the available search options on the backend, table edit screen (UserName_Search_Backend.png)
On the front-end however, the values are not populated with the table data but with the same values that get populated from the backend placeholder data (UserName_Search_Frontend.png)
Investigation Steps
This occurs on each of our tables
We have attempted unchecking server-side processing and leaving it checked, with no change in behavior between the two
We turned off the search option, leaving only a multiselect box
I looked into my browser dev tools, it appears that the initial table population passes the variables correctly to the ajax call (Initial_Request_Frontend.png). But the subsequent select box ajax call only passes the table id and column name (SearchBox_Request_Frontend.png), which explains why it would not be able to return the correct data. It seems this ajax request would also need to contain the VAR1 and VAR2 parameters
The link provided points to a a test page that demonstrates the problem
Thanks for your reply I replied to in the private issue over a month ago but didn't hear from you since. So here it is in the public ticket:
Just to be clear, we are not attempting to populate the filter list via the VAR values as you say here:
"The VAR placeholders can be used for dynamic filtering - but you can not place them as possible dynamic values for a dropdown filter."
Likewise, this presumption is incorrect:
"I presume you tried to place a full list of all possible values here, then the expected behaviour was for those to appear as possible values in a filter."
We are in fact doing what you state here:
"SELECT * FROM yourDatabaseTable
WHERE columnName = '%VAR1%'
Then, the default filtered value is the one you set in the VAR1 Placeholder setting on Back-end."
We use the VAR1 value and VAR2 value in our SQL query to populate the data table.
The comma separated list is used in a rather lengthy query we have, which I have left the beginning out of for brevity. Here is our WHERE clause for this query:
```
<REST OF OUR QUERY HERE>
....
WHERE %WPDB%phonicscore_practicebird_practice_sessions.`owner_id` IN
(
%VAR1%
)
AND %WPDB%phonicscore_practicebird_practices.`music_piece_id` IN
(
%VAR2%
)
```
This yields the data in the table correctly that we desire (we are able to use the comma separated list in this way and it works as expected). It yields the correct different data that we desire based on the values in those VAR's.
The actual problem we are experiencing is that it appears that the filter values are not dynamically populated based on the data in the table currently being displayed.
The values are always populated based on the result of the query that occurs with our "test" placeholder values that must be entered in the admin editor section of the table.
Please correct me if I am wrong here, but it appears your statement here:
"The only way to define which filter values are possible for a dropdown filter can be manually set in the Column settings/data tab."
Indicates that the filter options are not populated based on the current data in the table, but must always be predefined in some way (either manually, or as we are experiencing, with the initial table data from the table edit screen values).
Is this correct? If so, is there a recommended way to accomplish populating the filter values with the data from the current data in the table (which is itself populated via a SQL query containing placeholder VAR's)?
Perhaps, based on your example given here:
"
If we want page 1 for example only filtered for "Region A" , you can set predefined filter to this column as %VAR1% (filter type has to be set as text) :
Then for a page where we need only Region 1 filtered, use shortcode [wpdatatable id=1 var1='Region A'] ,"
Could we define var1 as a comma separated list of values possibly? I may be able to work around with this.
PLEASE NOTE: I was disallowed uploading more than 4 images to the ticket. So I have uploaded them elsewhere and reference them by link in the problem description.
Table Description
Problem Description
Investigation Steps
Hi Miloš ,
Thanks for your reply I replied to in the private issue over a month ago but didn't hear from you since. So here it is in the public ticket:
Just to be clear, we are not attempting to populate the filter list via the VAR values as you say here: "The VAR placeholders can be used for dynamic filtering - but you can not place them as possible dynamic values for a dropdown filter." Likewise, this presumption is incorrect: "I presume you tried to place a full list of all possible values here, then the expected behaviour was for those to appear as possible values in a filter." We are in fact doing what you state here: "SELECT * FROM yourDatabaseTable WHERE columnName = '%VAR1%' Then, the default filtered value is the one you set in the VAR1 Placeholder setting on Back-end." We use the VAR1 value and VAR2 value in our SQL query to populate the data table. The comma separated list is used in a rather lengthy query we have, which I have left the beginning out of for brevity. Here is our WHERE clause for this query: ``` <REST OF OUR QUERY HERE> ....
WHERE %WPDB%phonicscore_practicebird_practice_sessions.`owner_id` IN ( %VAR1% ) AND %WPDB%phonicscore_practicebird_practices.`music_piece_id` IN ( %VAR2% )
```
This yields the data in the table correctly that we desire (we are able to use the comma separated list in this way and it works as expected). It yields the correct different data that we desire based on the values in those VAR's.
The actual problem we are experiencing is that it appears that the filter values are not dynamically populated based on the data in the table currently being displayed. The values are always populated based on the result of the query that occurs with our "test" placeholder values that must be entered in the admin editor section of the table. Please correct me if I am wrong here, but it appears your statement here:
"The only way to define which filter values are possible for a dropdown filter can be manually set in the Column settings/data tab."
Indicates that the filter options are not populated based on the current data in the table, but must always be predefined in some way (either manually, or as we are experiencing, with the initial table data from the table edit screen values).
Is this correct? If so, is there a recommended way to accomplish populating the filter values with the data from the current data in the table (which is itself populated via a SQL query containing placeholder VAR's)?
Perhaps, based on your example given here:
"
If we want page 1 for example only filtered for "Region A" , you can set predefined filter to this column as %VAR1% (filter type has to be set as text) :
Then for a page where we need only Region 1 filtered, use shortcode [wpdatatable id=1 var1='Region A'] ,"
Could we define var1 as a comma separated list of values possibly? I may be able to work around with this.
Thanks again for your time.
-Thomas