As of today there is no official guide for this simple need. If you have old orders more than 1000, you are right if you want to get rid of old orders – just to fasten your website.
So i decided to write my own script and apply to my web store with 30K order history.
Step1: I activated HPOS in dual-mode (aka safe mode).
Step2: 1 day passed and i disabled legacy mode and kept HPOS only
Step3: 1 more day passed and i deleted the oldest 5 orders from wp_postmeta.
Step4: Everything looked good, and i did the same for all orders.
Warning: You should take a backup before applying this code:
function delete_old_non_hpos_orders() {
$query_args = array(
'post_type' => 'shop_order',
'post_status' => 'any',
'posts_per_page' => 5,//change this number after making sure it is safe
'orderby' => 'date',
'order' => 'ASC',
);
$query = new WP_Query($query_args);
global $wpdb;
foreach ($query->posts as $post) {
$order_id = $post->ID;
// Check if the order exists in HPOS
$exists_in_hpos = $wpdb->get_var("SELECT id FROM {$wpdb->prefix}wc_orders WHERE id = $order_id");
if ($exists_in_hpos) {
echo $order_id.PHP_EOL;
$wpdb->delete("{$wpdb->prefix}postmeta", ['post_id' => $order_id]);
$wpdb->update(
"{$wpdb->prefix}posts",
['post_type' => 'shop_order_placehold'],
['ID' => $order_id]
);
}
}
}
if(isset($_GET["delete_old_non_hpos_orders"])){
add_action('wp',function(){
if(!current_user_can('administrator'))return;
delete_old_non_hpos_orders();
die('Stop');
});
}Once you placed this code to your theme’s functions.php or as a snippet, you can run YOURWEBSITEURL/?delete_old_non_hpos_orders=yes URL to see the how it works.
Once you are done with that, you must delete the code.
Discover more from WP DEV - Elvin Haci
Subscribe to get the latest posts sent to your email.
Hi I wrote a production version so you can skip pages with batch of 1000, after testing, for if someone has 50k + store size 🙂 might come in handy
function delete_old_non_hpos_orders() {
$query_args = array(
‘post_type’ => ‘shop_order’,
‘post_status’ => ‘any’,
‘posts_per_page’ => 8000,
‘orderby’ => ‘date’,
‘order’ => ‘ASC’,
‘paged’ => isset($_GET[‘paged’]) ? intval($_GET[‘paged’]) : 1, // Add pagination
);
$query = new WP_Query($query_args);
if ($query->have_posts()) { // Check if there are any posts
global $wpdb;
foreach ($query->posts as $post) {
$order_id = $post->ID;
// Check if the order exists in HPOS
$exists_in_hpos = $wpdb->get_var(“SELECT id FROM {$wpdb->prefix}wc_orders WHERE id = $order_id”);
if ($exists_in_hpos) {
echo $order_id . PHP_EOL;
$wpdb->delete(“{$wpdb->prefix}postmeta”, [‘post_id’ => $order_id]);
$wpdb->update(
“{$wpdb->prefix}posts”,
[‘post_type’ => ‘shop_order_placehold’],
[‘ID’ => $order_id]
);
}
}
// Pagination links for the next 8000 records
$total_pages = $query->max_num_pages;
$current_page = $query_args[‘paged’];
if ($current_page < $total_pages) {
$next_page_url = add_query_arg('paged', $current_page + 1, $_SERVER['REQUEST_URI']);
echo 'Next 8000‘;
}
} else {
echo “No more orders found.”; // Indicate the end of the orders
}
}
if (isset($_GET[“delete_old_non_hpos_orders”])) {
add_action(‘wp’, function () {
if (!current_user_can(‘administrator’)) return;
delete_old_non_hpos_orders();
die(‘Stop’);
});
}
You’re welcome
Thank you for sharing.
Btw, currently Woo provides wp-cli command for this.
Yesterday I used it and worked smooth.
wp wc hpos cleanup