php– 删除Woocommerce中重复的导入订单

概述我在Woocommerce中有重复的订单,我想删除它们只保留独特的清洁簿记.我不擅长SQL,我写了这个请求但是当有重复时它列出了两个重复.SELECT * FROM `wp_posts` WHERE post_type = 'shop_order' AND post_status = 'wc-completed' GROUP BY post_date

我在Woocommerce中有重复的订单,我想删除它们只保留独特的清洁簿记.

我不擅长sql,我写了这个请求但是当有重复时它列出了两个重复.

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

这给了我307个结果.

如何编写删除重复项的正确请求并仅保留唯一订单？

样本数据：

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   

编辑：

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

给了我614个结果,这是前一个请求的两倍.

wordpress：5.0
Woocommerce：3.5.2最佳答案首先,如果您不知道,woocommerce订单数据位于四(4)个表中：

> wp_posts
> wp_postMeta
> wp_woocommerce_order_items
> wp_woocommerce_order_itemMeta

所以下面的钩子函数使用WPDB类和方法.它会：

>在一个查询中获取所有重复的订单ID(最低ID)(搜索通常唯一的重复订单密钥)
>在一个查询中删除所有重复查询的订单.

但请务必始终先进行数据库备份.

代码可以在任何前端页面加载中一次性完成工作(在使用后删除)：

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    ");}

代码位于活动子主题(或活动主题)的function.PHP文件中.经过测试和工作.

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

总结

以上是内存溢出为你收集整理的php – 删除Woocommerce中重复的导入订单全部内容，希望文章能够帮你解决php – 删除Woocommerce中重复的导入订单所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。