WordPress (monster) function that retrieve terms from one or more taxonomies having posts "in common" with given term(s) form a different taxonomy.

get_term_related_terms.php

<?php
/**
 * Retrieve terms from one or more taxonomies having posts "in common" with given term(s)
 * from a different taxonomy.
 *
 * @param mixed         $terms      One or more terms to use as base.
 *                                  Can be a term id, a term slug, a term object or an array of them.
 *                                  Term (or all terms if array given) must belong to same
 *                                  taxonomy speficied in $term_tax param
 * @param string        $term_tax   Taxonomy which given term(s) belong to.
 * @param string|array  $taxonomies One or more taxonomies to retrieve terms from. Required.
 * @param string|array  $args       Additional arguments. Accepted values and default values are:
 *                                  (int)          'child_of'    Retrieve only terms being children of a given term.
 *                                                               Default NULL.
 *                                  (array)        'exclude'     Exclude form results given term ids.
 *                                                               Default NULL.
 *                                  (array)        'include'     Include in results only given term ids.
 *                                                               Ignored if 'exclude' is not empty. Default NULL.
 *                                  (int)          'limit'       Limit results to a specific number of results.
 *                                                               Default NULL (no limit).
 *                                  (boolean)      'only_top'    If true returns only top-level terms.
 *                                                               Ignored if 'child_of' is not empty. Default false.
 *                                  (string)       'orderby'     Order results based on this column. Accepted values:
 *                                                               'term_id', 'term_taxonomy_id', 'slug', 'name' & 'count'.
 *                                                               Default NULL.
 *                                  (string)       'order'       Ordering direction. Accpeted values:
 *                                                               'ASC' & 'DESC'. Ignored if 'orderby' is empty/not valid.
 *                                                               Default 'ASC'.
 *                                  (string|array) 'post_status' Limit results to terms connected by post in one or more
 *                                                               post statuses. Default 'publish'.
 *                                  (string|array) 'post_type'   Limit results to terms connected by post in one or more
 *                                                               post types. Default '' (all post types).
 */
