How does WordPress Store Taxonomy Terms in the Database?

While programming my Organize Drafts WordPress Plugin, I did a lot of research on how WordPress uses taxonomies and I spent some time digging around in the WordPress core.  Here’s what I found.

Creating your own taxonomy for use in a WordPress plugin or theme isn’t very difficult, and for a primer I would suggest checking out the WordPress documentation on taxonomies.

However, I came across some confusion on the web about whether WordPress stores the taxonomies in the database.  This confusion seems to be caused by the fact that you have to call register_taxonomy on every load of the WordPress core.  If you don’t, your taxonomy won’t be recognized.

Here’s an abbreviated example of how to call register_taxonomy in a WordPress plugin that uses a class.  As described in the codex, this function needs to be called at a specific time.  When writing WordPress plugins, one of the tricky things is to always make sure your code is being called at the correct time during the WordPress loading process.  The init action hook can be used to call code that needs to run after WordPress has finished loading but before any headers have been sent.

        final class LSW_Organize_Drafts {
 
        public function __construct() {
          $this->hooks();
         }
        private function hooks() {
	    add_action('init', array($this, 'setup_taxonomy'));
         }

        public function setup_taxonomy() {
	    if(!taxonomy_exists('lswdrafttype')) {
		register_taxonomy( 'lswdrafttype', $post_types, $args );
	     }
         }
        }

       new LSW_Organize_Drafts();
  

In actuality, my code is more complex, as I made the class a Singleton, and also applied filters to the arguments, but hopefully the above code will help new WordPress plugin developers see how they can go about calling register_taxonomy inside of the init hook when using OOP design principles.

So what does register_taxonomy actually DO?

Register_taxonomy actually adds your taxonomy to the global variable $wp_taxonomies.  This happens on line 461 of taxonomy.php, as shown here:

term-taxonomoy-tables-4
Excerpt from the WordPress core file taxonomy.php

$wp_taxonomies is a global array of registered taxonomies. Before adding your taxonomy to the array, it checks the arguments that you supplied, and merges them with default arguments.

The $args that you supply tell WordPress what to do with your custom taxonomy. You get to specify whether it’s public, hierarchical, whether it should be shown in the menu, etc.  Here’s a screenshot that shows an example of what the $args might contain:

term-taxonomoy-tables-3
Screenshot taken while debugging WordPress in PHPStorm — this shows what the $args contain after some processing — for what you can actually supply, view the codex 

So, it’s true that the settings for your taxonomy are NOT saved in the database.  They are generated from a PHP array that’s coded in a plugin file or a theme file.

OK, so what if I don’t register my taxonomy?

If you don’t register the taxonomy, then it won’t be visible on any WordPress pages and you won’t be able to use it. However, your taxonomy terms and relationships that you may have previously created are still in the database.

What are Taxonomy Terms?

The terms are the actual category names or tag names, for example.  Or in my case, the terms are the draft types that I’ve created.

If you’re having trouble understanding taxonomy terms and what the difference is between the taxonomy name and the term names, then here are some real-world examples:

Taxonomy: Birds

Terms: Ducks, Geese, Eagles, Chickens, Sparrows

Taxonomy: Property Zoning Types

Terms: Commercial, Residential

Taxonomy: Medication Types

Terms: Over-the-counter, Prescription, Controlled

Why should I use a custom Taxonomy instead of just categories?

Using categories for everything will get messy fast.   Let’s say that you have a real estate website with a page for a house.  You probably want to organize that house by location, sale price, number of bedrooms, etc.  You could just simply add it to multiple categories, so the house could be in the cheap category, the Seattle category, the 4 bedroom category, etc.

However, custom taxonomies make life easier!  You can query each taxonomy separately and also display objects with the same taxonomy easily.  (NOTE: In WordPress, a post is an object!  A page is an object!  A custom post type is an object!  Knowing this becomes important when you look at the database tables.) If you have the taxonomies of sale_price, number_of_bedrooms, and location, you can then easily get all houses that are in the same location or the same price range.

