Woocommerce - Showing only products in stock

Question

a client want to show a total of the amount of products they have in their shop, I have used this code


$numposts = (int) $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts WHERE post_type = 'product' AND post_status = 'publish'");

Which works fine, however it shows the total number of published products and I want it to only show where stock is at 1 or greater, so basically it only shows the total number of products that are in actually in stock

To count all in stocks product you must have to join other table wp_postmeta with field _sale_price which is greator than 0 — Rahil Wazir
Sorry you will need to talk to me like a child, how do I do that? — user3593556
Try this SELECT COUNT(*) FROM $wpdb->posts p INNER JOIN $wpdb->postmeta pm ON p.id = pm.post_id WHERE p.post_type = 'product' AND p.post_status = 'publish' AND pm.meta_key = "_sale_price" AND pm.meta_value > 0 — Rahil Wazir
No that failed, it referred to _sale_price as the error, I am not sure this is right, I don't want anything to do with sale price I want any product with a stock quantity of 1 or more not sale price — user3593556

doublesharp doublesharp · Accepted Answer · 2014-05-01T18:22:04

Try joining to the post_meta table using the _stock_status meta_key where the meta_value is 'instock'. Caching the data is recommended since you don't want to run this on every request but will need to balance an appropriate amount of time to cache the data (since sales within the cache period won't be reflected in the total number of instock items). Only works if you are using a cache (which is highly recommended with WooCommerce due to the number of queries).

global $wpdb;
// cache key
$key = 'in_stock_products';
// we get back 'false' if there is nothing in the cache, otherwise 0+
$in_stock_products = wp_cache_get( $key );
// run the query if we didn't get it from the cache
if ( false === $in_stock_products ){
    // create the SQL query (HEREDOC format)
    $sql_query = <<<SQL
    SELECT COUNT(p.ID) AS in_stock_products
    FROM {$wpdb->posts} p
    JOIN {$wpdb->postmeta} pm 
        ON p.ID = pm.post_id 
        AND pm.meta_key = '_stock_status' 
        AND pm.meta_value = 'instock'
    WHERE p.post_type = 'product' AND p.post_status = 'publish'
SQL;
    // query the database
    $in_stock_products = (int) $wpdb->get_var( $sql_query );
    // cache the results, choosing an appropriate amount of time to cache results
    $cache_ttl = 0; // 0 is "as long as possible", otherwise cache time in seconds
    wp_cache_add( $key, $in_stock_products, $cache_ttl ); // cache as long as possible
}
// $in_stock_products now has the value either from the cache or the database query.
echo "There are $in_stock_products in stock";

Woocommerce - Showing only products in stock

1 Answers