Getting taxonomy terms used by custom post type

Question

I have a custom post type - case - for which I am building an archive page. There are three custom taxonomies for this type - micro_organisms, infection_types, and antibiotic_types. These taxonomies are hierarchical, and have a variety of terms, up to three levels deep. For the given post type, I'm wanting to list terms for, say, the antibiotic_types taxonomy, that are used by any case entries.

While a term within the antibiotic_types taxonomy may be used by any of the case entries, I want to show the hierarchy leading to the selected term.

So far, I've got the following working, which results in a flat list of just the selected terms, not reflecting the hierarchy the terms are in.

// sourced from:
// http://wordpress.stackexchange.com/questions/66015/how-to-get-a-list-of-taxonomy-terms-which-are-being-used-only-within-certain-pos
function get_terms_by_post_type( $taxonomies, $post_types ) {
    global $wpdb;
    $query = $wpdb->get_results( "SELECT t.*, COUNT(*) AS count from $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN $wpdb->term_relationships AS r ON r.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN $wpdb->posts AS p ON p.ID = r.object_id WHERE p.post_type IN('" . join( "', '", $post_types ) . "') AND tt.taxonomy IN('" . join( "', '", $taxonomies ) . "') GROUP BY t.term_id");
    return $query;
}
?>
...
<ul class="taxonomy-term-list">
<?php
$terms = get_terms_by_post_type(array($tax), array('case'));

foreach ($terms as $term):
    $termData = get_term_by('id', $term->term_id, $tax);
    $termName = $termData->name;
    $termUrl = get_term_link($termData);
?>
    <li class="taxonomy-term"><a href="<?php echo $termUrl; ?>?post_type=<?php echo $type; ?>"><?php echo $termName; ?> (<?php echo $term->count; ?>)</a></li>
<?php
endforeach;
?>
</ul>

The output resembles:

• penicillin-g
• ceftriaxone
• amphotericin-b
• 5-flucytosine

While the desired output is:

• beta-lactams
  • cephalosporins
    • ceftriaxone
  • penicillins
    • penicillin-g
• antifungals
  • amphotericin-b
  • 5-flucytosine

I've also tried using the following:

wp_list_categories(
    array(
        'taxonomy' => 'antibiotic_types',
        'hierarchical' => 1,
        'hide_empty' => 1,
        'title_li' => ''
    )
);

Which shows all of the terms for the given taxonomy, even those not used by any case entries.

Having browsed the function reference, it doesn't look as if there is any built-in function for doing what I want to achieve, not directly anyway. I haven't done much with taxonomy queries until now. Any suggestions?

Answer 1

If you see doc for wp_list_categories in codex, you'll se that it accepts a include param, that should contain a comma separed list of categorys ids.

So if you change your function get_terms_by_post_type creating the function get_terms_id_by_post_type to retrieve only the term ids, just like:

function get_terms_id_by_post_type( $taxonomies, $post_types ) {
    global $wpdb;
    $query = $wpdb->get_col( "SELECT t.term_id from $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN $wpdb->term_relationships AS r ON r.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN $wpdb->posts AS p ON p.ID = r.object_id WHERE p.post_type IN('" . join( "', '", $post_types ) . "') AND tt.taxonomy IN('" . join( "', '", $taxonomies ) . "') GROUP BY t.term_id");
    return $query;
}

Then you can:

// retrieve the term ids used by post type
$terms = get_terms_id_by_post_type( array($tax), array('case') );

// if that terms exist implode in a commasepared string
$term_list = ! empty($terms) ? implode(',', $terms) : false;

// use wp_list_categories with include param to included only used terms
if ( $term_list ) {
  wp_list_categories(
    array(
        'taxonomy' => 'antibiotic_types',
        'hierarchical' => 1,
        'hide_empty' => 1,
        'title_li' => '',
        'include' => $term_list
    )
  );
}

Answer 2

Just in case someone else googling around needs to only show terms from one taxonomy used by CPT posts set to another taxonomy, here's the code I used for that, based on the answer by @gmazzap:

// Get all story post IDs set to story_type of "rn"
$story_post_ids = get_posts([
  'numberposts' => -1,
  'fields'      => 'ids',
  'post_type'   => 'story',
  'tax_query'   => [
    [
      'taxonomy' => 'story_type',
      'field'    => 'slug',
      'terms'    => ['rn'],
      'compare'  => 'IN',
    ]
  ],
]);

// Find story topics in use by those post IDs
$story_topic_ids = $wpdb->get_col("
      SELECT t.term_id FROM $wpdb->terms AS t
      INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id
      INNER JOIN $wpdb->term_relationships AS r ON r.term_taxonomy_id = tt.term_taxonomy_id
      WHERE tt.taxonomy IN('story_topic')
      AND r.object_id IN (".implode(',', $story_post_ids).")
      GROUP BY t.term_id
");

// Pull those topics
$story_topics = get_terms([
  'taxonomy' => 'story_topic',
  'include'  => $story_topic_ids,
]);

This finds all terms of type story_topic that are in use by posts set to the term story_type of "rn."