Duplicate Orders Issue When Syncing WooCommerce Sites through REST API

Question

I have been wrestling with a duplication problem with regard to syncing orders between a source and destination site via WooCommerce orders rest API endpoint:

/wp-json/wc/v3/orders

I have come up with the following code which successfully retrieves orders data from the source and creates on the destination, but it repeatedly gets the same order from the source and creates it again and again with a new ID automatically assigned to it.

I, therefore, expect orders to be synced only once and then updated if their status changes on the source site

if (!empty($orders) && is_array($orders)) {
      
       foreach ($orders as $order) {
          
           if (isset($order['status']) && isset($order['customer_id'])) {
               
               $first_name = $order['billing']['first_name'];
               $last_name = $order['billing']['last_name'];
               $phone = $order['billing']['phone'];
               
              
   //line items             
if (isset($order['line_items']) && is_array($order['line_items'])) {
   
   foreach ($order['line_items'] as $item) {
       $line_item_data = array(
           
           'name' => $item['name'],
           'quantity' => $item['quantity'],
           'price' => $item['price'],
           'subtotal' => $item['subtotal'],
           'total' => $item['total'],
           'product_id' => $item['product_id'],
       );

      
       $line_items_data[] = $line_item_data;
   }
}


          
               $order_data = array(
                   'status' => $order['status'],
                   'customer_id' => $order['customer_id'],
                   'billing' => array(
                       'first_name' => $first_name,
                       'last_name' => $last_name,
                       'phone' => $phone,
                   ),
                   'line_items' => $line_items_data,
                   
               );

At first, I tried to match both sites' order IDs, which is of course not a recommended practice but worth a shot, by

$url .= '/' . $order_data['number'];

Now that orders on both sites had the same identifier, the repetition of orders was prevented but it led to the accumulation of line_items fields (including quantity, price, etc.)

So, at the second step, I removed the above line and tried to make orders distinct by including the id property in line_items array:

  $line_item_data = array(
            'id' => $item['id'], //adding id object
            'name' => $item['name'],
            'quantity' => $item['quantity'],
            'price' => $item['price'],
            'subtotal' => $item['subtotal'],
            'total' => $item['total'],
            'product_id' => $item['product_id'],
        );

but I finally found out that this is not allowed, so this method too could not solve the problem.

{
   "code": "woocommerce_rest_invalid_item_id",
   "message": "Order item ID provided is not associated with order.",
   "data": {
       "status": 400
   }
}

It seems the issue is somehow due to the lack of a mapping mechanism, a track of Source IDs and their correspondent Destination IDs, without which the same orders on the source are fetched over and over again, instead of having their status updated. I wonder if there is any other way to solve this frustrating issue. Any help will be appreciated.

Full Code:

// Cron Schedule to Run the Code
add_filter('cron_schedules', 'add_every_minute_interval');

function add_every_minute_interval($schedules) {
    $schedules['every_minute'] = array(
        'interval' => 60, 
        'display' => __('Every Minute')
    );
    return $schedules;
}


add_action('wp_loaded', 'schedule_order_sync');

function schedule_order_sync() {
    if (!wp_next_scheduled('sync_orders_event')) {
        wp_schedule_event(time(), 'every_minute', 'sync_orders_event');
    }
}


add_action('sync_orders_event', 'sync_orders_from_source_to_destination');

 

    // Function to create order on destination site
    function create_order_on_destination_site($order_data, $api_url, $consumer_key, $consumer_secret) {
        $url = $api_url . '/wp-json/wc/v3/orders';

        

        $response = wp_remote_post($url, array(
            'headers' => array(
                'Authorization' => 'Basic ' . base64_encode($consumer_key . ':' . $consumer_secret),
                'Content-Type' => 'application/json',
            ),
            'body' => json_encode($order_data),
        ));

        if (is_wp_error($response)) {
            return false;
        }

        $body = wp_remote_retrieve_body($response);
        $created_order = json_decode($body, true);

        return $created_order;
    }
    
    // Function to get orders from source site
    function get_orders_from_source_site($api_url, $consumer_key, $consumer_secret) {
        $url = $api_url . '/wp-json/wc/v3/orders';
        $response = wp_remote_get($url, array(
            'headers' => array(
                'Authorization' => 'Basic ' . base64_encode($consumer_key . ':' . $consumer_secret),
            ),
        ));

        if (is_wp_error($response)) {
            return array();
        }

        $body = wp_remote_retrieve_body($response);
        $orders = json_decode($body, true);

        return $orders;
    }

// Main function to sync orders from source to destination
function sync_orders_from_source_to_destination() {
    // Source site API credentials
    $sourceSiteApiUrl = 'https://source.com';
    $sourceConsumerKey = 'xxxxxxxxxxxxxxxxxxxx';
    $sourceConsumerSecret = 'xxxxxxxxxxxxxxxxxxxx';

    // Destination site API credentials
    $destinationSiteApiUrl = 'https://destination.com';
    $destinationConsumerKey = 'xxxxxxxxxxxxxxxxxxxx';
    $destinationConsumerSecret = 'xxxxxxxxxxxxxxxxxxxx';

   

    // Get orders from source site
    $orders = get_orders_from_source_site($sourceSiteApiUrl, $sourceConsumerKey, $sourceConsumerSecret);


    
    if (!empty($orders) && is_array($orders)) {
        
        foreach ($orders as $order) {
           
            if (isset($order['status']) && isset($order['customer_id'])) {
               
                $first_name = $order['billing']['first_name'];
                $last_name = $order['billing']['last_name'];
                $phone = $order['billing']['phone'];
                
               
                
if (isset($order['line_items']) && is_array($order['line_items'])) {
    
    foreach ($order['line_items'] as $item) {
        $line_item_data = array(
            
            'name' => $item['name'],
            'quantity' => $item['quantity'],
            'price' => $item['price'],
            'subtotal' => $item['subtotal'],
            'total' => $item['total'],
            'product_id' => $item['product_id'],
        );

       
        $line_items_data[] = $line_item_data;
    }
}





                // order data
                $order_data = array(
                    'status' => $order['status'],
                    'customer_id' => $order['customer_id'],
                    'billing' => array(
                        'first_name' => $first_name,
                        'last_name' => $last_name,
                        'phone' => $phone,
                    ),
                    'line_items' => $line_items_data,
                    //etc
                );


                // Create order on destination site
                $created_order = create_order_on_destination_site($order_data, $destinationSiteApiUrl, $destinationConsumerKey, $destinationConsumerSecret);

                if ($created_order) {
                    echo 'Order ' . $created_order['id'] . ' created on destination site';
                } else {
                    echo 'Failed to create order on destination site';
                }
            } else {
                echo 'Order data is missing required fields';
            }
        }
    } else {
        echo 'No orders fetched from the source site';
    }
}
 

Answer 1

The correct approach is to clarify the relationship between the various data tables to be inserted: which is the primary table, which is the child table, and whether it is one-to-one or one-to-many or many-to-many. To synchronize data, you can use redis queues or kafka. Produce formatted data and push it to the queue, then enable consumers to consume it and synchronize it with the data table. Ensure that the data table has a unique index for the order number field, for example. Prevent insertion of duplicate data. You can maintain a redis collection to store the consumed orders.