Filter orders by product attribute term in WooCommerce Admin orders List

Question

In WooCommerce admin orders list, I am trying to filter orders that have order item(s) (product variation(s)) with a specific product attribute and term value. The problem is I am always getting no orders.

This is my code (added to my functions.php file):

add_filter('posts_request', 'custom_orders_filter', 10, 2);  
function custom_orders_filter($sql, $query) {

    global $pagenow, $wpdb;

    if (is_admin() && $pagenow === 'edit.php' && isset($_GET['post_type']) && $_GET['post_type'] === 'shop_order') {
        $product_option_key = 'pa_type-magazine'; // Replace with the desired meta_key
        $product_option_value = 'digital'; // Replace with the desired search term

        if ($query->is_main_query()) {
            $modified_sql = $sql;
            $modified_sql .= " JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS meta1 ON {$wpdb->prefix}posts.ID = meta1.order_id ";
            $modified_sql .= $wpdb->prepare(" AND meta1.meta_key = %s AND meta1.meta_value = %s ", $product_option_key, $product_option_value);

            $sql = $modified_sql;
        }
    }

    return $sql;
}

I also, just for debugging purposes, tried make a direct SQL query with PhpMyAdmin:

SELECT * FROM `wp_woocommerce_order_itemmeta` WHERE meta_value="digital";

And I successfully get 3 orders.

What could be the problem?

Answer 1

Your query is a bit wrong and incomplete. Also you need to insert the additional "JOIN" sub-query(s) and the other one inside the query using str_replace().

Now with the code below, I add 2 dynamic dropdowns filters (Ajax powered):

• The first one list all product attribute taxonomies
• The second one is hidden until you select an attribute, and shows dynamically the corresponding list of terms for this selected attribute.

Here is the complete code:

// Add 2 dynamic dropdowns to filter orders by order item variation product attribute / value
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 ) ) return;
    
    if ( isset($_GET['attr_tax']) && ! empty($_GET['attr_tax']) ) {
        $attr_slug = esc_attr($_GET['attr_tax']);
        $style = '';
        $terms = get_terms( array( 'taxonomy' => 'pa_'.$attr_slug ) );
    } else {
        $attr_slug = '';
        $style  = 'style="display:none"';
    }
    // Get all product attribute taxonomies
    $attribute_taxonomies = wp_list_pluck( wc_get_attribute_taxonomies(), 'attribute_label', 'attribute_name' );

    // First dropdown with product attribute taxonomies
    echo '<select name="attr_tax" id="filter-by-item-attribute">
    <option value="">' . __('Filter by Attribute', 'woocommerce') . '</option>';

    foreach ( $attribute_taxonomies as $taxonomy_slug => $taxonomy_name ) {
        printf( '<option value="%s"%s>%s</option>', $taxonomy_slug,
        selected( $taxonomy_slug, $attr_slug ), $taxonomy_name );
    }
    echo '</select>';
    
    $attr_val = isset($_GET['attr_val']) && ! empty($_GET['attr_val']) ? esc_attr($_GET['attr_val']) : '';

    // 2nd dropdwn (hidden) with the selected product attribute terms list
    echo '<select name="attr_val" id="filter-by-item-attr-value"' . $style . '>
    <option value="">' . __('Select a value', 'woocommerce') . '</option>';

    if ( ! empty($terms) ) {
        foreach ( $terms as $term ) {
            printf( '<option value="%s"%s>%s</option>', $term->slug,
            selected( $attr_val, $term->slug ), $term->name );
        }
    }
    echo '</select>';

    // Enqueing Javascript (Jquery + Ajax)
    wc_enqueue_js("$('form#posts-filter').on('change', 'select[name=attr_tax]', function(){
        if( $('select[name=attr_tax] :selected').val() != '' ) {
            var term = $('select[name=attr_tax] :selected').val();
            console.log('selected tax: pa_'+term);
            $.ajax({
                type: 'POST',
                url: '" . admin_url('/admin-ajax.php') . "',
                data: {
                    'action'    : 'product_attribute_terms',
                    'attr_value': term
                },
                success: function (response) {
                    $('select[name=attr_val]').show().find('option').remove().end().append(response);
                }
            });
        } else $('select[name=attr_val]').hide();
    });");
}

// The PHP AJAX Receiver
add_action('wp_ajax_product_attribute_terms', 'get_select_options_html');
function get_select_options_html() {
    if ( isset($_POST['attr_value']) ) {
        // Get the terms from the select product attribute
        $terms = get_terms( array( 'taxonomy' => 'pa_'.esc_attr($_POST['attr_value']) ) );
        if ( ! empty($terms) ) {
            // The options html
            $html ='<option value="">' . __('Select a value', 'woocommerce') . '</option>';
            foreach ( $terms as $term ) {
                $html .= sprintf( '<option value="%s">%s</option>', $term->slug, $term->name );
            }
        }
        wp_die($html); // We send back the html to jquery
    }
    wp_die();
}

// Change the main SQL query
add_filter('posts_request', 'custom_orders_filter', 20, 2);
function custom_orders_filter($sql, $query) {
    global $pagenow, $wpdb;

    if ( is_admin() && $pagenow === 'edit.php' && isset($_GET['post_type']) && 'shop_order' === $_GET['post_type']
    && isset($_GET['attr_tax']) && ! empty($_GET['attr_tax']) && isset($_GET['attr_val']) && ! empty($_GET['attr_val']) ) {
        $join = " INNER JOIN {$wpdb->prefix}woocommerce_order_items AS oi
            ON {$wpdb->prefix}posts.ID = oi.order_id
            INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS oim
            ON oi.order_item_id = oim.order_item_id";
        $and = $wpdb->prepare(" 
            AND oim.meta_key = '%s' AND oim.meta_value = '%s'", 
            'pa_' . esc_attr($_GET['attr_tax']), esc_attr($_GET['attr_val']) );
            
        $sql = str_replace( "FROM wp_posts", "FROM wp_posts  {$join}", $sql );
        $sql = str_replace( "WHERE 1=1  AND", "WHERE 1=1  {$and} AND", $sql );
    }
    return $sql;
} 

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