Get Taxonomy Terms By Other Taxonomy Term

getTaxTermsByOtherTaxTerm.php

<?php
function getTaxTermsByOtherTaxTerm($taxonomy_args, $out_taxonomy, $post_type = NULL) {
    global $wpdb;
    $tax_q = $taxonomy_ids = array();
    
    $post_type_q = !empty($post_type_q) ? "AND p.post_type = '$post_type'" : "";
    foreach ($taxonomy_args as $tax => $term_id) {
        $sql = "SELECT tt.term_taxonomy_id FROM $wpdb->term_taxonomy AS tt
                    INNER JOIN $wpdb->term_taxonomy t ON (t.term_id = tt.term_id AND tt.taxonomy = '$tax' AND t.term_id = $term_id)";
        $taxonomy_ids = array_merge( $taxonomy_ids, $wpdb->get_col($sql) );
    }
    if( !count($taxonomy_ids) )
        return false;

    foreach ($taxonomy_ids as $term_taxonomy_id) {
        $tax_q[] = "(SELECT COUNT(1) FROM $wpdb->term_relationships WHERE term_taxonomy_id IN ($term_taxonomy_id) AND object_id = p.ID ) = 1";
    }

    $sql = "SELECT p.ID FROM $wpdb->posts p WHERE (" . implode(" AND ", $tax_q) . ") $post_type_q AND (p.post_status = 'publish') GROUP BY p.ID";
    $res = $wpdb->get_col($sql);

    if ($res && count($res)) {
        $o_ids = implode(',', $res);
        $sql = "SELECT t.*, tt.* 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 = '$out_taxonomy' AND r.object_id IN ($o_ids) GROUP BY t.term_id";

        $res = $wpdb->get_results($sql);
        return $res;
    }
}

// example of call
$taxonomy_args = array(
    'pa_color' => 28,
    'pa_food' => 71,
    'pa_taste' => 67
);
$res = getTaxTermsByOtherTaxTerm($taxonomy_args, 'pa_country', 'product');