function get_term_related_terms( $terms = '', $term_tax = '', $taxonomies = '', $args = '' ) {
    if ( ! taxonomy_exists( $term_tax ) ) {
        return new WP_Error( 'wrong-term_tax-arg' );
    }
    $_taxonomies = array ();
    foreach ( (array) $taxonomies as $tax ) {
        if ( is_string( $tax ) && taxonomy_exists( $tax ) ) {
            $_taxonomies[] = esc_sql( $tax );
        }
    }
    if ( empty( $_taxonomies ) ) {
        return new WP_Error( 'wrong-taxonomies-arg' );
    }
    $taxonomy = implode( "','", array_unique( $_taxonomies ) );
    $default = array (
        'child_of'    => NULL,
        'exclude'     => NULL,
        'include'     => NULL,
        'limit'       => NULL,
        'only_top'    => FALSE,
        'orderby'     => '',
        'order'       => 'ASC',
        'post_type'   => '',
        'post_status' => 'publish',
    );
    $args = wp_parse_args( $args, $default );
    /* Early return filter hook to ease cache */
    $er = apply_filters( 'pre_get_term_related_terms', NULL, $terms, $term_tax, $taxonomies, $args );
    if ( NULL !== $er ) {
        return $er;
    }
    $term_tax_ids = array ();
    foreach ( (array) $terms as $term ) {
        $term_tax_obj = false;
        if ( is_numeric( $term ) ) {
            $term_tax_obj = get_term( $term, $term_tax );
        } elseif ( is_string( $term ) ) {
            $term_tax_obj = get_term_by( 'slug', $term, $term_tax );
        } elseif ( is_object( $term ) ) {
            $term_tax_obj = $term;
        }
        if ( is_object( $term_tax_obj ) && isset( $term_tax_obj->term_taxonomy_id ) ) {
            $term_tax_ids[] = absint( $term_tax_obj->term_taxonomy_id );
        }
    }
    $ttids = implode( ',', array_filter( array_unique( $term_tax_ids ) ) );
    if ( empty( $ttids ) ) {
        return new WP_Error( 'wrong-terms-arg' );
    }
    global $wpdb;
    $query = "SELECT t.term_id, t.name, t.slug, tt.* FROM {$wpdb->terms} t";
    $join = " INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_id = t.term_id ";
    $join .= "INNER JOIN {$wpdb->term_relationships} tr ON tr.term_taxonomy_id = tt.term_taxonomy_id ";
    $join .= "INNER JOIN {$wpdb->term_relationships} tr2 ON tr2.object_id = tr.object_id ";
    $join .= "INNER JOIN {$wpdb->term_taxonomy} tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id";
    $where = '';
    $type = $status = $include = $exclude = false;
    if ( ! empty( $args['post_type'] ) ) {
        if ( is_string( $args['post_type'] ) && post_type_exists( $args['post_type'] ) ) {
            $type = array ( $args['post_type'] );
        } elseif ( is_array( $args['post_type'] ) ) {
            $type = array_filter( $args['post_type'], 'post_type_exists' );
        }
    }
    if ( $args['post_status'] !== 'any' ) {
        if ( is_string( $args['post_status'] ) && ! empty( $args['post_status'] ) ) {
            $status = array ( $args['post_status'] );
        } elseif ( is_array( $args['post_status'] ) ) {
            $status = array_filter( $args['post_status'], 'is_string' );
        }
        if ( empty( $status ) ) {
            $status = array ( 'publish' );
        }
    }
    if ( ! empty( $type ) || ! empty( $status ) ) {
        $join .= " INNER JOIN {$wpdb->posts} p ON p.ID = tr.object_id";
    }
    if ( ! empty( $status ) ) {
        $status = implode( "','", array_unique( array_map( 'esc_sql', $status ) ) );
        $where .= " AND p.post_status IN ('{$status}')";
    }
    if ( ! empty( $type ) ) {
        $type = implode( "','", array_unique( array_map( 'esc_sql', $type ) ) );
        $where .= " AND p.post_type IN ('{$type}')";
    }
    if ( ! empty( $args['include'] ) ) {
        $include = array_filter( array_map( 'intval', $args['include'] ) );
    } elseif ( ! empty( $args['exclude'] ) ) {
        $exclude = array_filter( array_map( 'intval', $args['exclude'] ) );
    }
    if ( ! empty( $include ) || ! empty( $exclude ) ) {
        $operand = ! empty( $include ) ? 'IN' : 'NOT IN';
        $ids = ! empty( $include ) ? implode( ', ', $include ) : implode( ', ', $exclude );
        $where .= " AND t.term_id {$operand} ({$ids})";
    }
    if ( is_numeric( $args['child_of'] ) && (int) $args['child_of'] > 0 ) {
        $where .= $wpdb->prepare( " AND tt.parent = %d", $args['child_of'] );
    } elseif ( $args['only_top'] ) {
        $where .= " AND tt.parent = 0";
    }
    $where .= " WHERE tt.taxonomy IN('{$taxonomy}') AND tr2.term_taxonomy_id IN({$ttids}) ";
    $where .= $wpdb->prepare( " AND tt2.taxonomy = %s", $term_tax );
    $query .= $join . $where . " GROUP BY tt.term_taxonomy_id";
    $orderby_ok = array ( 'term_id', 'term_taxonomy_id', 'slug', 'name', 'count' );
    if ( is_string( $args['orderby'] ) && ! empty( $args['orderby'] ) ) {
        $by = strtolower( $args['orderby'] );
        if ( in_array( $by, $orderby_ok ) ) {
            $orderby = in_array( $by, array ( 'term_taxonomy_id', 'count' ) ) ? "tt.{$by}" : "t.{$by}";
            $order = is_string( $args['order'] ) && strtoupper( $args['order'] ) === 'DESC' ? 'DESC' : 'ASC';
            $query .= " ORDER BY {$orderby} {$order}";
        }
    }
    if ( is_numeric( $args['limit'] ) && (int) $args['limit'] > 0 ) {
        $query .= $wpdb->prepare( " LIMIT %d", $args['limit'] );
    }
    return $wpdb->get_results( $query );
}