"Filtering by Taxonomy" (#2722654) / TMS-Plugins

Comments

David Simpson started the conversation
March 16, 2021 at 8:34pm
I am trying to create a table by querying the Wordpress Posts. I have no trouble creating a table of Posts, including the Tags/Category Taxonomy. The problem starts when I try to FILTER by taxonomy. If I add a condition, like:
post.taxonomy.category = Stocks
it returns an error like this:
No results found. Please check if this query is correct! Table Constructor needs a query that returns data to build a wpDataTable.
Error: Unknown column 'post_taxonomy_category' in 'on clause'
I've tried searching by category ID#, Title, and Slug, and get the same result each time. If I remove the filter and click Next, wpDataTables displays a preview of the table, including the taxonomy info, with no trouble. The moment I add any taxonomy filter, I get the error. Other filters, such as "by author", work just fine.
[deleted] replied
March 17, 2021 at 11:38am
Hi David

Thank you for reaching out to us.

I suppose you used our Query Constructor to create that table.

Please note that this tool is not an ultimate query generator. It simply constructs a suggestion of a query. We are constantly working to improve it, but SQL is such a complicated and flexible language that full automation for constructing queries is next to impossible. Consequently, the more complicated the query, the higher is the probability that it will not return exactly what you need. So, you will often need to play around with the resulting query.

Issues with editing, sorting and/or filtering can happen with tables that contain multiple JOINs, CONCATs, and other statements in the query. If this is the case on your server-side table, you can either disable server-side processing (if you don't need the table to be editable, and you don't have over 2.000 rows of data), or 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.
Could I kindly ask you to send us the query so we can take a closer look?