PHP [SOLVED]: Get all parent products with "instock" stock status in Woocommerce

PHP [SOLVED]: Get all parent products with "instock" stock status in Woocommerce

Home Forums Scripting PHP Tutorials PHP [SOLVED]: Get all parent products with "instock" stock status in Woocommerce

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #245532

    Cloudy Point
    Keymaster

    QuestionQuestion

    In WooCommerce I have the following SQL query to select parent products. I would like to select only “in-stock” product.

    This is My actual SQL query:

    $query=$db->query("select * from wp_posts 
      where post_parent='0' and post_type='product'  and post_status='publish' 
      group by ID 
      limit 10");
    

    How can I Select only “in-stock” parent products (but not all of them)?

    #245533

    Cloudy Point
    Keymaster

    Accepted AnswerAnswer

    The right way to get only parent products with a stock status like “instock”:
    :

    global $wpdb;
    
    // The SQL query
    $results = $wpdb->get_results( "
        SELECT p.*, pm.meta_value as stock_status
        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 p.post_parent = '0'
        AND pm.meta_key LIKE '_stock_status'
        AND pm.meta_value LIKE 'instock' 
        GROUP BY p.ID
    " );
    
    // Testing output (objects array)
    echo '<pre>'; print_r($results); echo '</pre>';
    

    Tested and works.


    To get only the product IDs:

    global $wpdb;
    
    // The SQL query
    $results = $wpdb->get_col( "
        SELECT 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 p.post_parent = '0'
        AND pm.meta_key LIKE '_stock_status'
        AND pm.meta_value LIKE 'instock' 
    " );
    
    // Testing output (array of IDs)
    echo '<pre>'; print_r($results); echo '</pre>';
    

    Source: https://stackoverflow.com/questions/48023115/get-all-parent-products-with-instock-stock-status-in-woocommerce
    Author: LoicTheAztec
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.