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 want to write a select query to return data on only posts which have are associated with the taxonomy term food-and-beverage

SELECT wp_postmeta . * , wp_posts.post_name
FROM wp_posts, wp_postmeta, wp_terms, wp_term_relationships
WHERE wp_posts.post_type =  'projects'
AND wp_posts.post_status =  'publish'
AND wp_posts.ID = wp_postmeta.post_id
AND wp_term_relationships.object_id = wp_posts.ID
AND wp_term_relationships.term_taxonomy_id = wp_terms.term_id
AND wp_terms.slug =  'food-and-beverage'
ORDER BY wp_posts.ID DESC 

For some reason unknown to me, this is returning posts which do not have food-and-beverage selected

As evidenced by the answer below, this is not the ideal approach, yet it is one that I've committed to, as refactoring the code (this is generated SQL, and theres lots more of it) would take me forever.

EDIT


Since everyone seems to be in agreement (myself included) that the way I'm doing this is wrong, I'm going to reluctantly try to refactor.

However, I'm going to leave the question open for the time being in case someone can put together a quick fix for me (I'll happily drop you some bounty points).

For the bold/crazy, here is a gist with a big chunk of the source: https://gist.github.com/2950395

I pass the ajax it a couple "filters" as POST variables.

share|improve this question
    
Since this is more-or-less straight up SQL, should I maybe ask it over at StackOverflow instead? –  Zach L Jun 18 '12 at 19:30
add comment

2 Answers 2

up vote 2 down vote accepted

(I realize you're leaning away from this, but maybe if you can get it working, it's worthwhile. With the recent perfomance improvements in 3.4 for WP_Query, this could be worthwhile.)

WP_Query is the right decision if this is a secondary loop. Otherwise, you might look into pre_get_posts.

When you use WP_Query make sure that:

  1. You don't use a reserved global variable to save your new query. (For instance, a lot of people cause problems by saving their new query as $wp_query and then they overwrite their existing query object.
  2. You use the right loop format e.g. `$my_custom_query->have_posts();
  3. Finally, rather than using tag or the tax var (which is deprecated), use a tax_query.

Looking at your example, here's a suggested loop:

$food_query_args = array(
    'post_type' => 'projects',
    'tax_query' => array(
        array(
            'taxonomy' => 'post_tag', // or the name of your custom taxonomy
            'field' => 'slug',
            'terms' => 'food-and-beverage' // FYI, it's more stable imho to use the ID if you can. If you do, that switch 'slug' in the preceding line to 'id'
        )
    )
);

$food_query = new WP_Query( $food_query_args );

if( $food_query->have_posts() ) : while( $food_query->have_posts() ) : $food_query->the_post();

    // STUFF

endwhile; endif; wp_reset_postdata();
share|improve this answer
    
Thanks, you've effectively convinced me to start rewriting this thing, as much as I hate to do it. I added a link to a Gist, with an expanded version of my source if you want to take a look. I'd appreciate any advice/direction... –  Zach L Jun 18 '12 at 20:07
    
@ZachL Regarding your edit, take a look at the Codex. The tax_query expressly requires an array of arrays so that you can theoretically query by multiple terms (e.g. in term1 && term2 && !term3) –  mrwweb Jun 19 '12 at 3:08
    
apologies... I did read the Codex there before the edit, and thought I had it right. I must've been on tilt yesterday. Thank you for the help. I'll give it another read. –  Zach L Jun 19 '12 at 13:58
add comment

You don't need to write these SQL queries. you can utilize WP_Query api.

the following will display posts that has terms 'food-and-beverage' in tags.

$query = new WP_Query( array( 'tag' => 'food-and-beverage' ) );

if you use different taxonomy, use that instead of tag.

as you need to filter posts by using other options, you can check whole api documentation for WP_Query here: http://codex.wordpress.org/Class_Reference/WP_Query

That will save you from writing SQL.

share|improve this answer
    
sigh... I knew someone would say that. I spent several hours trying your approach (the right way no doubt), and just couldn't get it to work. I now have an embarassingly long speghetti code of a php file where the above portion is the only thing stopping me from finishing, forgetting it forever and pretending that I'm not a hack. –  Zach L Jun 18 '12 at 19:17
    
To elaborate a little more, when I used WP_Query, (or get_posts), it messed up an existing query on the page. When I tried to reset the query, or store $post as $oldPost and then revert afterwards, it would freeze up inexplicably... –  Zach L Jun 18 '12 at 19:27
add comment

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.