Get terms from one taxonomy that relate to terms from another taxonomy through published posts.

get-terms-by-overlap.php

<?php

/**
 * Get terms from one taxonomy that relate to terms from another taxonomy through published posts.
 * For instance, get all tags that are set on posts in the category "WordPress".
 *
 * @param array $args An array of options. Must contain at least 'term_ids' OR 'term_names'. Options are:
 * 		string 'get_taxonomy'     => the taxonomy whose terms you want to retrieve. Default is post_tag
 * 		string 'through_taxonomy' => the taxonomy whose term(s) you're providing. Default is category
 * 		array  'term_ids'         => an array of term IDs through which to find overlapping terms
 *      array  'term_names'       => an array of term names through which to find overlapping terms
 * @return array An array of term_ids
 */
function get_terms_by_overlap( $args ) {
	global $wpdb;

	$args = wp_parse_args( $args, array(
		'get_taxonomy'     => 'post_tag',
		'through_taxonomy' => 'category',
		'term_ids'         => false,
		'term_names'       => false
	) );

	if ( $args['term_ids'] ) {
		$args['term_ids'] = array_map( 'intval', (array) $args['term_ids'] );
		$term_where = "t1.term_id IN (" . implode( ',', $args['term_ids'] ) . ")";
	} elseif ( $args['term_names'] ) {
		$args['term_names'] = (array) $args['term_names'];
		array_walk( $args['term_names'], array( $wpdb, 'escape_by_ref' ) );
		$term_where = 't1.name IN ("' . implode( '","', $args['term_names'] ) . '")';
	} else {
		return array();
	}

	return $wpdb->get_col(
		"SELECT DISTINCT t2.term_id
		FROM {$wpdb->posts} as p
			LEFT JOIN {$wpdb->term_relationships} as tr1 ON p.ID = tr1.object_ID
			LEFT JOIN {$wpdb->term_taxonomy} as tt1 ON tr1.term_taxonomy_id = tt1.term_taxonomy_id
			LEFT JOIN {$wpdb->terms} as t1 ON tt1.term_id = t1.term_id
			LEFT JOIN {$wpdb->term_relationships} as tr2 ON p.ID = tr2.object_ID
			LEFT JOIN {$wpdb->term_taxonomy} as tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
			LEFT JOIN {$wpdb->terms} as t2 ON tt2.term_id = t2.term_id
		WHERE tt1.taxonomy = 'category' AND p.post_status = 'publish' AND {$term_where} AND tt2.taxonomy = 'post_tag'
		ORDER by t2.term_id"
	);
}