How to filter WooCommerce admin products by on sale?

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?

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.

// 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>
    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');

Here are some of my favorite WordPress tools

Thanks for reading this article! I hope it's been useful as you work on your own websites and e-commerce sites. I wanted to share some tools I use as a WordPress developer, and I think you'll find them helpful too.

Just so you know, these are affiliate links. If you decide to use any of them, I'll earn a commission. This helps me create tutorials and YouTube videos. But honestly, I genuinely use and recommend these tools to my friends and family as well. Your support keeps me creating content that benefits everyone.

Themes: Over the past few years, I've consistently relied on two primary themes for all sorts of projects: the Blocksy theme and the Kadence Theme. If you explore this website and my YouTube channel, you'll come across numerous tutorials that delve into these themes. If you're interested in obtaining a 10% discount for both of these themes, then:

Code Snippets Manager: WPCodeBox allows you to add code snippets to your site. Not only that, but it also provides you with the capability to construct and oversee your WordPress Code Snippets library right in the cloud. You can grab it with the 20% discount here (SAVE 20% Coupon: WPSH20).

Contact forms: There are hundreds of contact forms out there but Fluent Forms is the one I like the most. If you need a 20% discount then use this link (save 20% coupon is WPSH20).

Gutenberg add-ons: If I need a good Gutenberg blocks add-on then Kadence Blocks is the one I have used the most. You’ll get a 10% discount with the coupon SIMPLEHACKS here.

Website migration: While building a website you probably need a good plugin that can help you with the migration, backups, restoration, and staging sites. Well, WpVivid is the one I have used for the last couple of years. If you use this link along with the WPSH20 coupon you’ll get a 20% discount.

Woocommerce extensions: There are a bunch of Woocommerce extensions that I really like but the one that stands really out is Advanced Dynamic Pricing. Once again, you’ll get a 20% discount if you use this link here (save 20% coupon is WPSH20)

Web Hosting: If you would like to have a really fast and easy-to-use managed cloud hosting, then I recommend Verpex Hosting (see my review here). Btw, this site is hosted in Verpex.)

To see all my most up-to-date recommendations, check out this resource that I made for you!

Do you want to thank me and buy me a beer?

Every donation is entirely welcome but NEVER required. Enjoy my work for free but if you would like to thank me and buy me a beer or two then you can use this form here below.

Donation Form (#2)

Best selling plugins

Janek T.
Janek T.

I am a Wordpress enthusiast who has been making websites since 2011. In this site I am offering simple to follow tips on how to use Wordpress and Woocommerce.
If you want to be the first to be notified about the new tutorials then please subscribe to my Youtube channel here
Follow me in Twitter here

Articles: 102