Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More

Get lowest price bigger than 0 of a simple “instock” product for a specific product category in Woocommerce

General Tech Bugs & Fixes
Max. 2000 characters
Replies

usr_profile.png
Peter Jarvis

User

( 8 months ago )

 

In WooCommerce I am trying to get the lowest price of an available (instock) simple product when the price is bigger than 0, for a defined product category…

I have this code that displays the lowest price:

function wpq_get_min_price_per_product_cat($term_id){    
    global $wpdb;

    $sql = "
    SELECT  MIN( meta_value+0 ) as minprice
    FROM {$wpdb->posts} 
    INNER JOIN {$wpdb->term_relationships} ON ({$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id)
    INNER JOIN {$wpdb->postmeta} ON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id) 
    WHERE  
      ( {$wpdb->term_relationships}.term_taxonomy_id IN (%d) ) 
    AND {$wpdb->posts}.post_type = 'product'  
    AND {$wpdb->posts}.post_status = 'publish' 
    AND {$wpdb->postmeta}.meta_key = '_price'
    ";

    return $wpdb->get_var($wpdb->prepare($sql, $term_id));
}

But how to target "in stock" products with a price bigger than 0?

usr_profile.png
Brian Burl

User

( 8 months ago )

 

The following changed SQL query will get the lowest price, for a defined product category term id, for a product price bigger than 0 and for an "in stock" product:

function wpq_get_min_price_per_product_cat( $term_id ){
    global $wpdb;

    return $wpdb->get_var( $wpdb->prepare("
        SELECT  MIN(pm.meta_value+0)
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}postmeta as pm
            ON p.ID = pm.post_id
        INNER JOIN {$wpdb->prefix}postmeta as pm2
            ON p.ID = pm2.post_id
        INNER JOIN {$wpdb->prefix}term_relationships as tr
            ON p.ID = tr.object_id
        INNER JOIN {$wpdb->prefix}term_taxonomy as tt
            ON tr.term_taxonomy_id = tt.term_taxonomy_id
        WHERE tt.taxonomy = 'product_cat'
        AND tt.term_id = %d
        AND p.post_type = 'product'
        AND p.post_status = 'publish'
        AND pm.meta_key = '_price'
        AND pm.meta_value > 0
        AND pm2.meta_key = '_stock_status'
        AND pm2.meta_value = 'instock'
    ", $term_id) );
}

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

what's your interest


forum_ban8_5d8c5fd7cf6f7.gif