删除Woocommerce中重复的导入订单

Question

I have duplicate orders in Woocommerce and I would like to delete them to only keep unique for clean bookkeeping.

I am not good at SQL, I wrote this request but it lists both duplicate when there is a duplicate.

SELECT *
FROM `wp_posts` 
WHERE post_type = 'shop_order' AND post_status = 'wc-completed' 
GROUP BY post_date 
HAVING count(*) > 1 
ORDER BY `wp_posts`.`post_date` DESC

which gives me 307 results.

How can I write the right request to delete the duplicates and only keep unique orders?

Sample data:

ID  post_author post_date       post_date_gmt   post_content    post_title  post_excerpt    post_status comment_status  ping_status post_password   post_name   to_ping pinged  post_modified   post_modified_gmt   post_content_filtered   post_parent guid    menu_order  post_type   post_mime_type  comment_count   
22282   227 2018-02-04 01:00:00 2018-02-04 00:00:00     Order – February 4, 2018 @ 01:00 AM       wc-completed    open    closed      order-4-02-18-6         2018-03-19 17:12:32 2018-03-19 16:12:32     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      2   
22277   0   2018-01-29 01:00:00 2018-01-29 00:00:00     Order – January 29, 2018 @ 01:00 AM       wc-completed    open    closed      order-29-01-18-2            2018-03-19 17:12:33 2018-03-19 16:12:33     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      2   
22276   0   2018-01-28 01:00:00 2018-01-28 00:00:00     Order – January 28, 2018 @ 01:00 AM       wc-completed    open    closed      order-28-01-18-2            2018-03-19 17:12:33 2018-03-19 16:12:33     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      2   
22275   0   2018-01-25 01:00:00 2018-01-25 00:00:00     Order – January 25, 2018 @ 01:00 AM       wc-completed    open    closed      order-25-01-18-2            2018-03-19 17:12:33 2018-03-19 16:12:33     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      2   
22232   154 2018-01-24 00:00:00 2018-01-24 00:00:00     Order – January 24, 2018 @ 12:00 AM       wc-completed    open    closed      order-24-01-18-4            2018-01-24 00:00:00 2018-01-24 00:00:00     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      0   
22230   213 2018-01-23 00:00:00 2018-01-23 00:00:00     Order – January 23, 2018 @ 12:00 AM       wc-completed    open    closed      order-23-01-18-2            2018-01-23 00:00:00 2018-01-23 00:00:00     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      0   
22229   0   2018-01-22 00:00:00 2018-01-22 00:00:00     Order – January 22, 2018 @ 12:00 AM       wc-completed    open    closed      order-22-01-18-2            2018-01-22 00:00:00 2018-01-22 00:00:00     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      0   
22228   224 2018-01-20 00:00:00 2018-01-20 00:00:00     Order – January 20, 2018 @ 12:00 AM       wc-completed    open    closed      order-20-01-18-2            2018-01-20 00:00:00 2018-01-20 00:00:00     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      0   
22227   0   2018-01-19 00:00:00 2018-01-19 00:00:00     Order – January 19, 2018 @ 12:00 AM       wc-completed    open    closed      order-19-01-18-2            2018-01-19 00:00:00 2018-01-19 00:00:00     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      0   
22226   0   2018-01-17 00:00:00 2018-01-17 00:00:00     Order – January 17, 2018 @ 12:00 AM       wc-completed    open    closed      order-17-01-18-2            2018-01-17 00:00:00 2018-01-17 00:00:00     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      0   
22225   0   2018-01-16 00:00:00 2018-01-16 00:00:00     Order – January 16, 2018 @ 12:00 AM       wc-completed    open    closed      order-16-01-18-2            2018-01-16 00:00:00 2018-01-16 00:00:00     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      0   
22224   0   2018-01-15 00:00:00 2018-01-15 00:00:00     Order – January 15, 2018 @ 12:00 AM       wc-completed    open    closed      order-15-01-18-4            2018-01-15 00:00:00 2018-01-15 00:00:00     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      0   
22222   0   2018-01-14 00:00:00 2018-01-14 00:00:00     Order – January 14, 2018 @ 12:00 AM       wc-completed    open    closed      order-14-01-18-6            2018-01-14 00:00:00 2018-01-14 00:00:00     0   https://www.hemen-biarritz.com/?post_type=shop_ord...   0   shop_order      0   

EDIT:

SELECT DISTINCT post_date FROM `wp_posts` WHERE post_type = 'shop_order' AND post_status = 'wc-completed'

gives me 614 results, Which is the double of the previous request.

WordPress: 5.0
Woocommerce: 3.5.2

Answer 1

First if you don't know, woocommerce Order data Is located in four (4) tables:

• wp_posts
• wp_postmeta
• wp_woocommerce_order_items
• wp_woocommerce_order_itemmeta

So the following hooked function use WPDB class and methods. It will:

1. get all duplicated orders IDs (the lowest ID) in one query (searching for duplicated order Keys which are normally unique)
2. delete all duplicated queried orders in one query.

But be sure to always make a database backup before.

The code will work do the job in one shot on any front end page load (to be removed after usage):

add_action( 'template_redirect', 'progressive_delete_duplicated_orders' );
function progressive_delete_duplicated_orders() {
    global $wpdb;

    // Get duplicated orders (smaller ID)
    $duplicated_orders = (array) $wpdb->get_col("
        SELECT p.ID, pm.meta_value, COUNT(*) as c
        FROM {$wpdb->prefix}postmeta as pm
        INNER JOIN {$wpdb->prefix}posts as p ON p.ID = pm.post_id
        WHERE p.post_status = 'wc-completed'
        AND pm.meta_key = '_order_key'
        GROUP BY pm.meta_value
        HAVING c > 1
    ");

    if( sizeof($duplicated_orders) == 1 )
        $where_clause = 'WHERE p.ID = ' . reset($duplicated_orders);
    elseif( sizeof($duplicated_orders) > 1 )
        $where_clause = 'WHERE p.ID IN (' . implode( ',',$duplicated_orders ) . ')';
    else return; // Exit

    // Delete duplicated Orders data everywhere
    $wpdb->query("
        DELETE p, pm, woi, woim
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
        INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON p.ID = woi.order_id
        INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woim ON woi.order_item_id = woim.order_item_id
        $where_clause
    ");
}

Code goes in function.php file of your active child theme (or active theme). Tested and work.

The duplicated orders will be removed on first frontend page load. So after that you can remove or comment the code.