Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

share|improve this question
    
This general PHP/MySQL question seems to address my issue: stackoverflow.com/questions/907806/… I will test this and report back accordingly. –  Brent Aug 13 '12 at 22:23

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
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 Aug 14 '12 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 Aug 14 '12 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 Aug 14 '12 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);

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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