With WordPress 2.3 about to be released, I had to update some code I used to correspond with the new taxonomy schema.

For another blog I run, I have a feature where I show only the categories that have recently been posted against. For example, this post is being published under the category ‘Sidenotes’, so sidenotes would be the first category shown on a list.

For the new schema, I had to write a new query. I thought I’d share my query in case others find it useful or want a real example of a query using the new taxonomy schema.

<?php
function getLastFiveCats() {
	global $wpdb;
	$query = "select t.term_id as term_ids, t.name, t.slug, max(p.ID) as id, tx.count from $wpdb->terms t, $wpdb->term_taxonomy tx, $wpdb->term_relationships tr, $wpdb->posts p where t.term_id = tx.term_id and tx.parent != 0 and tx.taxonomy = 'category' and tr.term_taxonomy_id = t.term_id and tr.object_id = id and p.post_status = 'publish' group by term_ids order by id desc limit 5";
	$results = $wpdb->get_results($query);
	foreach ($results as $result) {
		$catPermalink = get_bloginfo('url') . "/category/" . $result->slug . "/";
		?>
			<li><a href='<?php echo $catPermalink ?>'><?php echo $result->name ?></a></li>
		<?php
	}
}
?>

Please note that this query only searches for non-parent categories, but this can be changed to search for all categories pretty easily by removing: tx.parent != 0

Here’s another query that gets all the categories and orders them by when they were last updated:

<?php
function getAllCats() {
	global $wpdb;
	$query = "select t.term_id as term_ids, t.name, t.slug, max(p.ID) as id, tx.count from $wpdb->terms t, $wpdb->term_taxonomy tx, $wpdb->term_relationships tr, $wpdb->posts p where t.term_id = tx.term_id and tx.taxonomy = 'category' and tr.term_taxonomy_id = t.term_id and tr.object_id = id and p.post_status = 'publish' group by term_ids order by id desc";
	$results = $wpdb->get_results($query);
	foreach ($results as $result) {
		$catPermalink = get_bloginfo('url') . "/category/" . $result->slug . "/";
		?>
			<li><a href='<?php echo $catPermalink ?>'><?php echo $result->name ?></a></li>
		<?php
	}
}
?>