finding out the top 5 source ( source is a custom taxonomy ), in a given category

Question

The client I'm working with have thousands of articles and media ( audio/video ) files. To WordPress, all are posted by Author ID 1, which we named as STAFF.

All the content though in reality has been created by various people and sources, certainly not by the STAFF.

To keep that info ( who created the content or where the source is ), I used a non-hierarchical custom taxonomy called "source". if the source happens to be an institution name I tagged the content by the institution name as follows.

Example:

source: CNN source: Washington Post

And if the source happens to be a person, I used a prefix "by " in front of the name.

Example:

source: by anderson cooper source: by jon stewart

Of course, the content has been tagged and categorized, accordingly.

Now, the question is how to effectively find out things like who's the top 5 source's in category XYZ?

I don't think this can be pulled by using the wp_query args - by tapping into the advanced tax query's section, cause wp_query args eventually returns posts only. Here we are asking terms in return.

One possible way for this is the following

take the XYX category

go thru all the XYX categorized posts - one by one - particularly paying attention to the source data in those posts and while going thru them, dump each source term id encountered in those posts into a temporary table. ( for this you will use wp_terms, wp_term_relations and wp_term_taxonomy and that your creation mySQL table)

when done with going thru all the posts in that category, you finally switch your attention to the temporary table

and get a summary on that table, by getting ( source ) term counts after a group by and finally get the top 5.

but getting all that in a single statement? that ain't my cup of tea yet.

Answer 1

Thanks to some leads from the wp-hackers list on this very question and some googling around, here is the answer to my problem

$sql = "SELECT count(*) as count,terms2.name as tag FROM wp_posts as p1
     LEFT JOIN wp_term_relationships as r1 ON p1.ID = r1.object_ID
     LEFT JOIN wp_term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
     LEFT JOIN wp_terms as terms1 ON t1.term_id = terms1.term_id,

     wp_posts as p2

     LEFT JOIN wp_term_relationships as r2 ON p2.ID = r2.object_ID
     LEFT JOIN wp_term_taxonomy as t2 ON r2.term_taxonomy_id =t2.term_taxonomy_id
     LEFT JOIN wp_terms as terms2 ON t2.term_id = terms2.term_id

     WHERE

     t1.taxonomy = 'category' AND p1.post_status = 'publish' AND terms1.name = 'YOUR CATEGORY NAME HERE' 
     AND
     t2.taxonomy = 'THAT OTHER CUSTOM TAXONOMY HERE - IN MY CASE THAT IS TO BE SOURCE' AND p2.post_status = 'publish'

     AND p1.ID = p2.ID

     GROUP BY tag

     ORDER BY count DESC limit 5
 ";


global $wpdb;
$terms = $wpdb->get_results($sql,ARRAY_A);
foreach ($terms as $term):
    extract($term);
    echo "$tag,";
endforeach;

The above snippet will get me the top 5 source terms in the desired category when I adjust the WHERE to be as

WHERE t1.taxonomy = 'category' AND p1.post_status = 'publish' AND terms1.name = 'DESIRED CATEGORY NAME HERE' AND t2.taxonomy = 'Source' AND p2.post_status = 'publish'

Not a SQL I recommend for using in high traffic sites but with some transient caching, why not?