I want to write a select query to return data on only posts which have are associated with the taxonomy term
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
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.
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.
(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
When you use
WP_Query make sure that:
$wp_queryand then they overwrite their existing query object.
taxvar (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();
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
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.