[wp-hackers] finding out the top 5 source ( source is a custom taxonomy ), in a given category

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 =
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 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
> process.
>
> 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.
>>>
>>> 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 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.
>>>
>>>
>>> http://wordpress.stackexchange.com/questions/84371/finding-out-the-top-5-source-source-is-a-custom-taxonomy-in-a-given-categor
>>> _______________________________________________
>>> wp-hackers mailing list
>>> wp-hackers at lists.automattic.com
>>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>>
>>
>>
>>
>> --
>> -- I've kinda got a thing for WordPress > http://www.drewapicture.com
>> _______________________________________________
>> 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