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('';
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.