How to Query Database for posts with certain Taxonomy Term

Question

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.

Answer 1

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

Answer 2

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.