How to Delete Completed Orders in Woocommerce Using a My SQL Query

how to delete completed orders in woocommerce using a my sql query

Woocommerce orders are stored in the post table, postmeta table, woocommerce_order_items, and woocommerce_order_itemmeta tables. Various parts of an order are stored in different tables. addition to that the order status is managed through the taxonomies which stores the order status list.
Anyone who is/will be writing this query have to write at least 5-6 queries minimum or
single large join query.

So, my suggestion to you should uninstall WooCoommerce, if you don't have much larger product setup. To do this you can follow this link which will delete the data

How do I delete orders from a cloned WooCommerce site?

You can use this plugin in order to delete in bulk
https://wordpress.org/plugins/woocommerce-store-toolkit

Woocommerce delete download permission to refunded orders, using a query trigger in wc core function

After searching some posts i have modded the query to run in a function, its working and prevent download permission after digital product order become refunded:

function mysite_refunded($order_id) {
$sql = "DELETE
FROM wp_woocommerce_downloadable_product_permissions
WHERE EXISTS (SELECT 1 FROM wp_posts
WHERE wp_posts.ID = wp_woocommerce_downloadable_product_permissions.order_id AND wp_posts.post_status = 'wc-refunded');";
global $wpdb;
$wpdb->get_results($sql);
}

add_action( 'woocommerce_order_status_refunded', 'mysite_refunded', 10, 1);

sql query to bulk remove woocommerce products by publish date

Woocommerce products are located mainly in 2 tables, the wp_posts and wp_postmeta. If you want to delete them, you will have to use a JOIN clause to combine the product rows from these tables.

Suppose you want to delete every product added before the 1st of September of 2018. You can use this query below.

DELETE wp_posts, wp_postmeta FROM wp_posts 
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_type = 'product' and wp_posts.post_date < '2018-09-01'

Delete processing orders after 10 days

This function will handle it:

function expire_after_x_days(){
global $wpdb;
// Get current time
$today = date("mdy");

// set time to expire
$time_to_expire = "-10 days";
$expiration_date = date("mdy", strtotime( $today . $time_to_expire));

// Get orders with processing status
$result = $wpdb->get_results("SELECT * FROM $wpdb->posts WHERE post_type = 'shop_order' AND post_status = 'wc-processing'");

if( !empty($result)) foreach ($result as $order){
// Get order's time
$order_time = get_the_time('mdy', $order->ID );

// Compare order's time with current time -10 days
if ( $order_time < $expiration_date ){

// Update order status
$orders = array();
$orders['ID'] = $order->ID;
$orders['post_status'] = 'wc-cancelled';
wp_update_post( $orders );
}
}
}

// Use the best HOOK for your case
add_action( 'admin_footer', 'expire_after_x_days' );

// OR simply call the function if you are pasting this in your functions.php


Related Topics



Leave a reply



Submit