0

I wrote SQL query to get items ID with 'instock' status, but don't know how to add to SELECT only from specified product category.

My code:

$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 pm.meta_key LIKE '_stock_status'
    AND pm.meta_value LIKE 'instock' 
" );
  • Any specific reason you're not using WP_Query? – WebElaine Sep 26 at 21:12
0

If you are find products by specified category. Please find below code :

$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
    LEFT JOIN {$wpdb->prefix}term_relationships ON ({$wpdb->prefix}posts.ID = {$wpdb->prefix}term_relationships.object_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 'instock' 
    AND {$wpdb->prefix}term_relationships.term_taxonomy_id IN ( 'cat 1', 'cat 2', 'cat 3' ) 
" );

Note : You need to replace "cat 1", "cat 2" and "cat 3" with your category.

0

If you absolutely have to use $wpdb :

$results = $wpdb->get_col( 
  $wpdb->prepare( "
    SELECT p.ID
    FROM $wpdb->posts as p
    INNER JOIN $wpdb->postmeta as pm ON p.ID = pm.post_id
    INNER JOIN $wpdb->term_relationships as tr ON p.ID = tr.object_id
    WHERE p.post_type = 'product'
    AND p.post_status = 'publish'
    AND pm.meta_key = '_stock_status'
    AND pm.meta_value = 'instock'
    AND tr.term_taxonomy_id = %d
  ", $cat_id)
);

If you would like to select by category name instead of ID, you have to join one more table:

$results = $wpdb->get_col( 
  $wpdb->prepare( "
    SELECT p.ID
    FROM $wpdb->posts as p
    INNER JOIN $wpdb->postmeta as pm ON p.ID = pm.post_id
    INNER JOIN $wpdb->term_relationships as tr ON tr.object_id = p.ID
    INNER JOIN $wpdb->terms as t ON t.term_id = tr.object_id
    WHERE p.post_type = 'product'
    AND p.post_status = 'publish'
    AND pm.meta_key = '_stock_status'
    AND pm.meta_value = 'instock'
    AND t.name %LIKE% %s
  ", $wpdb->esc_like($cat_name) )
);

However, the preferred method for querying products, is this:

$products = wc_get_products( array(
  'category' => array( 'shirts' ),
  'stock_status' => 'instock',
  'status' => 'publish',
));

wc_get_products and WC_Product_Query

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.