Get all parent products with "instock" stock status in Woocommerce

Question

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)?

update your question add a proper data sample and the expected result — ScaisEdge

LoicTheAztec LoicTheAztec · Accepted Answer · 2017-12-29T14:27:53

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>';

Get all parent products with "instock" stock status in Woocommerce

1 Answers