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

by

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
);

Leave a Comment