In this article I will try to explain how to perform a series of operations on the WordPress categories associated with articles using SQL queries that act directly on the database.
The sample queries have been written for a MySQL database: however, they should work - with a few minor changes - on any other WordPress-compatible DB Engine as well.
Preliminary tasks
Needless to say, since we're going to perform some permanent operations to our WordPress DB, it is definitely advisable to run a complete backup of all the tables that we are going to modify (or of the entire database), so that we'll be able to recover from SQL errors and/or unwanted changes.
If you need a tool to accomplish this, we strongly suggest you to use either the official MySQL Workbench or SQLyog, a simple and free solution that allows you to backup individual tables and / or entire databases through a practical user interface and with a few clicks.
both of these tools are available in both Windows and Linux versions and will also allows you to make the queries that we're going to illustrate in the following paragraphs using a handy and convenient GUI interface, thus eliminating the need to use the command-line.
However, if you like the command-line, you can perform the backup using the mysqldump console tool: if you need info about that tool you can check out the official guide.
Posts, Categories and Relationships
Before taking a look at the queries, it is advisable to spend a couple minutes to understand how WordPress organizes the various posts and categories within its database. As you probably already know, WordPress does use a relational database model (RDBMS): more specifically, posts and categories are linked together using a many-to-many relationship; this means that each post can be associated with multiple categories and, conversely, each category can be related with multiple posts.
The posts are stored in the wp_posts table: this means that, to retrieve a specific post, you need to execute the following SQL query:
|
1 |
SELECT * FROM wp_posts WHERE ID = @ID; |
It goes without saying that the @ID placeholder must be replaced with the post ID we want to retrieve.
Categories works in a slightly different (and more complex) way: they are stored in the wp_terms table, together with other "terms" of different types (taxonomies); they can be identified as categories because they do have the category taxonomy, but such information is stored in a different table (wp_term_taxonomy). This means that, if we want to retrieve all the categories, we'll need to execute the a SQL query with a JOIN statement in the following way:
|
1 2 3 |
SELECT * FROM wp_terms JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy = 'category'; |
Let's now address the many-to-many relationship between posts and categories: such info is stored in the wp_term_relationships table, which contains the object_id field (the post_id) and the term_taxonomy_id (the term_id of the category). This means that, in order to retrieve all the categories of a specific post, we can execute the following SQL query:
|
1 2 3 4 |
SELECT * FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id JOIN wp_terms t ON t.term_id = tt.term_id WHERE tr.object_id = @PostID AND tt.taxonomy = 'category' |
Replacing the @PostID placeholder with our post's post_id.
Now we know everything we need to perform some useful INSERT and/or DELETE queries to add and/or remove some categories to our posts.
Adding a new Category to all Posts
Let's assume that we have just created a "News" category, and we want to add it to all the articles: although we could definitely do that using GUI by logging to the WordPress administration panel, such technique would be very slow (and error prone) if our WordPress site had a lot of articles.
Here is how we can solve the problem with a single SQL query:
|
1 2 3 4 5 |
INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT ID, @CategoryID, 0 FROM wp_posts p WHERE p.post_type = 'post' AND p.ID NOT IN (SELECT object_id FROM wp_term_relationships tr3 WHERE tr3.term_taxonomy_id = @CategoryID); |
The above query works in a rather simple way: it retrieves all the posts from the wp_posts table (minus those who are already associated with the @CategoryID) and creates an entry in the wp_term_relationships table for each one of them. Needless to say, the @CategoryID must be replaced with the actual ID of the category we want to add.
Adding a new Category to all Posts with an existing Category
Let's now see how can we add an additional category to all post that are already associated with another, already existing category. Such task can be performed with the following SQL query:
|
1 2 3 4 5 |
INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT object_id, @CategoryID, 0 FROM wp_term_relationships tr2 WHERE tr2.term_taxonomy_id = @OldCategoryID AND object_id NOT IN (SELECT object_id FROM wp_term_relationships tr3 WHERE tr3.term_taxonomy_id = @CategoryID); |
As we can see, we'll have to replace the @CategoryID with the ID of the additional category we want to add, and the @OldCategoryID placeholder with the existing category ID.
The above query follow the same approach of the previous one, with the following key difference: the IDs of the posts that we need to add the new category to are not retrieved by querying the wp_post table; they are fetched from the wp_term_relationships instead. We are allowed to do that because we know they must be there, since we only want to retrieve those who already have a relationship (with the existing category); from these records we can retrieve the object_id, which is precisely what we need to create the new relationship with the additional category.
Removing categories
Let's now see the queries that will allow us to remove the relationships between a given category - once we know its category ID - and one or more posts using the same criteria shown above.
To remove the relationships between a category and all the posts:
|
1 |
DELETE FROM wp_term_relationships WHERE term_taxonomy_id = @CategoryID; |
To remove the relationships between a category and all the post having an active relationship with another category:
|
1 2 3 4 5 |
DELETE FROM wp_term_relationships WHERE term_taxonomy_id = @CategoryID AND object_id IN (SELECT object_id FROM (SELECT * FROM wp_term_relationships) AS tr2 WHERE tr2.term_taxonomy_id = @OtherCategoryID); |
Last but not least, to remove the relationships between a category and all the post NOT having an active relationship with another category:
|
1 2 3 4 5 |
DELETE FROM wp_term_relationships WHERE term_taxonomy_id = @CategoryID AND object_id NOT IN (SELECT object_id FROM (SELECT * FROM wp_term_relationships) AS tr2 WHERE tr2.term_taxonomy_id = @OtherCategoryID); |
Conclusions
That's it for the time being: we definitely hope that this tutorial will help those who are looking for a convenient way to perform some bulk tasks with the posts and/or categories of their WordPress site!
