[FIXED] Show related products by attribute and products that have stock

Issue

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

Solution

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.

Answered By - jtowell