"Layered navigation block" module : don't display out of stock products for choosen attributes

[r@ph]

Problem: for those who don't permit out of stock purchase, choosing eg. size XL in the filters displays products without XL size available.

 

Solution:

Based on stock 1.6.0.14

 

Open modules/blocklayered/blocklayered.php

 

Find: (line ~1840)

$query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
FROM `'._DB_PREFIX_.'product_attribute_combination` pac
LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
ON (pa.`id_product_attribute` = pac.`id_product_attribute`)'.
Shop::addSqlAssociation('product_attribute', 'pa').'
WHERE '.implode(' OR ', $sub_query).') ';

Replace with:

$query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
FROM `'._DB_PREFIX_.'product_attribute_combination` pac
LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
ON (pa.`id_product_attribute` = pac.`id_product_attribute`)'.
Shop::addSqlAssociation('product_attribute', 'pa').'
JOIN `'._DB_PREFIX_.'stock_available` sa
ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`quantity`>0)
WHERE '.implode(' OR ', $sub_query).') ';

[garyjj127]

Thanks for posting. This works perfectly! My question is, this has been an issue with this module for many versions. Why don't they include this fix in the updates? 

[musicmaster]

 Why don't they include this fix in the updates? 

The problem is with multi-attribute combinations. 

[mr.pz]

Dear r@ph,

 

THANK YOU VERY MUCH!!!!!!

Your solution works perfectly with prestashop 1.6.1.1

 

[edouardd]

unfortunately, not working for me, if anyone have another solution.

 

prestashop 1.6.1.1

Edited September 30, 2015 by edouardd (see edit history)

[zanpo]

Thanks r@ph, you're a genius, I've been having problems with filters on products sizes combined with in stock, and haven't found a solution until now. It works on 1.6.1.1, but the only problem is that the number of products for each filter doesn't change when I apply a filter. For example if I have 100 products in stock, and only 30 products size S, it displays 100 products for each of the sizes.

[rufusqueen]

I have problem with layered navigation block also

WHenever I tried to filter / sort by / next page .. all it does is repeat again the first 12 products and not filtering / showing the rest of products. With the word "loading..." at the top of the category page.

 

Been searching high and low how to fix this problem for the past 3 weeks.

ANyone can help?

 

CHeck out my website category http://www.iwantmylulu.com/en/30-baby-lulu

The products attributes has color and size and age.. so i want my customer to be able to browser the products based on the attributes filtering. 

[Virgy]

Great solution! Elegant and simple Thank you very much for your post.  

[cricket-hockey]

Great fix, but is there any way to change the number of products available in brackets to the correct number?

 

For example, before I added this fix, I had 7 pairs of shoes in a size 12, but when when you clicked on size 12, 26 pairs of shoes would be shown.

 

Now only 7 come up which is correct, but the filter is still showing this on the left hand side:

•  12 (26)

[ulischott]

cricket-hockey is absolutely right! Is there any way to change it?

If anyone who is good at reading code could post the lines, where an change has to be implemented... That would be great!

[wiseprt]

Anyone?

[Alberto Fernández]

This fix works fine to me for products with only 1 attribute (color). Would be great to show at the same time the corresponding cover images in product list page.

[Alberto Fernández]

 

Problem: for those who don't permit out of stock purchase, choosing eg. size XL in the filters displays products without XL size available.

 

Solution:

Based on stock 1.6.0.14

 

Open modules/blocklayered/blocklayered.php

 

Find: (line ~1840)

$query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
FROM `'._DB_PREFIX_.'product_attribute_combination` pac
LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
ON (pa.`id_product_attribute` = pac.`id_product_attribute`)'.
Shop::addSqlAssociation('product_attribute', 'pa').'
WHERE '.implode(' OR ', $sub_query).') ';

Replace with:

$query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
FROM `'._DB_PREFIX_.'product_attribute_combination` pac
LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
ON (pa.`id_product_attribute` = pac.`id_product_attribute`)'.
Shop::addSqlAssociation('product_attribute', 'pa').'
JOIN `'._DB_PREFIX_.'stock_available` sa
ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`quantity`>0)
WHERE '.implode(' OR ', $sub_query).') ';

 

Unfortunately I just noticed that it doesn't work if you have more than 1 attribute (PS 1.6.1.4):

 

Price filter doesn't work properly (in my case, a price range of 20-40 € is returning a products with price out of that range).

 

color filter doesn't work, returns 0 articles found per color selected.

 

number of items show is wrong.

[AdrianaM]

Unfortunately I just noticed that it doesn't work if you have more than 1 attribute (PS 1.6.1.4):

 

Price filter doesn't work properly (in my case, a price range of 20-40 € is returning a products with price out of that range).

 

color filter doesn't work, returns 0 articles found per color selected.

 

number of items show is wrong.

I have the same problem. This solution work great with 'size' my multi combination attribute, but at the same time other 'color' attribute filter stopped to work - showing 0 items. Maybe somebody found  solution for that - it's important for me so it work with both attributes.

