Get list of terms of current taxonomy archive for another taxonomy

Question

I have a Custom Post Type named 'Real estate' which has (amongst others) two different taxonomies:

• 'Purpose' (hierarchical; terms used are 'Living', 'Working' and 'Holidays') and
• 'Location' (non-hierarchical; e.g. 'London', 'Berlin', 'Paris').

For a search form on the 'Living' taxonomy archive I would need a list of all 'Location' terms associated with posts in the 'Living' taxnonomy term archive in order to give it back in the search form through a select. How can I, best as a list of comma separated values for an array, get all the terms for 'Location' used in the 'Living' term archive?

Answer 1

Ok, so I think I've got something for you! As you know, I've had the same issue, and when I was looping through each taxonomy I found that I was generating between 25-35 queries, which is crazy! So I decided to have a go at some MySQL on the term relationships table to see if there was a faster was to query the data.

With some help from a great developer I know (Mac McDonald http://wordpress.mcdspot.com/ ), I've made a function which makes one request and returns and array of term IDs in the "other" taxonomy. You can have to prepare the term IDs to query against first, like this;

//the taxonomy we want to check terms for $opposite_taxonomy_terms = get_terms( $taxonomy );

//new array, with current term id, very important!
$sibling_term_ids = array( $this_term->term_id );

foreach( $opposite_taxonomy_terms as $term )
{
    //collect all ids, ready to build the MySQL query
    $sibling_term_ids[] = $term->term_id;

}

$imploded = implode(', ', $sibling_term_ids );

$results = rd_get_opposite_terms( $imploded, $this_term->term_id );

I'm getting the terms I wish to "check" for, and then adding them to an array which must contain the main term ID you're querying against. I then implode this to get a comma separated list for the MySQL query. That's found in the rd_get_opposite_terms() function;

function rd_get_opposite_terms( $imploded = array(), $current_term_id = FALSE )
{
    if( empty( $imploded ) || ! $current_term_id )
        return FALSE;

    global $wpdb;

    //perform a query to find terms that have posts in both the $current_term_id (from one taxonomy) and terms in $imploded (term IDs from another taxonomy). Returns IDs as an array.
    return $wpdb->get_col( 'SELECT tr1.term_taxonomy_id FROM wp_term_relationships tr1 WHERE tr1.term_taxonomy_id IN ( ' . $imploded . ' ) AND tr1.object_id IN (SELECT DISTINCT object_id FROM wp_term_relationships tr2 WHERE tr2.term_taxonomy_id = ' . $current_term_id . ' ) GROUP BY tr1.term_taxonomy_id' );
}

I admit, this is near the top end of my development ability at this point in time, but I think it's a much better solution than what I originally had, and I hope it helps you too!