Ask your WordPress questions! Pay money and get answers fast! (more info)

Query posts that have only one custom taxonomy term

I have a music blog in which I have a custom taxonomy "artist_tag" with which I tag my articles by what artist is mentioned in each article.

I now need to get the most recent image of specific artists and when I pull the thumbnail from the most recent artist_tag post its thumbnail is not necessarily the correct artist. For example a story about the Beatles and the Doors may have a picture of just John Lennon but be tagged the-beatles and the-doors, so if I pull the thumbnail from the most recent story with artist_tag "the-doors" it would return John Lennon's picture.

What I need to do is run a query - using get_posts or WP_query to build thr query - Preferably not using custom SQL - that is essentially:

Give me the most recent post that has the artist_tag term "the-doors" and no other artist_tag terms associated.

Answers (3)

2012-08-31

Michael Caputo answers:

So just so i understand: you don't want to return posts that belong to two categories. If they belong to two, they shouldn't be a result.


mackrider comments:

Theoretically yes, but not the category taxonomy - the artist_tag taxonomy.

Give me all posts that have the artist_tag of "the-doors" AND HAVE NO OTHER artist_tag taxonomies besides "the-doors"

Thanks


Michael Caputo comments:

I'm trying to find an easy solution, but coming up short.

Could you maybe come at it from the other angle and exclude all non associated terms?


mackrider comments:

What do you mean?


Michael Caputo comments:

Instead of writing a query that says

Only include term A

write one that says

Exclude B, C, D, E, F, G, H.....


mackrider comments:

Yeah but that could get expensive, no? It would have to be dynamic -- basically would have to get all artist_tags and then exclude them on the fly.

I could imagine that would get fairly pricey... give it a shot and I can test it.


Michael Caputo comments:

I'd suggest using a caching plugin on your site if you were going to go that route... I don't have time to write this up right now unfortunately, I can take a look later on though

2012-08-31

Hai Bui answers:

I can be wrong but I don't think it can be done without using custom SQL. Why are you against using custom SQL?


mackrider comments:

I'd prefer that way - but if that is the way to do it then that's fine.


Hai Bui comments:

Try the code below. I suggest using term id instead of the term slug because term slug would require another table (= another join) and it'd be more expensive, since you do this with code already, you should get the term id and replace it in the custom sql query.

$query = "
SELECT $wpdb->posts.* FROM $wpdb->posts
LEFT JOIN $wpdb->term_relationships
ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy
ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->posts.post_type = 'post'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->term_taxonomy.taxonomy = 'artist_tag'
AND $wpdb->term_taxonomy.term_id = 1
AND NOT EXISTS (SELECT * FROM $wpdb->posts AS post2
LEFT JOIN $wpdb->term_relationships AS termrelation2
ON (post2.ID = termrelation2.object_id)
LEFT JOIN $wpdb->term_taxonomy AS termtax2
ON (termrelation2.term_taxonomy_id = termtax2.term_taxonomy_id)
WHERE post2.ID = $wpdb->posts.ID
AND termtax2.taxonomy = 'artist_tag'
AND termtax2.term_id <> 1)
ORDER BY $wpdb->posts.post_date DESC
LIMIT 5
";
$results = $wpdb->get_results($query);
if ( $results ) {
foreach ( $results as $post ) {
setup_postdata( $post );
// do something here
}
wp_reset_postdata();
}


mackrider comments:

Thanks Hai Bui!

2012-08-31

Jerry DeFoe answers:

Would it work for you to have two sets of taxonomies such as artist_tag and mentioned_artist_tag where artist_tag can only contain 1 value and mentioned_artist_tag may have multiple selections (or none)?