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
During my experiments with this I wrote a WP CLI command wp posts-before. The command can be installed as a WordPress plugin or as a WP CLI package with the command
This new command allows us to run something like
which returns all of the posts before that date.
This gives us the same results as
Excellent collection of CLI commands.
I had to run something a long these lines but ended up finding in my case the query by ID was all I needed. So my code looked more like:
wp post delete $(wp post list --post_type="meta" --id="829" --compare=">" --posts_per_page="100000" --format=ids) --force
It was quite the command and took a while to run, but it worked.