Update an ACF field in all WooCommerce processing orders

Question

The objective is to get/query/loop through all orders which have order_status "Processing", and if they have no items in backorder, update an advanced custom field 'internal_status' to value 'Ready to Pack'

Right now for a method of testing (to keep it simple and see if it's working) I am just trying to update the custom field whenever an order is passed to status "Completed" (no condition for "items in backorder" yet)

Based on Auto completed status for all existing processing orders in WooCommerce answer code, here is my code attempt:

function auto_update_orders_internal_status(){
     // Get all current "processing" customer orders
    $processing_orders = wc_get_orders( $args = array(
        'numberposts' => -1,
        'post_status' => 'wc-processing',
    ) );
    if(!empty($processing_orders))
        foreach($processing_orders as $order)
        
   add_action('acf/save_post', 'update_internal_status_ready_to_pack');
            
}

add_action( 'woocommerce_order_status_completed', 'auto_update_orders_internal_status' );



function update_internal_status_ready_to_pack ( $order_id ) {
    
    $internalstatus = 'Ready to Pack';
    update_field( 'internal_status', $internalstatus, $order_id );

}

One thing I am aware I don't fully grasp here is the method of querying / getting all orders on "Processing status" and updating their corresponding fields.

Answer 1

You should try instead the following lightweight and effective way (with a custom function that uses a WPDB SQL query, to query all order Ids that have not backordered items):

/*
 * Custom function that query all orders without backordered items
 *
 * @param  bool|string $status  Order status can be defined (optional)
 * @return array
 */
function get_orders_ids_without_backordered_items( $status = false ) {
    global $wpdb;

    $post_status_query = $status ? "AND p.post_status = 'wc-" . str_replace('wc-', '', $status) . "'" : '';

    return (array) $wpdb->get_col( "
        SELECT p.ID
        FROM {$wpdb->prefix}posts p
        WHERE p.ID NOT IN (
            SELECT DISTINCT oi.order_id FROM {$wpdb->prefix}woocommerce_order_items oi
            INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
            WHERE oim.meta_key = 'Backordered'
        ) $post_status_query
    " );
}

// Update ACF 'internal_status' custom field on processing orders.
add_action( 'woocommerce_order_status_completed', 'auto_update_orders_internal_status', 10, 2 ); // Optional (to be removed if not necessary)
add_action( 'woocommerce_order_status_processing', 'auto_update_orders_internal_status', 10, 2 );
function auto_update_orders_internal_status( $order_id, $order ){
     // Get all "processing" orders without backordered items
    $orders_ids = get_orders_ids_without_backordered_items( 'processing' );
    if( ! empty($orders_ids) ) {
        foreach($orders_ids as $post_id) {
            $acf_value = 'Ready to Pack';
            if ( get_field( 'internal_status', $post_id ) !== $acf_value ) {
                update_field( 'internal_status', $acf_value, $post_id ); 
            }
        }
    }
}

Code goes in functions.php file of the active child theme (or active theme). The first function is tested and works. The 2nd function doesn't throw any error.

Related: Change order status for backordered items in Woocommerce