For example, if we have custom taxonomies of sale_price and location, we could use this code to get all cheap houses in Seattle:

$args = array(
	 'posts_per_page' => 8,
	 'orderby' => 'rand',
	 'post_type' => 'houses',
	 'sale_price' => 'cheap',
	 'location' => 'seattle'
);
$cheap_houses_in_seattle = get_posts( $args );

Where does WordPress store Taxonomy Terms?

The actual terms are in the terms table.  All WordPress tables have a prefix.  In my example, the prefix is linsoft_, but the default prefix is wp_ so the actual name of your table might be wp_terms.

The taxonomy names are stored in the term_taxonomy table.

If you are familiar with database design, you would probably think that there would be a ONE TO MANY relationship between the taxonomy name and the terms, but in fact these tables have a ONE TO ONE relationship.  The WordPress database is not normalized!  There is duplicate data…  See this screenshot:

term-taxonomoy-tables-2
Screenshot from MySQL Workbench of the WordPress Term Taxonomy Table

As you can see, the taxonomy name is repeated many times. The term_id actually references the term_id that’s in the terms table.

Here’s what’s the terms table looks like:

term-taxonomoy-tables-6
Screenshot from MySQL Workbench of the WordPress terms table

How does get_terms work?

If you want to get the terms associated with the taxonomy, you use the function get_terms.  To display all of them, including the empty ones, you would do this:

get_terms( 'lswdrafttype', 'hide_empty=0');

Where ‘lswdrafttype’ is the name of your taxonomy.

The get_terms function begins at line 1070 of taxonomy.php  It’s a long function and most of it deals with forming the MySQL query.

The query is formed on line 1437:

$query = "SELECT $distinct $fields FROM $wpdb->terms AS t $join WHERE $where $orderby $order $limits";

 

As you can see, it’s going to join tables together.

The final query will look different depending on the paramters you gave to get_terms. In my case, the final query looks like this:


SELECT t.*, tt.* FROM wp_terms
AS t INNER JOIN wp_term_taxonomy
AS tt ON
t.term_id = tt.term_id
WHERE tt.taxonomy IN ('lswdrafttype') ORDER BY t.name ASC

What does the WordPress function taxonomy_exists do?

Taxonomy_exists does NOT check the database!  All it does is check the global array $wp_taxonomies.  If you have not called register_taxonomy, then your taxonomy will not be in the $wp_taxonomies variable, and the result will be false, even if you have terms associated with that taxonomy stored in the database!

So, do not fear, your taxonomies might still be in the database, even if taxonomy_exists returns false!

OK, so how does WordPress know which terms are associated with which posts?

Good question!  There’s another table for that, called term_relationships (remember, with your prefix before the table name, so commonly it might be called wp_term_relationships).

term-taxonomoy-tables-7
Screenshot from MySQL Workbench of the term_relationships WordPress table

Does this table look confusing?  Boring?  Remember how I told you that posts are objects in WordPress?  Well, this is why I mentioned it.  The object_id may be a post_id or an id of another object.  The term_taxonomy_id is the term_id found in the terms table.

You can get all of the taxonomy terms with the WordPress function wp_get_post_terms.  This function ends up called wp_get_object_terms, which creates a query that uses an inner join between the terms table and the term_taxonomy table.  See line 2401 of taxonomy.php.

In its simplest use, wp_get_post_terms can be supplied a $post_id and it will return the tags associated with it.  For example, calling this:

wp_get_post_terms(636);

In my database set-up, this function ends up running this MySQL query:


SELECT t.*, tt.* FROM linsoft_terms AS t INNER JOIN linsoft_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN linsoft_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id  WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (636) ORDER BY t.name ASC

If you want to get your custom taxonomy terms instead of the tags, then you will want to pass wp_get_post_terms a second parameter, which is an array of taxonomy names.

For example:

wp_get_post_terms($post_id, 'my_custom_taxonomy');

I hope that this helps!   If you have any questions or clarifications, please do not hesitate to post a comment. Thank you!

2 comments

Leave a Reply to Alexis Cancel reply

Your email address will not be published. Required fields are marked *