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

Here are some of my favorite Wordpress tools

Thank you for reading this article. I hope you found it helpful as you build your own websites and e-commerce sites. Here are some tools I use as a Wordpress developer and enthusiast that I hope you’ll also find helpful.

These are affiliate links, so if you do decide to use any of them, I’ll earn a commission and this helps me create these tutorials and make Youtube videos. But in all honesty, these are the exact tools that I use and recommend to everyone, even my friends and family.

Themes: For the last couple of years I have two go-to themes which I use for every kind of projects. Those two themes are Blocksy theme and Kadence Theme. On this site and my Youtube channel you’ll see a lot of tutorials I have made about them. If you would like to get a 10% discount for both of them then:

Code Snippets manager: WPCodeBox allows you to add code snippets to your site. Also, it allows you to build and manage your WordPress Code Snippets library 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 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 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 of 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.

Choose amount

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: 89