We have provided some of our PODS types with categories that are organized hierarchically. Similar to the example “Hierarchical Taxonomy: Cuisine” here.
To display the PODS entries, we use the shortcode PODS and the filter instruction with where.
We realize that we can filter the list according to the lowest level of the taxonomy, like where="cuisine.slug='french'".
Is there also a way to filter by the next level of the taxonomy, something like where="cuisine.parent.slug='european'"?
Regards Friedbert
So far we used a nested SQL query.
where="cuisine.term_id IN (SELECT t.term_id FROM wp_terms p JOIN wp_term_taxonomy t ON p.term_id=t.parent WHERE p.slug='european')"
Unfortunately, this is no longer supported. For some time now, this has led to the error message:
Pods Embed Error: WHERE contains SQL that is not allowed
It seems that this problem is not relevant. I thought there should be ready-made solutions. Now I have experimented myself and found the following solution.
There is an undocumented (or poorly documented) feature in the PODS shortcode: The "join" attribute can be used to introduce a SQL fragment that is inserted at the appropriate place in the SQL query. This replaces the previously nested SQL query.
Joins via table wp_term_taxonomy to the table wp_terms take you to the parent category.
So, I have added a corresponding join clause:
join="LEFT JOIN wp_term_taxonomy AS a ON cuisine.term_id=a.term_id
LEFT JOIN wp_terms AS q ON a.parent=q.term_id"
changed my where clause:
where="cuisine.term_id and q.slug='european'"
where the fragment cuisine.term_id ensures that the field cuisine is available in the SQL query.
Regards Friedbert