Get Terms Filtered By Taxonomy and Post Type

Get_Terms_Filtered_By_Taxonomy_and_Post_Type.php

<?php
//variant 1, use IDS values for filter
function getFilterTerms($taxonomy, $post_type, $all_ids = NULL) {
    global $wpdb;
    $ids_q = count($all_ids) ? "AND p.ID IN (" . implode(',', $all_ids) . ")" : "";

    $sql = "SELECT t.* 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 = '$post_type' AND tt.taxonomy = '$taxonomy' $ids_q
            GROUP BY t.term_id ORDER BY t.name";
    $query = $wpdb->get_results($sql);
    return $query;
}

//variant 2, use order value for sortby order woocommerce parameter
function getFilterTerms($taxonomy, $post_type, $order_attr = NULL) {
    global $wpdb;
    $order = isset($order_attr) ? "tm.meta_value ASC" : "t.name ASC";
    $metaq = isset($order_attr) ? "INNER JOIN $wpdb->termmeta AS tm ON t.term_id = tm.term_id AND tm.meta_key = 'order_$taxonomy' " : "";

    $sql = "SELECT t.* 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 $metaq
            WHERE p.post_type = '$post_type' AND tt.taxonomy='$taxonomy'
            GROUP BY t.term_id ORDER BY $order";

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

//variant 3, use order value for sortby order woocommerce parameter AND filter by category ID
function getFilterTerms($taxonomy, $post_type, $order_attr = NULL, $category_id = NULL) {
    global $wpdb;
    $order = isset($order_attr) ? "tm.meta_value ASC" : "t.name ASC";
    $metaq = isset($order_attr) ? "INNER JOIN $wpdb->termmeta AS tm ON t.term_id = tm.term_id AND tm.meta_key = 'order_$taxonomy' " : "";
    $pcatq = isset($category_id) ? "AND p.ID IN (SELECT object_id FROM $wpdb->term_relationships
            WHERE term_taxonomy_id = (SELECT term_taxonomy_id FROM $wpdb->term_taxonomy WHERE term_id = $category_id))" : "";

    $sql = "SELECT t.* 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 $metaq
            WHERE p.post_type = '$post_type' AND tt.taxonomy='$taxonomy' $pcatq
            GROUP BY t.term_id ORDER BY $order";

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