[wp-hackers] Querying posts for all associated term_ids?

[Mike Schinkel]

> You are just trying to get a distinct list of taxonomies for a given post_type. For example for the WP Posts you would find the categories and post tags. Correct?

I didn't follow your clarification so I'll try to explain other with details:

The person who asked the question wanted was a page of client projects, each of which would be stored in posts of post_type="our_work" where each of those our_work posts had been assigned a term from the "client_name" taxonomy. He was able to do that, no problem.
 
He also wanted a drop down that listed clients for which they had "our_work" projects so a visitor could filter by projects for just one client, and clearly the dropdown shouldn't have listed any given client more than once. That's the question: how to get a list of terms with no duplicates that have been assigned to posts of a given post type and how to do it completely in the WordPress API.

I couldn't figure out how to use get_posts() to give me a list of taxonomy terms (since it's about getting posts) and I couldn't figure out how to use get_terms() to filter against terms assigned to posts of a given post_type hence the SQL code below that does exactly what I need.

It would have been possible to use hooks with get_posts() to get what I need but then I've dove into SQL anyway and I added code that could potentially have side-effects elsewhere so I recommended he use raw SQL. But I'd still prefer to have recommended a WordPress API solution and I'm asking her in hopes to find that I overlooked something.

-Mike

On Sep 4, 2010, at 5:09 PM, Paul wrote:

> Mike,
> 
> Just trying to understand the question here. Not a soluton. I've read through the StackExchange item. Still not sure I understand. 
> 
> You are just trying to get a distinct list of taxonomies for a given post_type. For example for the WP Posts you would find the categories and post tags. Correct?
> 
> P-
> 
> 
> On Sep 4, 2010, at 4:32 PM, Mike Schinkel wrote:
> 
>> Hi all,
>> 
>> I am trying to figure out if it is possible to use the WordPress database API without having to add hooks to get the distinct list of term IDs from a given taxonomy that are associated with posts of a given post type? 
>> 
>> I looked and could not find any thing available in WordPress core even though it seems that I must be missing something. Here's the SQL code that achieves what I'm after:
>> 
>> SELECT DISTINCT
>> tt.term_id
>> FROM wp_posts p
>> INNER JOIN wp_term_relationships tr ON p.ID = tr.object_id
>> INNER JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
>> WHERE 1=1
>> AND p.post_status='publish'
>> AND p.post_type='our_work'
>> AND tt.taxonomy='client_name'
>> 
>> FYI this came up in me trying to answer this question:
>> 
>> http://wordpress.stackexchange.com/questions/1140/
>> 
>> Thanks in advance.
>> 
>> -Mike
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
> 
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers