WooCommerce admin products filter by on sale

Written by

I needed the ability to filter the WooCommerce products list within admin by products which where on sale. This was to allow the client to easily find products they have put on offer within certain categories. The below code achieves this.

It does need some work, as it currently ignores the on sale start and end dates, however you should be able to easily modify it for this purpose.

/*
 * Woocommerce Filter by on sale
 */
function custom_woocommerce_filter_by_onsale($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 selected=”selected” value=”1″>On sale</option>
			<option selected=”selected” value=”2″>Not on sale</option>
		</select>
	‘;
	
	return $output;
}
add_action(‘woocommerce_product_filters’, ‘custom_woocommerce_filter_by_onsale’);



/*
 * Woocommerce Filter by on sale where statement
 */
function custom_woocommerce_filter_by_onsale_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[‘post_type’] != “product” || !$selected) {
        return $where;
    }

	$productsIDs = [];
	if ($selected == 1) {
		$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);
	} elseif ($selected == 2) {
		$querystr = ‘
			SELECT p.ID, p.post_parent
			FROM ‘ . $wpdb->posts . ‘ p
			WHERE p.ID NOT 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);
	}
	
	$where .= ‘ AND ‘ . $wpdb->posts . ‘.ID IN (‘ . implode(“,”, $productsIDs) . ‘) ‘;
	
	return $where;
}
add_filter(‘posts_where’ , ‘custom_woocommerce_filter_by_onsale_where_statement’);

* Woocommerce Filter by on sale

function custom_woocommerce_filter_by_onsale($output) {

global $wp_query;

$selected = filter_input(INPUT_GET, ‘product_sale’, FILTER_VALIDATE_INT);

if ($selected == false) {

$selected = 0;

}

$output .= ‘

<option value=””>Filter by sale</option>

</select>

‘;

return $output;

}

add_action(‘woocommerce_product_filters’, ‘custom_woocommerce_filter_by_onsale’);

* Woocommerce Filter by on sale where statement

function custom_woocommerce_filter_by_onsale_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[‘post_type’] != “product” || !$selected) {

return $where;

}

$productsIDs = [];

if ($selected == 1) {

$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);

} elseif ($selected == 2) {

$querystr = ‘

SELECT p.ID, p.post_parent

FROM ‘ . $wpdb–>posts . ‘ p

WHERE p.ID NOT 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);

}

$where .= ‘ AND ‘ . $wpdb–>posts . ‘.ID IN (‘ . implode(“,”, $productsIDs) . ‘) ‘;

return $where;

}

add_filter(‘posts_where’ , ‘custom_woocommerce_filter_by_onsale_where_statement’);

This code is provided as it and may require changes to work for your needs.

Comments

19 responses to “WooCommerce admin products filter by on sale”

15th August 2019

Jarkko Saltiola

Thank’s for this, helped a bit.

There’s a little typo though. Line 32 gives “PHP Parse error: syntax error, unexpected ‘=’”.

So just change “= >” to “=>”.

Reply
1. 15th August 2019
  
  Shane Rutter
  
  Thanks, I have modified the snippet!
  
  Reply
2nd June 2020

Emmanuel Oriaifo

Great.

It Worked, perfectly

I was able to sort the products based on onsale products, giving me the opportunity to bulk edit only onsale products

Thank you

Reply
1. 6th June 2020
  
  Shane Rutter
  
  Glad it worked for you!
  
  Reply
16th December 2020

Fahmi

Hi,

Thanks a lot !
It works !
You safe my time !
Love you !

Reply
1. 17th December 2020
  
  Fahmi
  
  I need some help here.
  
  How to filter by Attribute?
  
  Would you help me, please..
  
  Reply
  1. 17th December 2020
    
    Shane Rutter
    
    I dont have any code examples to hand, but I do have another filter code example I have just posted. Maybe one of these may get you pointed in the right direction?
    
    I think you should be able to use the filter by image example, and change the attribute values / search paramamters. As the filter by image example is based around product attributes / meta data values.
    
    https://shanerutter.co.uk/woocommerce-admin-products-filter-by-image/
    
    Hope that helps get your in the right direction!
    
    Reply
17th December 2020

WooCommerce admin products filter by image | Shane Rutter Blog

[…] Why not take a look how to filter by on sale items? […]

Reply
4th February 2021

Flo

Awsome !

Reply
15th February 2021

oppo

1) fix
$selected = filter_input(INPUT_GET, ‘product_sale’, FILTER_VALIDATE_INT);
if ($selected == false) {
$selected = 0;
}
$output .= ‘

Filter by sale
On sale
Not on sale

‘;

2) fix
$selected = filter_input(INPUT_GET, ‘product_sale’, FILTER_VALIDATE_INT);
if (!is_admin() || (isset($_GET[‘product’]) && $_GET[‘post_type’] != “product”) || !$selected ) {
return $where;
}

Reply
1. 15th February 2021
  
  Shane Rutter
  
  Thanks, good shout with the validation checks! I have edited the code.
  
  Reply
  1. 27th November 2021
    
    pacmanito
    
    You forgot to add (isset($_GET[‘product’]) condition to your code, otherwise PHP warning is thrown. Another way is to use get_query_var(‘post_type’) instead of $_GET[‘product’]
    
    Reply
    1. 4th December 2021
      
      Shane Rutter
      
      Thanks I have updated the code 🙂
      
      Reply
13th July 2021

Arek

Thank’s!

Fix for variations:

change:
1) SELECT p.ID to: SELECT p.ID, p.post_parent (2 places)
2) return $n->ID; to return $n->post_parent > 0 ? $n->post_parent : $n->ID; (2 places)

Reply
1. 19th July 2021
  
  Shane Rutter
  
  Perfect, thanks for your input and glad it help you.
  
  Reply
16th November 2021

Radim

Many thanks for this, it helped a lot.
A little fix:
$output .= ‘

Filter by sale
On sale
Not on sale

‘;

Reply
16th November 2021

Radim

A little fix:
option value=”1″ ‘ . (($selected === 1) ? ‘selected=”selected”‘ : ”) . ‘
option value=”2″ ‘ . (($selected === 2) ? ‘selected=”selected”‘ : ”) . ‘

Reply
27th November 2021

pacmanito

It’s rather strange – when I check the $querystr query for “not on sale” directly via DB the result does not contain sale items. But when the snippet is run in WP it returns sale products and checking via Query Monitor I can see that the part of query added via snippet (AND wp.ID NOT IN (***)) contains IDs of sale product. I can’t get how it happens. And it’s only for published products, everything works as expected for draft products.
At the same time, I was able to get proper results via changing $querystr query for “not on sale” products to the same as for “on sale” (without NOT) and moving $where part inside if clause and swapping wp.ID IN for wp.ID NOT IN for “not on sale” products.

Reply
1. 4th December 2021
  
  Shane Rutter
  
  Is it possible another plugin is adding to the query causing the results to change? I did have an issue with another plugin tampering with query results at one point.
  
  Reply