5

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

halfer
  • 19,824
  • 17
  • 99
  • 186
Louis
  • 2,548
  • 10
  • 63
  • 120
  • Do you want to actually _delete_ records, or just view everything besides the duplicates? – Tim Biegeleisen Dec 19 '18 at 09:34
  • Hi, I want to first list them, and then delete them. – Louis Dec 19 '18 at 09:35
  • What makes a pair of records duplicate? – Tim Biegeleisen Dec 19 '18 at 09:47
  • It's the post_date field and the post_author – Louis Dec 19 '18 at 09:51
  • Which record do you want to retain from all the duplicates? – Tim Biegeleisen Dec 19 '18 at 10:02
  • There will be at most 2 duplicates each time there is duplication. And I want to keep any of the two duplicates. – Louis Dec 19 '18 at 10:04
  • Are you more familiar with consuming APIs? You could try consuming the woocommerce API. List orders: https://woocommerce.github.io/woocommerce-rest-api-docs/#list-all-orders Delete orders: https://woocommerce.github.io/woocommerce-rest-api-docs/#delete-an-order) This might be easier for you than using SQL? – Hans Ott Dec 19 '18 at 10:54
  • Yes API, I didn't think about it. Thanks. Do yo know if I can achieve deleting duplicate orders with this ? – Louis Dec 19 '18 at 11:00
  • @Louis API is not needed… A SQL Query is the lightest and efficient way. How do you know that orders are duplicates? What are their similarities? How you defined them? This is important as it will make an SQL query working for your case. – LoicTheAztec Dec 19 '18 at 13:32
  • Hey @LoicTheAztec thanks for being here. They are duplicates : same amount (€) same date and hour, and same client name (it came from an poorly made import/export) – Louis Dec 20 '18 at 14:47
  • @Louis Ok I am going to try to make it for you… give me some time for it. I will notify you. – LoicTheAztec Dec 20 '18 at 14:56
  • Thanks @LoicTheAztec, look at Tim's answer, he did something good i guess – Louis Dec 20 '18 at 15:02
  • ok I see. So manually it is then ? (it's easy to list them by date and see the duplicates in the backoffice. ) – Louis Dec 20 '18 at 15:14

2 Answers2

2

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.

LoicTheAztec
  • 229,944
  • 23
  • 356
  • 399
  • I have the same problem after importing orders and each order has been imported 5 times. However the order numbers are unique for each additional duplicate. Any ideas how I can get around this? – Dayley Sep 05 '19 at 03:08
  • Having the same issue. We have order that is displayed 3 times, but with different order number. – Mathias Beck Nov 14 '19 at 12:25
1

Try a delete join, arbitrarily retaining the record with the max ID:

DELETE
FROM wp_posts
WHERE post_type = 'shop_order' AND post_status = 'wc-completed' AND
      ID NOT IN (SELECT ID
                 FROM (SELECT MAX(ID) AS ID FROM wp_posts
                       GROUP BY post_date, post_author) t);

If you just want to view the posts with logical duplicates removed, then try:

SELECT w1.*
FROM wp_posts w1
INNER JOIN
(
    SELECT post_date, post_author, MAX(ID) AS max_id
    FROM wp_posts
    WHERE post_type = 'shop_order' AND post_status = 'wc-completed'
    GROUP BY post_date, post_author
) w2
    ON w1.post_date = w2.post_date AND
       w1.post_author = w2.post_author AND
       w1.ID = w2.max_id
WHERE w1.post_type = 'shop_order' AND w1.post_status = 'wc-completed'
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I need to search in wp_posts only the matching : WHERE post_type = 'shop_order' AND post_status = 'wc-completed' – Louis Dec 19 '18 at 10:23
  • You can see in the WHERE of my question – Louis Dec 19 '18 at 10:30
  • @Louis I updated my answer, sorry for missing that. It doesn't change the overall strategy. – Tim Biegeleisen Dec 19 '18 at 10:45
  • Thanks ! I have 686 results with the `SELECT w1.*` command you suggested. Seems a lot. Does this include all duplicates (both the unique and its duplicate)? – Louis Dec 19 '18 at 10:59
  • No, it should _not_ include duplicates, as you have defined them. – Tim Biegeleisen Dec 19 '18 at 11:00
  • mmm ok. I simulated the DELETE command you suggested, and it would delete 451 lignes from my database. Still seems a lot. Because I think there is 308 duplicates – Louis Dec 19 '18 at 11:05
  • Then I think the problem is with your logic, and not my query. I reopened your question, by the way, but perhaps I made a mistake in doing that. – Tim Biegeleisen Dec 19 '18 at 11:06
  • Yes you maybe right... I am trying to figure out how many duplicates there are. – Louis Dec 19 '18 at 11:30
  • @LoicTheAztec might be able to help on this Woo related question? – Louis Dec 19 '18 at 11:31