Does WordPress Offer a Way to Find All of the Categories that Don’t Have Children?

Background

As discussed in Is There a Difference Between Taxonomies and Categories, taxonomies refer to a generalized system of classification and organization of objects or data. In the case of WordPress; Categories, Tags, Post Format(ting), and Link Categories are built in taxonomies used to help classify and organize posts, links, and metadata. Terms are instances of these taxonomies; take for example a WordPress blog about computer software. If one of the taxonomies is a category, then one of the terms would be programming languages, because programming languages is a topic in computer software, and could be considered a categorical taxonomy. Additonally, WordPress developers and Plugin developers may utilize the register_taxonomy() function to initialize the built-in taxonomies, as well as create new taxonomies in WordPress.

Working with WordPress Categories

Ok, so WordPress offers up a few interesting functions under the Taxonomy API ( no codex page, links to most recent source code ). If I understand them correctly, some of the promising functions I can use for my purposes are the following:

• get_terms(): The codex pretty much says the same thing the source code comments say; that it retrieves the terms in a given taxonomy or list of taxonomies. I can provide an array of arguments to change which terms are returned; for example, to ensure that I include terms that are empty ( that is, are not referred to by any posts in the case of categories or tags ), I can set the hide empty argument to 0 or false;
• get_term_heirarchy(): Returns all of the term IDs of a given term’s children as an array.
• term_is_ancestor_of(): Does just what it says; compare two terms, with term one being the term of reference, and term two the term to check for ancestry.

Umm, So What About Descendants

My goal here is to find out which categories don’t have any child categories ( or descendant terms, to be more general ). I also don’t want to ignore empty categories ( categories that have children but don’t have any posts that reference them ).

Does WordPress have an easy way to do such a search? If so, are there performance concerns I should be worried about?

Some Edits: To provide an example for future reference, here is some hard data I am working with…

WordPress Category Admin View

Table of WordPress Taxonomy

Table of WordPress Terms

Table of WordPress Term_Relationships

In my test case, I want to return the following back:

• Uncategorized
• Firmware and Embedded Systems
• Operating Systems
• Web and Internet Based Applications
• Hardware

All of these have no children, even though some have an empty count.

There is no way easy way to do that. You have to query directly to achieve that. I am assuming, you only want the parent categories which don’t have descendants or even if they have descendants, it’s not used in any post

global $wpdb;
$categories = $wpdb->query("SELECT $wpdb->terms.* 
                            FROM  $wpdb->terms
                            INNER JOIN $wpdb->term_taxonomy
                              ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id
                            WHERE $wpdb->terms.term_id NOT IN(
                              SELECT $wpdb->term_taxonomy.parent
                              FROM $wpdb->term_taxonomy
                            )
                              AND $wpdb->term_taxonomy.taxonomy = 'category'"
                          );

Original Poster’s Addon EDIT

How Does This Query Work?

Assume the table prefix is main_. First, we need to get all of the categories so we can start examining which ones have no children. This is done by selecting everything from the term_taxonomy table and using a WHERE clause to limit the results where the taxonomy field is equal to category:

SELECT *
FROM main_terms
WHERE main_term_taxonomy.taxonomy = 'category';

However, we also need the names of the categories which is stored in the terms table, so we join the two tables together using an INNER JOIN, which also ensures that only rows of data common to both columns are included ( as opposed to an outer join ). Both tables being joined together need a common column to “stitch” them together.

SELECT *
FROM main_term_taxonomy
INNER JOIN main_terms
ON main_term_taxonomy.term_id = main_terms.term_id
WHERE main_term_taxonomy.taxonomy = 'category';

Finally, we exclude categories where they appear as a parent for another category in the term_taxonomy table. To do this, we use the keywords NOT IN, and construct another SQL query inside to SELECT everything in the parent column within term_taxonomy. This nested SQL query then provides which term ID’s should be excluded from our results as they have entries in term_taxonomy.parents, indicating they are parents of another category.

SELECT main_term_taxonomy.* 
FROM  main_term_taxonomy
INNER JOIN main_terms
ON main_term_taxonomy.term_id = main_terms.term_id
WHERE main_term_taxonomy.taxonomy = 'category'
AND main_term_taxonomy.term_id NOT IN(
    SELECT main_term_taxonomy.parent
    FROM main_term_taxonomy
);

Interestingly, this does return the correct rows, but doesn’t include the category name. This is because we’re selecting all of the columns from the wrong table, main_term_taxonomy. So we must switch the tables’ places in our SQL to get the information we desire.

SELECT main_terms.* 
FROM  main_terms
INNER JOIN main_term_taxonomy
ON main_term_taxonomy.term_id = main_terms.term_id
WHERE main_term_taxonomy.taxonomy = 'category'
AND main_term_taxonomy.term_id NOT IN(
    SELECT main_term_taxonomy.parent
    FROM main_term_taxonomy
);