Is there a way to filter out rows from a dataset returned from a MySQL query? Example, I have a large table from a dynamic dataset retrieved via a complex MySQL query. I want to omit rows with a value of <x from displaying in the wpDataTable. The query will always return aggregated data from the full 125 row dataset, but I only want it to display the rows matching a certain criteria. Can this be done?
Thank you. I am familiar with such operators in MySQL. This is a very complex query (with 6 subqueries), I was hoping there was a way to have the table only rows matching certain criteria. Please let me know if such a filter exists and, if so, where the documentation is for it.
I only know about filters via SQL. But perhaps the support team knows other options.
Alternatively, you could enable filtering on a column for the end user and predefine the maximum value there. Then you would only have to disable the box via CSS so that the user cannot change it.
Agreed, not as elegant as I'd like, but defiinitely doable. Now, to figure out how to enable filtering for this field and set the minimum instead of a strict value...
Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.
1. In regards to your initial question, Jens gave some interesting ideas, thank you for that @Jens.
We will first show you some general ways how you can predefine filters with our Plugin, it can be through the SQL Query itself with a WHERE statement, like Jens wrote, whether that was a direct/fixed manual value;
or if you try our Placeholders as VAR/variables, then insert a different variable via table shortcode etc;
SELECT * FROM yourDatabaseTable
WHERE columnName > '%VAR1%'
This would, of course, show only the values higher than the one set via the VAR placeholder.
%VAR1% can be defined in the Placeholders tab when you're creating a table,
or you can pass a different VAR1 value in the table's shortcode.
-
2. Or, if you don't add the condition in an SQL query ( or don't use an SQL Table),
you can add %VAR1% in column settings -> Filtering, under "Predefined filtering value".
Then, you can have different versions of the same table, with the VAR1 value passed in the shortcode,
let's say if we had a "Regions" column and we wish to filter a different region per each page.
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'] ,
page 2 for only "Region B", shortcode would be [wpdatatable id=1 var1='Region B'] and so on,
to apply the values defined in the shortcode to the filter.
2. To be more specific for your use-case, I did not fully understand your question, where you wrote :
This is a very complex query (with 6 subqueries), I was hoping there was a way to have the table only rows matching certain criteria. Please let me know if such a filter exists and, if so, where the documentation is for it.
We have advanced column filters available, as explained in this Documentation.
If you have one( or more columns) which you could filter on, you can add multiple column filters as part of the Query or directly in the Columns as predefined filters;
So, when it comes to filters - our Plugin can only filter per each column;
you can, of course combine multiple filters from multiple columns; but I did not fully understand that part, so if you can try to clarify it - add some screenshots or a Video, then we can advise?
Of course, if you are skilled in SQL, you can also try any kind of SQL operator and that should work;
Just be advised that our Plugin is not the same as a Database Management Tool;
We use an SQL Parser, so some complex Query might work in the DB but if you encounter an issue in our SQL Table;
such as if it returns the data as needed, but a filter/search does not work :
Our logic is based on a PHP SQL parser which has full support for the SQL dialect for the following statement types
Filtering, sorting, and search may not work properly if you include:
Accent graves ( ` ) around the table name
JOIN functions
UNION functions
CONCAT functions
sub-queries
-
So, first you can check for accent graves around the table name, if you have this, remove it...
Then, see if you used CONCAT to create any column.
If so, go into this column setting, and disable it from "global search" in the Filtering tab.
-
-
If none of that helps,
you can try preparing a MySQL view (which will return the data that you need, call it e.g. “view1” and then build a wpDataTables based on a simple query like "SELECT * FROM view1″.
If you need help with that, you can see our video, where we show an example of using View in our plugin.
3. Just to additionally comment on Jenses approach with the Number Range and the predefined filter, but then to disable the filtering for the table;
That is a nice idea, thanks for that, Jens.
This can work - if you wish to completely disable filtering on this Table, just note that then users will not be able to use any kind of column filters on your table.
If you wish to allow them to use other column filters, but only hide this one - then you need to activate the Filters above the Table, and use CSS to hide only this specific column filter.
Here is my example, if we have this table and want to set predefined MIN value as a Number Slider filter for "price" column.
If we want it to only load values from 50 and upwards ( max value in my table is 120 in that field) :
Now, the users can still move the MIN slider and see other values.
If you enable the filters above the table like this :
And use custom CSS to select only the Column you need and hide its filter :
Is there a way to filter out rows from a dataset returned from a MySQL query? Example, I have a large table from a dynamic dataset retrieved via a complex MySQL query. I want to omit rows with a value of <x from displaying in the wpDataTable. The query will always return aggregated data from the full 125 row dataset, but I only want it to display the rows matching a certain criteria. Can this be done?
Hallo Adam,
If you use SQL query as a source type, you can filter it out within the SQL query.
You can add a simple WHERE condition here.
SELECT field1, field2 FROM tablename WHERE field2 < 'your value'
If you use the same query often on your site with different conditions, you can also do this with transfer variables.
SELECT field1, field2 FROM tablename WHERE field2 < '%VAR1%'
Kind regardsJens
Thank you. I am familiar with such operators in MySQL. This is a very complex query (with 6 subqueries), I was hoping there was a way to have the table only rows matching certain criteria. Please let me know if such a filter exists and, if so, where the documentation is for it.
Hello Adam,
I only know about filters via SQL. But perhaps the support team knows other options.
Alternatively, you could enable filtering on a column for the end user and predefine the maximum value there. Then you would only have to disable the box via CSS so that the user cannot change it.
A bit unsightly but doable.
Agreed, not as elegant as I'd like, but defiinitely doable. Now, to figure out how to enable filtering for this field and set the minimum instead of a strict value...
2 step:
![9969785095.png](https://ticksy_attachments.s3.amazonaws.com/9969785095.png)
- Click on the setting of the column you want to filter
- select the tab filtering and add the range
If you leave the checkbox unchecked in step 1 at "Filters in a form" you don't have to hide it at all.
That would be perfect :-)
Thank you!
Hi Adam,
Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.
1. In regards to your initial question, Jens gave some interesting ideas, thank you for that @Jens.
We will first show you some general ways how you can predefine filters with our Plugin, it can be through the SQL Query itself with a WHERE statement, like Jens wrote, whether that was a direct/fixed manual value;
or if you try our Placeholders as VAR/variables, then insert a different variable via table shortcode etc;
or via the Advanced Column filters in general.
Here are some basic examples :
If you wish to try Pre-filtering tables through URL, please check out this page with examples.
-
You can also use placeholders for table generation.
1. For example, if you have a MySQL query-based table, you can use this query:
This would, of course, show only the values higher than the one set via the VAR placeholder.
%VAR1% can be defined in the Placeholders tab when you're creating a table,
or you can pass a different VAR1 value in the table's shortcode.
-
2. Or, if you don't add the condition in an SQL query ( or don't use an SQL Table),
you can add %VAR1% in column settings -> Filtering, under "Predefined filtering value".
Then, you can have different versions of the same table, with the VAR1 value passed in the shortcode,
let's say if we had a "Regions" column and we wish to filter a different region per each page.
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'] ,
page 2 for only "Region B", shortcode would be [wpdatatable id=1 var1='Region B'] and so on,
to apply the values defined in the shortcode to the filter.
2. To be more specific for your use-case, I did not fully understand your question, where you wrote :
This is a very complex query (with 6 subqueries), I was hoping there was a way to have the table only rows matching certain criteria. Please let me know if such a filter exists and, if so, where the documentation is for it.
We have advanced column filters available, as explained in this Documentation.
If you have one( or more columns) which you could filter on, you can add multiple column filters as part of the Query or directly in the Columns as predefined filters;
So, when it comes to filters - our Plugin can only filter per each column;
you can, of course combine multiple filters from multiple columns; but I did not fully understand that part, so if you can try to clarify it - add some screenshots or a Video, then we can advise?
Of course, if you are skilled in SQL, you can also try any kind of SQL operator and that should work;
Just be advised that our Plugin is not the same as a Database Management Tool;
We use an SQL Parser, so some complex Query might work in the DB but if you encounter an issue in our SQL Table;
such as if it returns the data as needed, but a filter/search does not work :
Our logic is based on a PHP SQL parser which has full support for the SQL dialect for the following statement types
SELECT, INSERT, UPDATE, DELETE, REPLACE, RENAME, SHOW, SET, DROP, CREATE INDEX, CREATE TABLE, EXPLAIN and DESCRIBE.
Some of them are disabled for security reasons.
Filtering, sorting, and search may not work properly if you include:
-
So, first you can check for accent graves around the table name, if you have this, remove it...
Then, see if you used CONCAT to create any column.
If so, go into this column setting, and disable it from "global search" in the Filtering tab.
-
-
If none of that helps,
you can try preparing a MySQL view (which will return the data that you need, call it e.g. “view1” and then build a wpDataTables based on a simple query like "SELECT * FROM view1″.
If you need help with that, you can see our video, where we show an example of using View in our plugin.
3. Just to additionally comment on Jenses approach with the Number Range and the predefined filter, but then to disable the filtering for the table;
That is a nice idea, thanks for that, Jens.
This can work - if you wish to completely disable filtering on this Table, just note that then users will not be able to use any kind of column filters on your table.
If you wish to allow them to use other column filters, but only hide this one - then you need to activate the Filters above the Table, and use CSS to hide only this specific column filter.
Here is my example, if we have this table and want to set predefined MIN value as a Number Slider filter for "price" column.
If we want it to only load values from 50 and upwards ( max value in my table is 120 in that field) :
Now, the users can still move the MIN slider and see other values.
If you enable the filters above the table like this :
And use custom CSS to select only the Column you need and hide its filter :
#table_1_10_filter_sections {
display: none !important;
}
You will notice in the HTML, all these DIV containers have a similar name for all column filters, they start like :
#table_1_0_filter_sections
#table_1_1_filter_sections
#table_1_3_filter_sections
and so on... So you just need to isolate the right filter number and insert it in the CSS selector for this to work.
We hope this helps, thanks to Jens for these additional workaround ideas again.
Let us know how it goes and if you have any additional questions about that.
Kind Regards,
Miloš Jovanović
[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