Custom select disappears after filtering on WooCommerce admin orders page

Question

I added a filter by product to WooCommerce admin orders page, using:

function add_my_product_filter_to_orders() {
    global $typenow;

    if ($typenow !== 'shop_order') return;

    if (!current_user_can('administrator')) { return; }

    $args = array(
        'post_type'      => 'product',
        'posts_per_page' => -1,
        'tax_query'      => array(
            array(
                'taxonomy' => 'product_cat',
                'field'    => 'slug',
                'terms'    => 'test',
            ),
        ),
    );

    $products = get_posts($args);
    if (!$products) return;

    $selected_product = isset($_GET['my_product_filter']) ? esc_attr($_GET['my_product_filter']) : '';

    echo '<select name="my_product_filter">';
    echo '<option value="">filter by product</option>';
    
    foreach ($products as $product) {
        echo '<option value="' . $product->ID . '" ' . selected($selected_product, $product->ID, false) . '>' . get_the_title($product->ID) . '</option>';
    }

    echo '</select>';
}
add_action('restrict_manage_posts', 'add_my_product_filter_to_orders', 25);

We used pre_get_posts hook for filtration.

function filter_orders_by_my_product($query) {
    global $pagenow, $typenow, $wpdb;

    if ($pagenow !== 'edit.php' || $typenow !== 'shop_order' || empty($_GET['my_product_filter'])) {
        return;
    }

    $product_id = intval($_GET['my_product_filter']);

    $order_ids = $wpdb->get_col($wpdb->prepare("
        SELECT order_id FROM {$wpdb->prefix}woocommerce_order_items oi
        JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
        WHERE oim.meta_key = '_product_id' AND oim.meta_value = %d
    ", $product_id));

    if (!empty($order_ids)) {
        $query->query_vars['post__in'] = $order_ids;
    } else {
        $query->query_vars['post__in'] = array(0);
    }
}
add_action('pre_get_posts', 'filter_orders_by_my_product');

On start the dropdown is displayed and everything is fine, but after filtering, it disappears and $products = get_posts($args) returns an empty array after filtration. How to fix that issue?

Answer 1

Instead of making an additional query to filter orders, it's much better and lightweight to change the original orders query using posts_request hook...

It also solves the issue where your custom filter dropdown disappears.

Try the following revised code:

add_action('restrict_manage_posts', 'add_custom_product_filter_to_orders', 25);
function add_custom_product_filter_to_orders() {
    global $typenow, $pagenow;

    if ( 'edit.php' === $pagenow && 'shop_order' === $typenow ) :

    $products = get_posts( array(
        'post_type'      => 'product',
        'posts_per_page' => -1,
        'tax_query'      => array( array(
            'taxonomy' => 'product_cat',
            'field'    => 'slug',
            'terms'    => 'test',
        ) ),
    ) );

    if ( !$products ) return;

    $selected_product = isset($_GET['product_id']) ? absint($_GET['product_id']) : '';

    printf('<select name="product_id">
    <option value="">%s</option>', esc_html__('All products') );
    
    foreach ($products as $product) {
        printf('<option value="%d" %s>%s</option>', $product->ID,  selected($selected_product, $product->ID, false), get_the_title($product->ID) );
    }

    echo '</select>';
    endif;
}

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

    if ( is_admin() && 'edit.php' === $pagenow && 'shop_order' === $typenow
    && isset($_GET['product_id']) && ! empty($_GET['product_id']) ) {
        $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 = '_product_id' AND oim.meta_value = %d", absint($_GET['product_id']) );
            
        $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 child theme (or in a plugin). Tested and works.

Filtering orders for a product with results:

Filtering orders for a product without results:

The custom dropdown filter is still displayed.

Note: This code will not when High Performance Order Storage is enabled.