WooCommerce: Filter Admin orders by product attribute term

Question

I have a store with many variable products. All orders are custom made specifically for the customer. This means that when we receive the product in the warehouse we need to find the order of the specific product. Right now we simply have to manually check most orders.

What we want to do that will save us valuable time is search orders by custom attribute terms. For example: We use attribute "size" for custom orders. We have 24 "size" terms (e.g. "12cm", :16cm" etc). We want to find the order based on the size term. For example: Find all orders who's size is "48cm".

Based on Extending search in backend orders list for product items by id or by sku, here is my code attempt:

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

    foreach ($orders as $order_post) {
        $order_id = $order_post->ID;
        $order = new WC_Order($order_id);
        $items = $order->get_items();
    
        foreach( $order->get_items() as $item_id => $item_values ) {
            if ( version_compare( WC_VERSION, '3.0', '<' ) ) {
                $product_id = $item_values['product_id'];
            } else {
                $product_id = $item_values->get_product_id();
            }
             $attribute = $item_values->get_data();
             
            // attribute value
            $value = $attribute['value'];
            
            // attribute slug
            $slug = $attribute['size'];
            
            add_post_meta($order_id, $slug, $value, true); //
        }
    }
    return array_merge($search_fields, array($slug));
}); 

But It didn't work. Any help is appreciated.

Answer 1

What you are trying to do can't really work in your case and it's a very heavy process that can crash admin or slow your entire web site.

Instead of what you can do is to display a dropdown of all your existing sizes values that will filter your orders:

Note that each product attribute taxonomy start with pa_ + the slug of the product attribute. I have set in the code the product attribute taxonomy for "Size" to pa_size.

For info the code below uses a very light SQL custom query.

// Custom function where metakeys / labels pairs are defined
function get_product_attribute_size_terms(){
    $taxonomy = 'pa_size';
    $options  = array();

    foreach ( get_terms( array('taxonomy' => 'pa_size' ) ) as $term ) {
        $options[$term->slug] = $term->name;
    }
    return $options;
}

// Add a dropdown to filter orders by variations size
add_action( 'restrict_manage_posts', 'display_admin_shop_order_by_meta_filter' );
function display_admin_shop_order_by_meta_filter(){
    global $pagenow, $typenow;

    if( 'shop_order' === $typenow && 'edit.php' === $pagenow ) {
        $domain    = 'woocommerce';
        $filter_id = 'by_size';
        $current   = isset($_GET[$filter_id])? $_GET[$filter_id] : '';

        echo '<select name="'.$filter_id.'">
        <option value="">' . __('Filter by Size…', $domain) . '</option>';

        $options = get_product_attribute_size_terms();

        foreach ( $options as $key => $label ) {
            printf( '<option value="%s"%s>%s</option>', $key,
                $key === $current ? '" selected="selected"' : '', $label );
        }
        echo '</select>';
    }
}

add_action( 'pre_get_posts', 'process_admin_shop_order_product_type_filter' );
function process_admin_shop_order_product_type_filter( $query ) {
    global $pagenow, $post_type, $wpdb;

    $filter_id = 'by_size';
    $taxonomy  = 'pa_size';

    if ( $query->is_admin && 'edit.php' === $pagenow && 'shop_order' === $post_type
         && isset( $_GET[$filter_id] ) && $_GET[$filter_id] != '' ) {

        $attribute_taxonomy = 'attribute_'.$taxonomy;

        $order_ids = $wpdb->get_col( $wpdb->prepare("
            SELECT DISTINCT opl.order_id
            FROM {$wpdb->prefix}wc_order_product_lookup opl
            INNER JOIN {$wpdb->prefix}postmeta pm
                ON opl.variation_id = pm.post_id
            WHERE pm.meta_key = %s
                AND pm.meta_value = '%s'
        ", $attribute_taxonomy, esc_attr($_GET[$filter_id]) ) );

        $query->set( 'post__in', count($order_ids) > 1 ? $order_ids : array(0) ); // Set queried order ids

        $query->set( 'posts_per_page', 25 ); // Set "posts per page"

        $query->set( 'paged', ( get_query_var('paged') ? get_query_var('paged') : 1 ) ); // Set "paged"
    }
}

Code goes in functions.php file of the active child theme (or active theme). Tested and works.

Related: Filter orders by product post type in WooCommerce admin orders list page