I've created a custom post type (minerals) with a custom taxonomy (location) which supports hierarchies. What I am trying to do is create a return according to the parent location of the mineral post type.
For example, someone clicks on Europe, and the results show all minerals from all listed countries in Europe.
Now the custom location taxonomy is organized to have all countries nested under their respective continent, but only the country (NOT the continent) is selected for the post. So my challenge is to sort through all the existing children of the requested parent and return the posts that met that criteria. (I've got a couple of other custom meta parameters to check as well, but those are easily filtered once I get this figured out.)
To perform this selection, I'm using the $wpdb class at the beginning of template page. I've already done something similar for other taxonomies that were based on price and size for the same "mineral" custom post type, but this is a little more complex.
Here's what I seem to need to do first:
SELECT wp_term_taxonomy.term_taxonomy_id WHERE wp_term_taxonomy.parent = $continent_id AND wp_term_taxnomy.count > 0;
I will assign these to a numeric array,then I would loop through the same query again to check if any of the selected
term_taxonomy_id are assigned as
wp_term_taxonomy.parent for other terms.
In the end what I'll have is an array of term_taxonomy_id's by which to query the wp_term_relationships table. My question is this, how do I use this generated array in a MYSQL query.
This is what I have so far:
SELECT wp_posts . * FROM wp_posts, wp_term_relationships WHERE wp_posts.ID = wp_term_relationships.object_id AND wp_term_relationships.term_taxonomy_id = (SOMETHING FROM THE TERM TAX ARRAY OF IDS) AND wp_posts.post_type = 'mineral'
This second line from the bottom is where I'm stuck. Maybe I'm doing this all wrong and someone can suggest a different approach.