Find using a hierarchical taxonomy

  • Resolved WidiGMX

    (@widigmx)


    10 months, 3 weeks ago

    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="cusine.slug='french'".
    Is there also a way to filter by the next level of the taxonomy, something like where="cusine.parent.slug='european'"?

    Regards
    Friedbert

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter WidiGMX

    (@widigmx)

    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

    Plugin Support Paul Clark

    (@pdclark)

    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.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Find using a hierarchical taxonomy’ is closed to new replies.