WooCommerce: Get Products On Sale Via PHP

We’ve already seen how to programmatically assign the “Sale” category to WooCommerce products on sale. That snippet actually uses the “shortcut” function we’re talking about today, wc_get_product_ids_on_sale().

This is a super helpful way to get something without complex queries or foreach loops. So I’d like to study its codebase, see what it returns, and finally test it and print something via a shortcode.


WooCommerce PHP Function: wc_get_product_ids_on_sale()

In plain English, this function retrieves an array containing IDs of all products currently on sale in your WooCommerce store. “On sale” means those products that have a sale price lower than their regular price.

wc_get_product_ids_on_sale() usage

wc_get_product_ids_on_sale() doesn’t require any arguments. You can call this function within your theme’s template files or custom plugins to target products with active sales.

If you simply:

print_r( wc_get_product_ids_on_sale() );

You’ll print the array on the frontend:

You can of course assign the array to a variable, run some PHP stuff such as count() or foreach(), and do any manipulation you like.

wc_get_product_ids_on_sale() codebase

Let’s look at the actual function code. By searching for the function name, we get this:

 * Function that returns an array containing the IDs of the products that are on sale.
 * @since 2.0
 * @return array
function wc_get_product_ids_on_sale() {
	// Load from cache.
	$product_ids_on_sale = get_transient( 'wc_products_onsale' );

	// Valid cache found.
	if ( false !== $product_ids_on_sale ) {
		return $product_ids_on_sale;

	$data_store          = WC_Data_Store::load( 'product' );
	$on_sale_products    = $data_store->get_on_sale_products();
	$product_ids_on_sale = wp_parse_id_list( array_merge( wp_list_pluck( $on_sale_products, 'id' ), array_diff( wp_list_pluck( $on_sale_products, 'parent_id' ), array( 0 ) ) ) );

	set_transient( 'wc_products_onsale', $product_ids_on_sale, DAY_IN_SECONDS * 30 );

	return $product_ids_on_sale;

We can immediately see that the function is cached, so it’s super fast. The transient lasts for 30 days, after which the products on sale are recalculated.

The ‘wc_products_onsale‘ transient is also deleted once there is a change in regard to scheduled sales, just so you know. In this way the function is recalculated at the next call.

But what matters is the $data_store->get_on_sale_products() function, so we need to study that one as well:

 * Returns an array of on sale products, as an array of objects with an
 * ID and parent_id present. Example: $return[0]->id, $return[0]->parent_id.
 * @return array
 * @since 3.0.0
public function get_on_sale_products() {
	global $wpdb;

	$exclude_term_ids            = array();
	$outofstock_join             = '';
	$outofstock_where            = '';
	$non_published_where         = '';
	$product_visibility_term_ids = wc_get_product_visibility_term_ids();

	if ( 'yes' === get_option( 'woocommerce_hide_out_of_stock_items' ) && $product_visibility_term_ids['outofstock'] ) {
		$exclude_term_ids[] = $product_visibility_term_ids['outofstock'];

	if ( count( $exclude_term_ids ) ) {
		$outofstock_join  = " LEFT JOIN ( SELECT object_id FROM {$wpdb->term_relationships} WHERE term_taxonomy_id IN ( " . implode( ',', array_map( 'absint', $exclude_term_ids ) ) . ' ) ) AS exclude_join ON exclude_join.object_id = id';
		$outofstock_where = ' AND exclude_join.object_id IS NULL';

	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	return $wpdb->get_results(
		SELECT posts.ID as id, posts.post_parent as parent_id
		FROM {$wpdb->posts} AS posts
		INNER JOIN {$wpdb->wc_product_meta_lookup} AS lookup ON posts.ID = lookup.product_id
		WHERE posts.post_type IN ( 'product', 'product_variation' )
		AND posts.post_status = 'publish'
		AND lookup.onsale = 1
		AND posts.post_parent NOT IN (
			SELECT ID FROM `$wpdb->posts` as posts
			WHERE posts.post_type = 'product'
			AND posts.post_parent = 0
			AND posts.post_status != 'publish'
		GROUP BY posts.ID
	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

At the end of the day, our wc_get_product_ids_on_sale is a SQL statement. Important things to note:

  • On sale items that are out of stock are excluded from the array in case you checked the “Hide out of stock items from the catalog” checkbox in the WooCommerce > Products > Inventory settings. Otherwise, the list will contain out of stock products as well.
  • The returned IDs will be those of ‘product’ and ‘product_variation’ post type, which means the list of arrays will also contain variation IDs!
  • The SQL only retrieves “published” products and product variations

Overall, this is a neat WooCommerce function that you can study to implement your own custom code and functionalities.

At the end of the day, knowing these “shortcuts” can help you save time while coding – because WooCommerce is already doing the work for you!

Now that we learned about wc_get_product_ids_on_sale, let’s do something with it! Here is an example.

PHP Snippet: Count WooCommerce Products On Sale (shortcode)

Remember we said that wc_get_product_ids_on_sale also contains the IDs of single variations? Well, let’s find a way to remove those before counting!

 * @snippet       Count Products On Sale | WooCommerce
 * @how-to        Get CustomizeWoo.com FREE
 * @author        Rodolfo Melogli
 * @compatible    WooCommerce 8
 * @community     https://businessbloomer.com/club/

add_shortcode( 'how_many_on_sale', 'bbloomer_how_many_on_sale' );

function bbloomer_how_many_on_sale() {
   $on_sale = wc_get_product_ids_on_sale();
   foreach ( $on_sale as $key => $id ) {
      if ( get_post_type( $id ) === 'product_variation' ) unset( $on_sale[$key] );
   return count( $on_sale ) . ' products are currently on sale!';

This will output:

Where to add custom code?

You should place custom PHP in functions.php and custom CSS in style.css of your child theme: where to place WooCommerce customization?

This code still works, unless you report otherwise. To exclude conflicts, temporarily switch to the Storefront theme, disable all plugins except WooCommerce, and test the snippet again: WooCommerce troubleshooting 101

Related content

  • WooCommerce: Display Total Discount / Savings @ Cart & Checkout
    If you love Ecommerce as much as I do, and are passionate about Sales Conversion Rate and reducing Shopping Cart Abandonment, today’s snippet will come in handy. Besides, this is officially the first guest blog on Business Bloomer (have ideas? Send me your proposal here)… so let me officially introduce you to today’s author: Jamie […]
  • WooCommerce: Display % Discount @ Shop Page
    Default WooCommerce shows a “Sale” badge if the item is on sale – but what about showing the exact sale percentage instead? I implemented this for one of my freelance clients so here you go with the easy-peasy solution. Enjoy!
  • WooCommerce: “Sale” Category (Automatic)
    You can use a shortcode or block in order to display the WooCommerce products on sale. However, what if you wanted a proper “product category” called “Sale” – and where you didn’t need to manually assign this category to each product? Basically, how do we display all the discounted products in a custom category called […]
  • WooCommerce: Display Prices as “Was $$$ – Now $$$ – Save $$$”
    Many retailers use this price tag strategy quite successfully. And displaying the amount of savings can increase your ecommerce store conversion rate as well ๐Ÿ™‚ So, turning simple product default pricing from “$30 $20″ to “Was $30 – Now $20 – Save $10” is quite easy. With a little CSS you can also style the […]
  • WooCommerce: Set / Override Product Price Programmatically
    There are times when the edit product page settings are not enough. Yes, you usually set regular and sale price via the price fields under “Product Data”; however sometimes you may have to override those prices via code, because you’re running a special promotion, you don’t want to manually change thousands of prices or maybe […]

Rodolfo Melogli

Business Bloomer Founder

Author, WooCommerce expert and WordCamp speaker, Rodolfo has worked as an independent WooCommerce freelancer since 2011. His goal is to help entrepreneurs and developers overcome their WooCommerce nightmares. Rodolfo loves travelling, chasing tennis & soccer balls and, of course, wood fired oven pizza. Follow @rmelogli

2 thoughts on “WooCommerce: Get Products On Sale Via PHP

  1. it was amazing tip men – TY Rodolfo Melogli

Questions? Feedback? Customization? Leave your comment now!

If you are writing code, please wrap it like so: [php]code_here[/php]. Failure to complying with this, as well as going off topic or not using the English language will result in comment disapproval. You should expect a reply in about 2 weeks - this is a popular blog but I need to get paid work done first. Please consider joining the Business Bloomer Club to get quick WooCommerce support. Thank you!

Your email address will not be published. Required fields are marked *