Selecting all posts from the children of a particular parent taxonomy

Question

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

Answer 1

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.

Answer 2

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);

var_dump($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.