Some plugins don’t make the most of the new hierarchical categories in WordPress (well… new since 2.1 anyways). Most plugins that fashion their own SQL queries take only a single level of a category hierarchy into account.
For example: my blog Spontaneous Derivation, has this partial category hierarchy underneath Fantasy and SF category:
Fantasy and SF [id 1] +-- Awards [id 2] +-- News [id 3] +-- Reviews [id 4]
Most plugins, when asked to work on category 1, will neglect to include the posts under ids 2, 3, and 4; yet all posts in the child categories implicitly belong to the parent category 1.
Here’s how to add hierarchical category support to these plugins, under the cut.
We’ll use the wPopularity plugin, which records the popularity of blog posts and allows you to display the most popular posts in a particular category. Currently it ignores the child categories.
The function that retrieves the posts most popular in a specific category is
function show_top_ranked_in_cat($limit, $before, $after,
$cat_ID = '') {
Halfway through the function, the following query is constructed:
$posts = $wpdb->get_results("
SELECT ID, post_title
FROM $wpdb->posts p
LEFT JOIN $wpdb->term_relationships tr
ON p.ID = tr.object_id
LEFT JOIN $wpdb->term_taxonomy tt
ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN $wpdb->ak_popularity pop
ON p.ID = pop.post_id
$join
WHERE tt.term_id = '".intval($cat_ID])."'
AND tt.taxonomy = 'category'
AND post_status = 'publish'
AND post_type = 'post'
AND post_date < NOW()
$where
$groupby
ORDER BY pop.total DESC
LIMIT ".intval($limit)
);
The bolded code is our target. We want to somehow change it to an IN condition, like so:
WHERE tt.term_id IN (".join(',', $categories).")
We need to recursively retrieve all the child category IDs for $cat_ID. We achieve this through the following code:
$all_cats = get_term_children(intval($cat_ID), 'category');
$all_cats[] = $cat_ID;
get_term_children($term, $taxonomy) is a pre-definied function in WordPress, where $term is the ID of the category/tag/link category and $taxonomy is the type of $term (e.g., category, tag, etc).
Now we need to escape the category ids before the join:
$in_cats = array();
foreach ($all_cats as $acat) {
$in_cats[] = "'".intval($acat)."'";
}
Now we can use $in_cats in our SQL query join:
$posts = $wpdb->get_results("
SELECT ID, post_title
FROM $wpdb->posts p
LEFT JOIN $wpdb->term_relationships tr
ON p.ID = tr.object_id
LEFT JOIN $wpdb->term_taxonomy tt
ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN $wpdb->ak_popularity pop
ON p.ID = pop.post_id
$join
WHERE tt.term_id IN (".join(',', $in_cats).")
AND tt.taxonomy = 'category'
AND post_status = 'publish'
AND post_type = 'post'
AND post_date < NOW()
$where
$groupby
ORDER BY pop.total DESC
LIMIT ".intval($limit)
);
And now using the following function:
function akpc_most_popular_in_cat($limit = 10, $before = '',
$after = ' ', $cat_ID = '')
{
global $akpc;
$akpc->show_top_ranked_in_cat($limit, $before,
$after, $cat_ID);
}
in a WordPress theme will account for all the categories under the given $cat_ID.
Simple and useful for those of us with hierarchical categories.
i replace:
WHERE tt.term_id IN (“.join(‘,’, $incats).”)
for
WHERE tt.term_id IN (“.join(‘,’, $in_cats).”)
and.. work it
thanks!
Thanks for pointing out the typo—it’s fixed now
And I’m glad this tip worked for you.
Hello,
I am looking for a way to have hierarchical tags in WordPress (not .com). Like this:
# CSS:font
# IRI is /?tag=CSS and /?tag=CSS:font
Full discussion: Hierarchical tags
Any ideas? Thanks
(by the way, Ajax Edit Comments is an excellent idea!)
Tags aren’t natively hierarchical in WordPress, whereas categories (these days) are. It would take a lot of hackery and knowledge of how WordPress handles its taxonomies currently (plus WordPress has been known to change taxonomy implementation on a whim, so that in itself is a bit dangerous).
Still, it’s an interesting idea for a feature.