Get and display products On Sale count in WooCommerce

Question

By doing an SQL query (does not seem to work properly) which counts how many products that are on sale (it counts how many products that have discounted prices), my goal is to display a message on the main archive (shop) using a hook. The message should contain the count (how many products that are on sale).

Desired example output: "Looking for a great deal? We currently have XX products on sale!"

Any ideas or insight as to why this is not working? Here's my code:

function get_onsale_products_count() {
    global $wpdb;

    // SQL query for counting products that are on sale
    $result = $wpdb->get_col( "
        SELECT COUNT(p.ID)
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
        WHERE p.post_type LIKE '%product%'
        AND p.post_status LIKE 'publish'
        AND pm.meta_key LIKE '_stock_status'
        AND pm.meta_value LIKE '_on_sale'
    " );

    return reset($result);
}

add_action('woocommerce_before_single_product_summary', 'get_onsale_products_count');
$count = get_onsale_products_count();
$great_deal = sprintf( __( 'Want to make a good deal? We\'ve got %s products on sale!' ), $count );
echo '<span class="great-deal">'.$great_deal.'</span>';

*"does not seem to work properly"* and *"this is not working"* are not clear problem descriptions. Please provide sample data, the expected output and what you get instead. — trincot, Dec 24 '18 at 21:19
This thread should be reopened: This question is clear for people that is used to woocommerce and the OP's code contains anything needed to get the evidence of the problem. — LoicTheAztec, Dec 27 '18 at 16:44

LoicTheAztec · Accepted Answer · 2018-12-24T21:03:24.517

Stock status has nothing to do with "on sale" products and _on_sale meta value doesn't exist, so that's why your SQL query doesn't work.

On sale products have a defined "sale price", So try instead:

function get_onsale_products_count() {
    global $wpdb;

    // SQL query for counting products that are on sale
    return $wpdb->get_var( "
        SELECT COUNT(p.ID)
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
        WHERE p.post_type LIKE '%product%'
        AND p.post_status = 'publish'
        AND pm.meta_key = '_sale_price'
        AND pm.meta_value != ''
    " );
}

add_action( 'woocommerce_before_single_product_summary', 'display_onsale_products_count_message', 3 );
function display_onsale_products_count_message(){
    echo '<span class="great-deal">' . sprintf( __( "Want to make a good deal? We've got %s products on sale!" ), 
    get_onsale_products_count() ) . '</span>';
}

Code goes in function.php file of your active child theme (or active theme). Tested and works.

Hi LoicTheAztec, sorry - but I thought that I had to count the products that way and I was trying to find the correct way of getting the onsale. Thank you for your answer, it works. — , Dec 25 '18 at 05:57

Get and display products On Sale count in WooCommerce

1 Answers1