wpcode

Recently I decided to use the WordPress post formats in my blog. If you're not familiar with post formats, hop over to the wordpress codex and read about them there.

Back? Good. :-)

I now have the ability to use different layouts for different kinds of posts, like "video", "image" or "status" - there are some additional tweaks I need to add to my theme; currently not all of my blog's views are respecting the new post formats. Work in progress, as always.

The problem I was facing after enabling the post formats: How can I add the post format to the posts that have been published in the past?

The easy but time-consuming way would be to edit every post via the administration, but that'll take quite a while if you have more than handful of posts, so I looked for a way to set the post format directly in the database for the old posts.

Post formats are stored in the tables wp_terms and wp_term_taxonomy, the relations between posts and terms are stored in the table wp_term_relationships -- so this will need some selecting across the different tables. If you're interested in what goes where in the WordPress database, see the description in the codex.

Basically what I want is: Find all post that currently have the category "Video" and assign the new post format "post-format-video" to them.

Let's first look inside the wp_terms table for a term named "Video":

SELECT * FROM `wp_terms` WHERE `name`='Video'
term_id | name  | slug  | term_group
57      | Video | video | 0

Ok, now let's check what taxonomy this term has - in this case, is it a category?

SELECT * FROM `wp_taxonomy` WHERE `term_id`=57
term_taxonomy_id | term_id | taxonomy | description | parent | count
61               | 57      | post_tag |             | 0      | 52

Hm, it seems that I did not use "video" as a category but as a tag. We also see that 52 posts were tagged "video" in the past. Now which posts exactly? Glad you asked, let's ask the database again:

SELECT * FROM `wp_term_relationships` WHERE `term_taxonomy_id`=61
object_id | term_taxonomy_id | term_order
40        | 61               | 0
58        | 61               | 0
...

The "object_id" is what we need - in this column are the IDs of the affected posts. In the same manner as above, we now only need to find the "term_taxonomy_id" of the post format which should be applied to the posts. Before we do go ahead, let's see if we can ask the database in a more efficient way to get the same result - the list of "object_id"s for the posts. If you look at the three queries above, we used the result of each query in the next query - like the "term_id" (57), the "term_taxonomy_id" (61)... how about using the query that gave that result instead of the result?

SELECT * FROM `wp_term_relationships`
WHERE `term_taxonomy_id`=(
	SELECT `term_taxonomy_id`
	FROM `wp_term_taxonomy` WHERE `term_id`=(
		SELECT `term_id` FROM `wp_terms` WHERE `name`='Video'
	)
)
object_id | term_taxonomy_id | term_order
40        | 61               | 0
58        | 61               | 0
...

Aha! :-) Sub-Queriesrule.
(Mind you, a real database wizard will come up with a better and more performant way of combining the infos, but this worked for me).

Here is the query to get the post format taxonomy info:

SELECT `term_taxonomy_id`
FROM `wp_term_taxonomy`
WHERE `term_id` IN (
	SELECT `term_id`
	FROM `wbr_terms`
	WHERE `slug`='post-format-video'
)
term_taxonomy_id |
3998             |

Now we simply need to add new rows to the "wp_term_relationships" table: for each "object_id" from the first query a column 'object_id' with that id and a column 'term_taxonomy_id' with the "term_taxonomy_id" of the desired post format (in my case, 3998).

Assuming you have made a backup of your database,
assuming you have made a backup of your database,
assuming you have made a backup of your database,
assuming you have made a backup of your database,
assuming you have made a backup of your database,
assuming you really have made a backup of your database,

let's put the pieces together:

INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) 
SELECT DISTINCT `object_id`, (
	SELECT `term_taxonomy_id`
	FROM `wp_term_taxonomy`
	WHERE `term_id` IN (
		SELECT `term_id`
		FROM `wp_terms`
		WHERE `slug`='post-format-video'
	)
)
FROM `wp_term_relationships`
WHERE `term_taxonomy_id`=(
	SELECT `term_taxonomy_id`
	FROM `wp_term_taxonomy`
	WHERE `term_id` IN (
		SELECT `term_id`
		FROM `wp_terms`
		WHERE `slug`='video'
	)
)
Row inserted: 52

Bam. Now we have a query that can be used to add post format X to posts already having a term Y.

Disclaimer: Fiddling with the database of your WordPress installation may lead to potential loss of your data. Never ever do this without a backup. If your not sure what this is all about, don't touch this. The above worked for my installation, it may not work for your's.