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.
(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 intopre_get_posts
.When you use
WP_Query
make sure that:$wp_query
and then they overwrite their existing query object.$my_custom_query->have_posts();
tag
or thetax
var (which is deprecated), use a tax_query.Looking at your example, here’s a suggested loop:
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.
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.