[AdrianaM]

Anyone? I really appriciate help, because I don't have any ideas how to fix it. I have 1.6.0.14 presta version. First size attribute filter didn't worked. After some fixes from this thread size attribute filter work perfect, but other: color attribute filter stopped to show results.

Web address: http://nikbutik.pl/ 

 

I'll be grateful for help!

[PrestaShark]

1.6.1.5 works good (module version 2.2.0) but need to disable option "Show the number of matching products" because it show wrong count. Any ideas how to fix it?

[chenler19]

I´m still try to find the solution 

[ssupreme]

Find line: public function indexAttribute($id_product = null)  /* around 861 */

and add line as follows after first INNER JOIN

INNER JOIN '._DB_PREFIX_.'stock_available sa ON (sa.id_product_attribute = pac.id_product_attribute AND sa.quantity > 0)

so it ends up as this:

Db::getInstance()->execute('
	INSERT INTO `'._DB_PREFIX_.'layered_product_attribute` (`id_attribute`, `id_product`, `id_attribute_group`, `id_shop`)
	SELECT pac.id_attribute, pa.id_product, ag.id_attribute_group, product_attribute_shop.`id_shop`
	FROM '._DB_PREFIX_.'product_attribute pa'.
	Shop::addSqlAssociation('product_attribute', 'pa').'
	INNER JOIN '._DB_PREFIX_.'product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute
	INNER JOIN '._DB_PREFIX_.'stock_available sa ON (sa.id_product_attribute = pac.id_product_attribute AND sa.quantity > 0)
	INNER JOIN '._DB_PREFIX_.'attribute a ON (a.id_attribute = pac.id_attribute)
	INNER JOIN '._DB_PREFIX_.'attribute_group ag ON ag.id_attribute_group = a.id_attribute_group
	'.(is_null($id_product) ? '' : 'AND pa.id_product = '.(int)$id_product).'
	GROUP BY a.id_attribute, pa.id_product , product_attribute_shop.`id_shop`'
);

It almost helped me, the last problem is categories and manufacture.
First case: I have 5 items before further filtering. When I pick one size seperatly, everything is fine I got 2,3,1,4,5 items, if I pick two or more sizes at once (for few items available), even if it is available for same items only, manufactures and categories count to full 5 items.
Second case: when I have attribute size for 3 items but 1 item isn't available. I pick a size it shows two items (count for size is also 2 items), but 3 manufactures appear.

[andyashler]

Works on 1.6.1.7 but not on 1.6.1.10

[system_error86]

Hello,

has anyone found the solution for more than one attributes?? Thanks a lot!

[marco Cape Town]

Where are we on this subject ?

Its a real problem making buyers unhappy

Can we expect an official fix from Prestashop ?

[panoskou]

for me the first solution works perfect (1.6.1.6), but I have choose to appear in checkbox the results but it isn't working.
Ant ideas?

[Abeunger]

Hi all

I can see that this might solve my problem.

But I am quite new and can anyone tell my how I make changes to the file?

I have found the file and the correct line, but I cannot change it.

Can anyone help a beginner ?

[dmconcept]

Hello,

 

Thank you for this post on this forum because it help me to find the solution.

I post here the result:
 

Find: (line ~1840) and replace all the block case by the following code.

This will take the filter quantity related to the stock for the filter attribute:

case 'id_attribute_group':
    $sub_queries = array();


    foreach ($filter_values as $filter_value) {
        $filter_value_array = explode('_', $filter_value);
        if (!isset($sub_queries[$filter_value_array[0]]))
            $sub_queries[$filter_value_array[0]] = array();
        $sub_queries[$filter_value_array[0]][] = 'pac.`id_attribute` = ' . (int) $filter_value_array[1];
    }
    foreach ($sub_queries as $sub_query) {
        $query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
                                FROM `' . _DB_PREFIX_ . 'product_attribute_combination` pac
                                LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa
                                ON (pa.`id_product_attribute` = pac.`id_product_attribute`) ';

        if (count($selected_filters['quantity']) != 2) {
            $query_filters_where .= ' JOIN `' . _DB_PREFIX_ . 'stock_available` sa
                                      ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`quantity` ' . (!$selected_filters['quantity'][0] ? '<=' : '>') . ' 0) ';
        }

        $query_filters_where .= Shop::addSqlAssociation('product_attribute', 'pa') . '
                                    WHERE ' . implode(' OR ', $sub_query) . ') ';
    }
    break;

 

[RICHARDMAXO]

Hello !!!!! :-) thanks you very much !!!! you save my life !!!!!!!! :-))))) 

it's works well on prestashop 1.6.1.16 !!!! THANKSSS ! 

the only thing , ans this is not important le number of product availables ... you just have to desactivate it in your filter module because it's written on the catégories page ! 

THAAAAAAANNNNNKS  LOOOOOOOOVE  

[Gilles6688]

Hello,

Don't work with PS 1.6.1.24... any solutions?

Thanks

Gilles