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.