Although, Woocommerce allows you to filter your products by category, by product type and by stock status, it doesn’t allow you to filter out on sale products. Therefore, this snippet here below allows you to filter WooCommerce admin products by on sale. Here is the end result.
How to filter WooCommerce admin products by on sale?
Now, there are two ways to insert this snippet here below.
First option: If you are using a child theme then go to Appearance >> Editor and open your childe theme’s functions.php file and paste this code inside it. Then save and see whether it works as promised.
Preferred method: I prefer not to mess with my theme’s functions.php file and therefore I suggest you to install a Code Snippets plugin. Now go to Snippets >> Add new and give it a title. Next, paste this code inside the Code box, choose “Only run in administration area”. If this is done, press on Save changes and activate button.
function wpsh_onsale_filter($output) {
global $wp_query;
$selected = filter_input(INPUT_GET, 'product_sale', FILTER_VALIDATE_INT);
if ($selected == false) {
$selected = 0;
}
$output .= '
<select id="dropdown_product_sale" name="product_sale">
<option value="">Filter by sale</option>
<option value="1" ' . (($selected === 1) ? 'selected="selected"' : '') . '>On sale</option>
<option value="2" ' . (($selected === 2) ? 'selected="selected"' : '') . '>Not on sale</option>
// Filter Woocommerce admin product by on sale
function wpsh_onsale_filter($output) {
global $wp_query;
$selected = filter_input(INPUT_GET, 'product_sale', FILTER_VALIDATE_INT);
if ($selected == false) {
$selected = 0;
}
$output .= '
<select id="dropdown_product_sale" name="product_sale">
<option value="">Filter by sale</option>
<option value="1" ' . (($selected === 1) ? 'selected="selected"' : '') . '>On sale</option>
<option value="2" ' . (($selected === 2) ? 'selected="selected"' : '') . '>Not on sale</option>
</select>
';
return $output;
}
add_action('woocommerce_product_filters', 'wpsh_onsale_filter');
// Woocommerce Filter by on sale where statement
function wpsh_onsale_filter_where_statement($where) {
global $wp_query, $wpdb;
// Get selected value
$selected = filter_input(INPUT_GET, 'product_sale', FILTER_VALIDATE_INT);
// Only trigger if required
if (!is_admin() || get_query_var('post_type') != "product" || !$selected) {
return $where;
}
$querystr = '
SELECT p.ID, p.post_parent
FROM ' . $wpdb->posts . ' p
WHERE p.ID IN (
SELECT post_id FROM ' . $wpdb->postmeta . ' pm WHERE pm.meta_key = "_sale_price" AND pm.meta_value > ''
)
';
$pageposts = $wpdb->get_results($querystr, OBJECT);
$productsIDs = array_map(function($n){
return $n->post_parent > 0 ? $n->post_parent : $n->ID;
}, $pageposts);
if ($selected == 1) {
$where .= ' AND ' . $wpdb->posts . '.ID IN (' . implode(",", $productsIDs) . ') ';
}
elseif ($selected == 2) {
$where .= ' AND ' . $wpdb->posts . '.ID NOT IN (' . implode(",", $productsIDs) . ') ';
}
return $where;
}
add_filter('posts_where' , 'wpsh_onsale_filter_where_statement');
