[wp-hackers] finding out the top 5 source ( source is a custom taxonomy ), in a given category
halukkaramete at gmail.com
Mon Feb 4 03:36:09 UTC 2013
Excellent idea by suggesting and simplifying my problem "that is
getting the popular TAGS in a category".
With that diagnosis, the rest is a little bit of googling and little
bit of customization.
Here it is.
$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 =
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 =
LEFT JOIN wp_terms as terms2 ON t2.term_id = terms2.term_id
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
$terms = $wpdb->get_results($sql,ARRAY_A);
foreach ($terms as $term):
The above snippet will get me the top 5 source terms 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?
On Sun, Feb 3, 2013 at 4:02 PM, Dion Hulse (dd32) <wordpress at dd32.id.au> wrote:
> IMHO this is probably a case where using a custom tax is not the
> optimal solution *if* you have no need to group posts by source for
> primary querying.
> If a post meta value was used instead, or even if the post_author
> field, it would have greatly simplified your data structure.
> See http://ottopress.com/2011/when-to-not-use-a-custom-taxonomy/
> With your current structure, you'll need to do a manual SQL if you
> want any kind of performance.. join term_relationships and
> term_taxonomy against eachother, probably join that against
> term_taxonomy again, and order by the count field.. The other option
> is to query for all posts in Category XYZ, loop over them, get the
> tags for each post, sum and filter.. but that's a rather expensive
> You might be able to find an article online about finding the most
> popular TAGS in a category, you can simply change the taxonomy names
> and it'd work as expected.. I certainly can't find one right now.
> On 4 February 2013 09:53, Drew <xoodrew at gmail.com> wrote:
>> You might look at how wp_generate_tag_cloud() in
>> wp-includes/category-template.php goes about finding the term counts and
>> assigning font sizes based on those counts.
>> On Sun, Feb 3, 2013 at 2:19 PM, Haluk Karamete <halukkaramete at gmail.com>wrote:
>>> 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.
>>> source: CNN source: Washington Post
>>> And if the source happens to be a person, I used a prefix "by " in
>>> front of the name.
>>> 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 posts - one by one particularly
>>> paying attention to the source data in those posts while going thru,
>>> dump each source term id in a temporary table when done with going
>>> thru all the posts in that category, switch your attention to the
>>> temporary table and get a summary on that table using a get count
>>> 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.
>>> wp-hackers mailing list
>>> wp-hackers at lists.automattic.com
>> -- I've kinda got a thing for WordPress > http://www.drewapicture.com
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
More information about the wp-hackers