How to Set Older WordPress Posts to Draft with WP CLI

In one of the slack channels I follow, this question came up

If I wanted to set any post older than a certain date to Draft, what’s the best way to do that w/ wp-cli?

This seemed like an interesting problem so I started looking into it.

Get All WordPress Posts Before a Certain Date

wp db query 'SELECT ID FROM `wp_posts` WHERE `post_date`<"2014-01-01";' --skip-column-names

We’re going to want these IDs as a comma separate list which we can do by adding | paste -s -d ',' - to the end of our command.

wp db query 'SELECT ID FROM `is_posts` WHERE `post_date`<"2014-01-01";' --skip-column-names | paste -s -d ',' -

Using the resulting list we’re going to use wp post list to make sure we’re getting posts (not pages, revisions, attachments, custom post types, etc.).

wp post list --post__in=$(wp db query 'SELECT ID FROM `is_posts` WHERE `post_date`<"2014-01-01";' --skip-column-names | paste -s -d ',' -)

Finally we’re going to add --format=ids because we only want the IDs for these posts.

wp post list --post__in=$(wp db query 'SELECT ID FROM `is_posts` WHERE `post_date`<"2014-01-01";' --skip-column-names | paste -s -d ',' -) --format=ids

Updating the Post Status

To update the post status for a single post to draft, we could update post 123 to draft with the command

wp post update 123 --post_status=draft

We could update multiple posts by listing multiple post IDs, e.g.

wp post update 123 765 --post_status=draft

Putting it All Together

Since we can update the post status for multiple posts with a command like

wp post update <post IDs> --post_status=draft

and we can get a list of the post IDs we want with the command

wp post list --post__in=$(wp db query 'SELECT ID FROM `is_posts` WHERE `post_date`<"2014-01-01";' --skip-column-names | paste -s -d ',' -) --format=ids

we can combine these to make the following:

wp post update $(wp post list --post__in=$(wp db query 'SELECT ID FROM `is_posts` WHERE `post_date`<"2014-01-01";' --skip-column-names | paste -s -d ',' -) --format=ids) --post_status=draft

Sal Ferrarello (@salcode)

Sal is a PHP developer with a focus on the WordPress platform. He is a conference speaker with a background including Piano Player, Radio DJ, Magician/Juggler, Beach Photographer, and High School Math Teacher. Sal can be found professionally at WebDevStudios, where he works as a senior backend engineer.