Get terms that contain posts that in turn belong to other terms?

2 Views

So I have custom taxonomy called “shape” and another one called “color”. If I want to get the list terms under shape that contain posts that are also present in terms under color, how do I do that?

Example, I have a post under custom post type toy called Ball which is present in both shape Circle and color Red. I want to retrieve the term name Circle via $wpdb query. I’d like to emphasize that I want to retrieve the list of terms and not the posts.

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. This should get you the names of all such terms in an array

    $wpdb->get_col("SELECT DISTINCT {$wpdb->terms}.name FROM {$wpdb->terms}
INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_id = {$wpdb->terms}.term_id
INNER JOIN {$wpdb->term_relationships} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
WHERE {$wpdb->term_taxonomy}.taxonomy = 'shape' AND {$wpdb->term_relationships}.object_id IN (
SELECT object_id FROM {$wpdb->term_relationships}
INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
WHERE {$wpdb->term_taxonomy}.taxonomy = 'color'
);");

    If you want more details other than name, you will have to change the fields to select & also change the function from get_col to get_results

    Please ensure that there are no spelling errors in the taxonomy names “shape” & “color” which are used inside the query

    References-

    http://codex.wordpress.org/Class_Reference/wpdb

    http://codex.wordpress.org/Database_Description