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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0