Show related products by attribute and products that have stock

Question

The code below displays related products based on attributes and works fine.

add_action( 'woocommerce_after_single_product_summary', 'custom_output_product_collection', 12 );
function custom_output_product_collection(){

## --- YOUR SETTINGS --- ##

$attribute = "Color"; // <== HERE define your attribute name
$limit     = "3";     // <== Number of products to be displayed
$cols      = "3";     // <== Number of columns
$orderby   = "rand";  // <== Order by argument (random order here)

## --- THE CODE --- ##

global $post, $wpdb;

// Formatting the attribute
$attribute = sanitize_title( $attribute );
$taxonomy  = 'pa_' . $attribute;

// Get the WP_Term object for the current product and the defined product attribute
$terms = wp_get_post_terms( $post->ID, $taxonomy );
$term = reset($terms);

// Get all product IDs that have  the same product attribute value (except current product ID)
$product_ids = $wpdb->get_col( "SELECT DISTINCT tr.object_id
    FROM {$wpdb->prefix}term_relationships as tr
    JOIN {$wpdb->prefix}term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
    JOIN {$wpdb->prefix}terms as t ON tt.term_id = t.term_id
    WHERE tt.taxonomy LIKE '$taxonomy' AND t.term_id = '{$term->term_id}' AND tr.object_id != '{$post->ID}'" );

// Convert array values to a coma separated string
$ids = implode( ',', $product_ids );

## --- THE OUTPUT --- ##

echo '<section class="'.$attribute.' '.$attribute.'-'.$term->slug.' products">
    <h2>'.__( "Collection", "woocommerce" ).': '.$term->name.'</h2>';

echo do_shortcode("[products ids='$ids' columns='$cols' limit='$limit' orderby='$orderby']");

echo '</section>';
}
add_action( 'woocommerce_after_single_product_summary', 'custom_output_product_collection', 12 );
function custom_output_product_collection(){

## --- YOUR SETTINGS --- ##

$attribute = "Color"; // <== HERE define your attribute name
$limit     = "3";     // <== Number of products to be displayed
$cols      = "3";     // <== Number of columns
$orderby   = "rand";  // <== Order by argument (random order here)

## --- THE CODE --- ##

global $post, $wpdb;

// Formatting the attribute
$attribute = sanitize_title( $attribute );
$taxonomy  = 'pa_' . $attribute;

// Get the WP_Term object for the current product and the defined product attribute
$terms = wp_get_post_terms( $post->ID, $taxonomy );
$term = reset($terms);

// Get all product IDs that have  the same product attribute value (except current product ID)
$product_ids = $wpdb->get_col( "SELECT DISTINCT tr.object_id
    FROM {$wpdb->prefix}term_relationships as tr
    JOIN {$wpdb->prefix}term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
    JOIN {$wpdb->prefix}terms as t ON tt.term_id = t.term_id
    WHERE tt.taxonomy LIKE '$taxonomy' AND t.term_id = '{$term->term_id}' AND tr.object_id != '{$post->ID}'" );

// Convert array values to a coma separated string
$ids = implode( ',', $product_ids );

## --- THE OUTPUT --- ##

echo '<section class="'.$attribute.' '.$attribute.'-'.$term->slug.' products">
    <h2>'.__( "Collection", "woocommerce" ).': '.$term->name.'</h2>';

echo do_shortcode("[products ids='$ids' columns='$cols' limit='$limit' orderby='$orderby']");

echo '</section>';
}

Unfortunately, products that are out of stock are also displayed.

---

I tried to add this

    FROM {$wpdb->prefix}term_relationships as tr
    JOIN {$wpdb->prefix}term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
    JOIN {$wpdb->prefix}terms as t ON tt.term_id = t.term_id
         //===========here===========//
    JOIN {$wpdb->postmeta} terms as as ON pm.meta_key = '_stock_status' AND meta_value = 'instock'
         //===========here===========//
    WHERE tt.taxonomy LIKE '$taxonomy' AND t.term_id = '{$term->term_id}' AND tr.object_id != '{$post->ID}'" );

But it didn't work.

---

Does anyone know how to make the products displayed are products with available stock only?

Any help is greatly appreciated,
Thank You

Answer 1

You were starting on the right track, you do need to look at the _stock_status in the postmeta table but you had a little bit of an error in that you haven't linked in the postmeta table and you had a typo in the line

JOIN {$wpdb->postmeta} terms as as ON pm.meta_key = '_stock_status' AND meta_value = 'instock'

So this line joins in the postmeta table to use

JOIN {$wpdb->prefix}postmeta as pm ON pm.post_id = tr.object_id

and then in the where you check the stock status

AND pm.meta_key = '_stock_status' AND meta_value = 'instock'"

Try

$product_ids = $wpdb->get_col( "SELECT DISTINCT tr.object_id
    FROM {$wpdb->prefix}term_relationships as tr
    JOIN {$wpdb->prefix}term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
    JOIN {$wpdb->prefix}terms as t ON tt.term_id = t.term_id
    JOIN {$wpdb->prefix}postmeta as pm ON pm.post_id = tr.object_id
    WHERE tt.taxonomy LIKE '$taxonomy' AND t.term_id = '{$term->term_id}' AND tr.object_id != '{$post->ID}'
    AND pm.meta_key = '_stock_status' AND meta_value = 'instock'" );

I have tested this and it works for me.