To produce a mega-menu for a recent WordPress project, I had a requirement to show all the terms within one taxonomy that were associated with Posts that a specific term from a second taxonomy was also associated with. Tricky. It took a little while to get my head around the problem, but I came up with a solution that did just what was needed.
if ( !function_exists( 'get_associated_terms' ) ) {
function get_associated_terms( $taxonomy_slug, $term_id = 0, $post_type = 'post' ) {
global $wpdb;
$sql = "SELECT DISTINCT $wpdb->terms.term_id, $wpdb->terms.name, $wpdb->terms.slug, $wpdb->terms.term_group, $wpdb->term_taxonomy.term_taxonomy_id, $wpdb->term_taxonomy.taxonomy, $wpdb->term_taxonomy.description, $wpdb->term_taxonomy.parent, $wpdb->term_taxonomy.count
FROM $wpdb->terms
INNER JOIN $wpdb->term_taxonomy ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id
INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
INNER JOIN $wpdb->posts ON $wpdb->term_relationships.object_id = $wpdb->posts.ID
INNER JOIN $wpdb->term_relationships tr2 ON $wpdb->posts.ID = tr2.object_id
INNER JOIN $wpdb->term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
WHERE $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = %s AND $wpdb->term_taxonomy.taxonomy = %s AND tt2.term_id = %d
ORDER BY $wpdb->terms.name";
$safe_sql = $wpdb->prepare( $sql, $post_type, $taxonomy_slug, $term_id );
$results = $wpdb->get_results( $safe_sql, OBJECT );
return $results;
}
}
Once added to the WordPress theme functions.php file or a plugin, its used in the same way you’d use one of the built-in functions, like get_terms(), except my function accepts fewer parameters. It returns an Object that can be looped through in the normal way.
$terms = get_associated_terms( 'tax1', 256 );
foreach($terms as $term) {
echo '<a href="/tax1/' . $term->slug . '" class="' . $term->taxonomy . '">' . $term->name . '</a>';
}
The first parameter in the function is the name of the taxonomy you wish to return. The second parameter is the ID of a term within your starting taxonomy. Running my example would return an Object containing all terms within the ‘tax1’ taxonomy which are assigned to Posts that also have term 256 assigned to them.