WooCommerce admin products filter by on sale

1st May 2019
/
WooCommerce, Wordpress
/
13 Comments

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.

13 Comments

Jarkko Saltiola

-
0

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 “=>”.

- Shane Rutter
  
  -
  1
  
  Thanks, I have modified the snippet!
  
Emmanuel Oriaifo

-
0

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

- Shane Rutter
  
  -
  0
  
  Glad it worked for you!
  
Fahmi

-
0

Hi,

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

- Fahmi
  
  -
  0
  
  I need some help here.
  
  How to filter by Attribute?
  
  Would you help me, please..
  
  - Shane Rutter
    
    -
    0
    
    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!
    
Pingback: WooCommerce admin products filter by image | Shane Rutter Blog
Flo

-
0

Awsome !

oppo

-
0

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;
}

- Shane Rutter
  
  -
  0
  
  Thanks, good shout with the validation checks! I have edited the code.
  
Arek

-
0

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)

- Shane Rutter
  
  -
  0
  
  Perfect, thanks for your input and glad it help you.