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.
Eine Reaktion zu “Add post format X to posts with category Y”
Kommentare sind geschlossen.