Search by order item SKU or ID in WooCommerce Orders Admin page

Question

What I am trying to do is to be able to search by order item SKU or ID in the WooCommerce Orders Admin page.

What I have found/done till now, but with no success is the following at functions.php file.

add_filter( 'woocommerce_shop_order_search_fields', 'woocommerce_shop_order_search_sku' );

function woocommerce_shop_order_search_sku( $search_fields ) {

    $args = array( 'post_type' => 'shop_order' );

    $orders = new WP_Query( $args );

    if ( $orders->have_posts() ) {
        while( $orders->have_posts() ) {
            $post = $orders->the_post();
            $order_id = get_the_ID();
            $order = new WC_Order( $order_id );
            $items = $order->get_items();
            foreach( $items as $item ) {
                $search_order_item_sku = wp_get_post_terms( $item['product_id'], 'search_sku' );
                foreach( $search_order_item_sku as $search_sku ) {
                    add_post_meta( $order_id, "_search_sku", $search_sku->sku );
                }
            }
        }
    };

    $search_fields[] = '_search_sku';

    return $search_fields;

}

I suppose the issue is the value of $search_sku at the line with the add_post_meta.

I have also tried it with get_sku(), $item['sku'] with no luck.

Answer 1

You have the right idea about saving extra metadata to the order. As jbby and helgatheviking suggest, there is no built-in postmeta for product_id or sku available by default in the woocommerce orders api. Your methodology for accessing and saving the metadata wasn't quite right, however. wp_get_post_terms will access custom taxonomy information, not metadata (use get_post_meta for that). You will be able to do what you were trying to do with this filter:

add_filter( 'woocommerce_shop_order_search_fields', function ($search_fields ) {
    $posts = get_posts(array('post_type' => 'shop_order'));

    foreach ($posts as $post) {
        $order_id = $post->ID;
        $order = new WC_Order($order_id);
        $items = $order->get_items();

        foreach($items as $item) {
            $product_id = $item['product_id'];
            $search_sku = get_post_meta($product_id, "_sku", true);
            add_post_meta($order_id, "_product_sku", $search_sku);
            add_post_meta($order_id, "_product_id", $product_id);
        }
    }

    return array_merge($search_fields, array('_product_sku', '_product_id'));
});

Strictly speaking you should probably move the calls to add_post_meta into a hook that runs when the order is originally saved to the database--this will prevent unnecessary legwork whenever you search through order.

Answer 2

@blacksquare, @jibby, @helgatheviking you are the men! This is the code that works, due to your help.

    //Search by product SKU in Admin Woocommerce Orders
add_filter( 'woocommerce_shop_order_search_fields', function ($search_fields ) {
    $posts = get_posts(array('post_type' => 'shop_order'));

    foreach ($posts as $post) {
        $order_id = $post->ID;
        $order = new WC_Order($order_id);
        $items = $order->get_items();

        foreach($items as $item) {
            $product_id = $item['product_id'];
            $search_sku = get_post_meta($product_id, "_sku", true);
            add_post_meta($order_id, "_product_sku", $search_sku);
        }
    }

    return array_merge($search_fields, array('_product_sku'));
});

Answer 3

While @Nikos and @blacksquare 's answers work, new post metas are added to every order on every search. If you have 100 orders and make 10 searches, there will be at least 100*10 = 1000 _product_sku entries in the wp_postmeta table. If some orders contain multiple products, there will be even more.

As @blacksquare suggested, add_post_meta should be called when the order is saved. That said, if the site is small and backend search performance isn't too much of a concern, the following code would work without creating redundant _product_sku entries.

add_filter( 'woocommerce_shop_order_search_fields', 'my_shop_order_search_fields') );

public function my_shop_order_search_fields( $search_fields ) {
    $orders = get_posts( array(
        'post_type' => 'shop_order',
        'post_status' => wc_get_order_statuses(), //get all available order statuses in an array
        'posts_per_page' => 999999, // query all orders
        'meta_query' => array(
            array(
                'key' => '_product_sku',
                'compare' => 'NOT EXISTS'
            )
        ) // only query orders without '_product_sku' postmeta
    ) );

    foreach ($orders as $order) {
        $order_id = $order->ID;
        $wc_order = new WC_Order($order_id);
        $items = $wc_order->get_items();
        foreach($items as $item) {
            $product_id = $item['product_id'];
            $search_sku = get_post_meta($product_id, '_sku', true);
            add_post_meta( $order_id, '_product_sku', $search_sku );
        }
    }

    return array_merge($search_fields, array('_product_sku')); // make '_product_sku' one of the meta keys we are going to search for.
}

While a better solution might be calling add_post_meta when an order is created, extra efforts are needed to create _product_sku for existing orders, and you have to create the _product_sku for orders made while the code isn't activated. For simplicity sake, I'd just use the solution suggested above.

p.s. @Nikos 's solution does have one (debatable) advantage - if you change a product's SKU after orders are made, Nikos's solution will find those orders using the new SKU, while the solution above will not. That said, a product's SKU should NOT be changed anyway, and it's debatable whether searching new SKUs should show old orders.

Answer 4

You may also want to look into https://uk.wordpress.org/plugins/woo-search-order-by-sku/ which does not overloads post meta, but alters the where clause.

Answer 5

As stated in answer by Ming Yeung, any solution which uses the add_post_meta() function will increase your database size, which might not be ideal for larger stores. That sort of approach will also potentially make the searching of Woocommerce orders in admin quite slow.

An alternative solution is to make use of one of the existing "index" post meta records that Woocommerce uses, and append your data to those records (but only append once).

The following code does exactly that. It does not add SKU (as asked in question) but instead allows for Woocommerce order search to include Country name (as opposed to country code). However this code could be easily adapted to also include SKU if needed:

// when doing a search within Woocommerce orders screen, include the Country name as part of the search criteria
// this code has been optimised to do as little DB work as possible (looks for any index values which haven't been updated yet, and updates them)
// this code also makes use of existing post meta "index" records, instead of creating additional new post meta rows (which would fill up database unnecessarily)
add_filter('woocommerce_shop_order_search_fields', function($search_fields) {
    global $wpdb;
    $records_to_update = $wpdb->get_results("SELECT * FROM $wpdb->postmeta WHERE (meta_key LIKE '%_address_index%') AND (meta_value NOT LIKE '%[HAS_COUNTRY]%')");
    foreach ($records_to_update as $record) {
        $order = new WC_Order($record->post_id);
        if ($record->meta_key == '_billing_address_index') {
            $country_name = WC()->countries->countries[ $order->get_billing_country() ];
        } else {
            $country_name = WC()->countries->countries[ $order->get_shipping_country() ];
        }
        update_post_meta($record->post_id, $record->meta_key, $record->meta_value . ' ' . $country_name . ' [HAS_COUNTRY]');
    }
    return $search_fields;
});