WooCommerce: Filter by purchased products on orders screen

wc-filter-products-on-orders-screen.php

<?php
/**
 * Allow sorting orders screen by purchased product
 * Modified version of https://github.com/wp-plugins/woocommerce-filter-orders-by-product/blob/master/woocommerce-filter-orders-by-product.php
 */

if ( function_exists('WC') && is_admin() ):
	add_action( 'restrict_manage_posts', 'aa_product_filter_in_order', 50 );
	add_filter( 'posts_where' , 'aa_product_filter_where' );
endif;

// Display dropdown of products in search bar for products
function aa_product_filter_in_order() {
	global $typenow, $wpdb;
	if ( 'shop_order' != $typenow ) {
		return;
	}
	$sql="SELECT ID,post_title FROM $wpdb->posts WHERE post_type = 'product' AND post_status = 'publish'";
	$all_posts = $wpdb->get_results($sql, ARRAY_A);
	$values = array();
	foreach ($all_posts as $all_post) {
		$values[$all_post['post_title']] = $all_post['ID'];
	}
	?>
	<select name="aa_order_product_filter">
		<option value=""><?php _e('All products', 'aa'); ?></option>
		<?php
		$current_v = isset($_GET['aa_order_product_filter'])? $_GET['aa_order_product_filter']:'';
		foreach ($values as $label => $value) {
			printf
			(
				'<option value="%s"%s>%s</option>',
				$value,
				$value == $current_v? ' selected="selected"':'',
				$label
			);
		}
		?>
	</select>
	<?php
}

// modify where to search for the product id in the order item metadata
function aa_product_filter_where( $where ) {
	if( is_search() ) {
		global $wpdb;
		$t_posts = $wpdb->posts;
		$t_order_items = $wpdb->prefix . "woocommerce_order_items";
		$t_order_itemmeta = $wpdb->prefix . "woocommerce_order_itemmeta";
		if ( isset( $_GET['aa_order_product_filter'] ) && !empty( $_GET['aa_order_product_filter'] ) ) {
			$product_id = (int) $_GET['aa_order_product_filter'];
			$where .= <<<MYSQL
AND
$product_id in (
		SELECT itemmeta.meta_value
		FROM $t_order_items orderitem
		LEFT JOIN $t_order_itemmeta itemmeta
			on itemmeta.order_item_id = orderitem.order_item_id
		WHERE
			orderitem.order_item_type = 'line_item'
		AND
			itemmeta.meta_key = '_product_id'
		AND
			$t_posts.ID = orderitem.order_id
)
MYSQL;
		}
	}
	return $where;
}