How to prevent `out of stock` items to show as a filter option in the sidebar (layered nav widget ) in WooCommerce?

3 Views

I have ‘hide out of stock’ checked in the settings, however when using the layered navigation widget to filter results by shoe size, it is returning products which have that size listed as an attribute, but the size is out of stock. Is there a fix to this?

WordPress version 3.9.1, WooCommerce version 2.1.7

Read More

http://www.foten.se

Post Views: 3

Related posts

Leave a Reply

3 comments

  1. He means not showing the products in the filter results. If a user filters by size M, he wants to see available size M products in the results, not All products that used to have a size M but dont anymore…

    This is something woocommerce has not solved in years! and something that anyone opening a woocommerce store should know. I am sure most of them wont use woocommerce because of this issue alone.

  2. I solve it by running the following script every night

        $dir = dirname(__FILE__);
    include_once($dir . '/../../../../wp-load.php');

    $currentCount = 0;
    $pageSize = 10;
    global $wpdb;
    $run = true;
    while ($run) {
        // select all variation of products with stock  == 0
        $sql = "SELECT * FROM `wpmf_postmeta` as pm 
LEFT JOIN wpmf_posts as p on p.ID = pm.post_id
WHERE 
meta_key = '_stock' and meta_value = 0
and p.post_parent <> 0 LIMIT $currentCount," . ($currentCount + $pageSize);
//        var_dump($sql);die;

        $res = $wpdb->get_results($sql, ARRAY_A);
        if (!$res) { //|| $currentCount > 20
            $run = false;
            break;
        }
        foreach ($res as $r) {
            $post_parent = $r['post_parent'];
            $post_excerpt = $r['post_excerpt'];
            $size = preg_replace('/[^0-9.]/', '', $post_excerpt);
//            echo($post_parent . ", $size" . '<br>');

            if ($size && $size != '') {
                // find the term ID and delete it from parent product
                $res_term = $wpdb->get_results("SELECT * FROM `wpmf_term_relationships` as tr
LEFT JOIN wpmf_terms as t on t.term_id = tr.term_taxonomy_id
where `object_id` = $post_parent and  name = $size", ARRAY_A);


//        var_dump($terms_rel);
                if ($res_term) {

                    $query = "
                    DELETE FROM wpmf_term_relationships
                    WHERE term_taxonomy_id = " . $res_term[0]['term_id'] . "
                    AND object_id = " . $post_parent . "
                ";
                    $res_query = $wpdb->query(
                        $query);

                    echo($post_parent . ", $size, $res_query" . '<br>');

                }
            }

            $currentCount++;
        }
    }

    wp_cache_flush();

    echo 'done! ' . $currentCount;

    The problem this script fixes is: the sidebar filters the product by attribute but the stock is manage by product variation (child post).

    The DB doesn’t have a way to link the 2 fields, therefore it is not possible to create a query that filters attributes that has a matching variation with stock == 0.

    Therefore this script solve the problem by deleting products attributes that have stock == 0

    here is a opposite script to set an attribute for a product with stock > 0 and a missing attr:

        $dir = dirname(__FILE__);
    include_once($dir . '/../../../../wp-load.php');

    $currentCount = 0;
    $pageSize = 10;
    global $wpdb;
    $run = true;
    while ($run) {
        // select all varaition of a prod with stock > 0
        $sql = "SELECT * FROM `wpmf_postmeta` as pm 
LEFT JOIN wpmf_posts as p on p.ID = pm.post_id
WHERE 
meta_key = '_stock' and meta_value <> 0
and p.post_parent <> 0 LIMIT $currentCount," . ($currentCount + $pageSize);
//        var_dump($sql);die;

        $res = $wpdb->get_results($sql, ARRAY_A);
        if (!$res) { //|| $currentCount > 20
            $run = false;
            break;
        }
        foreach ($res as $r) {
            $post_parent = $r['post_parent'];
            $post_excerpt = $r['post_excerpt'];
            $size = preg_replace('/[^0-9.]/', '', $post_excerpt);
            if ($size && $size != '') {
                // find the relevant term
                $res_parent = $wpdb->get_results("SELECT * FROM `wpmf_term_relationships` as tr
LEFT JOIN wpmf_terms as t on t.term_id = tr.term_taxonomy_id
where `object_id` = $post_parent and  name = $size", ARRAY_A);


//        var_dump($terms_rel);
                // if term is missing, create it
                if (!$res_parent) {
                    wp_set_object_terms($post_parent, $size, 'pa_size', true);
                    echo($post_parent . ", $size" . '<br>');

                }



                $currentCount++;
            }
        }
    }
    wp_cache_flush();

    echo 'done! ' . $currentCount;

    NOTE:

    1. this is not a proper solution to the problem – a solution should be at the design level, i.e. finding a way to link the 2 fields with an SQL query, this solution is a temp by-pass until a real solution is available

    2. I didn’t fully tested this code, I will update the answer if necessary in the future

    3. I’m not fully familiar with the DB structure of wordpress or woocommerce, and the result of this code might change depending on different plugins, using this code is at your own risk