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
Hello Friedbert,
Thank you for following up with your solution. A SQL JOIN certainly does provide more flexibility for advanced queries.
If the desired result is to filter to only terms which have a certain parent or parents, it may be possible without the JOIN by using parent = 2 if the query is on cuisine, cuisine.parent = 2 if the query is on a post type related to cuisine, or cuisine.parent IN( 0, 2, 3, 4 ) for multiple parents, including where there is no parent (parent ID 0).
The difference between this and the initial approach, cusine.parent.slug='european' is the datatype of field parent. Similar to the core WP_Term object, parent returns an ID, but is not necessarily expanded by Pods to a full Term object. So the above WHERE queries check the parent by ID, rather than slug of the parent, removing the need for a JOIN.
Adding a JOIN is certainly a valid approach — just different approaches to consider based on your needs for query speed versus flexibility.
