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.

Thanks, Brent


2 Answers 2


I think you're way overcomplicating this. Use get_terms to get all taxonomy terms that are a child of a particular term via the child_of argument, then use that array with a WP_Query tax query, no custom SQL queries necessary.

  • Well that's the part that I left off this post for simplicity's sake. I actually have post meta that I am needing to query at the same time as I'm querying the post, otherwise I would be using the built in functionality. I am checking to see if the available state of the mineral is not set to "sold" in a custom meta field that is attached to every mineral post type. I didn't see any other way to do that.
    – Brent
    Commented Aug 14, 2012 at 4:27
  • @Brent - does the key not exist if it is not set to sold? if the key exists for all posts, you can combine a meta query and tax query
    – Milo
    Commented Aug 14, 2012 at 5:15
  • Wow! That is what I was doing with the $wpdb class. Yes, the key does exist for all posts as it has four different states and is required to be set.
    – Brent
    Commented Aug 14, 2012 at 20:49

Ok, well I think I've figured out my own question. The link posted above in the comment to my question did prove to be the answer ultimately. In short, can I pass an array of values to a database query string? No. But I can create a join string to create a query like this:

$locIDS = join(',',$loc_array);

The code looks like this:

 wp_term_relationships.term_taxonomy_id IN ($locIDS)

And it renders like this:

 wp_term_relationships.term_taxonomy_id IN (24,25,26,27,28,29,30,39,44,64,65,66,72) 

Here's what my end code looks like that allows me to process this request:

$loc_array = array();

function get_local_ids($continent_id, $loc_arr){
    global $wpdb;

    $contQ = "SELECT wp_term_taxonomy.term_taxonomy_id FROM wp_term_taxonomy WHERE wp_term_taxonomy.parent = $continent_id AND wp_term_taxonomy.count > 0";

    $locID = $wpdb->get_results($contQ, ARRAY_N);

    foreach($locID as $locVal){

        echo "The value of locVal is: ". $locVal[0] ."<br>"; 
        $loc_arr[] = $locVal[0];
        get_local_ids($locVal[0], $loc_arr);

    return $loc_arr;

$loc_array = get_local_ids($contID, $loc_array);


$locIDS = join(',',$loc_array);

$mQuery = "
    SELECT $wpdb->posts.*
    FROM $wpdb->posts, $wpdb->term_relationships
    WHERE $wpdb->posts.ID = $wpdb->term_relationships.object_id 
    AND $wpdb->term_relationships.term_taxonomy_id IN ($locIDS)
    AND $wpdb->posts.post_status = 'publish' 
    AND $wpdb->posts.post_type = 'mineral'
    AND $wpdb->posts.post_date < NOW()
    ORDER BY $wpdb->posts.ID DESC

I've actually got even more than this going in the actual code. But I think this explains how I was able to accomplish what I was looking to do in my question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.