Only display attributes available in stock on filter (BlockLayered)

[Nixnix]

I need help finding how to set up my BlockLayered filtering in Category view so that only available attribute variations are shown. I am using Prestashop 1.6.0.9.

 

For example, if I have a tshirt in color "Red" and Size S, M and L, the tshirt is shown when I filter to only show size S even if the available amount in stock for size S is = zero. This is horrible for the customers, because it is very hard for them to find any available sizes.

 

Is this setting hidden somewhere in Prestashop Backend?

Do I have to do it with a module...?

 

I could not find anything (module or forum thread) so I would greatly appreciate help on how to solve this crucial problem.

 

Thank you very much!

 

Nixnix

 

 

[Jon Langberg]

Hi,

 

I am having the exact same problem, and have a hard time even imagining that this is supposed to be the way that Prestashop works by default.

 

Im on PS 1.6.0.04 and the 2.0.7 of the BlockLayered module.

 

Fingers crossed that someone has a fix for this!

 

Best regards!

Edited April 30, 2015 by Jon Langberg (see edit history)

[Nixnix]

Yeah, I had expected to find more about this on the forum (but found nothing). It is strange.

I fear it is in fact how Prestashop works by default - that it is not possible. So perhaps we have to find a module. I do not know of any though.

 

If anyone knows of a solution to this, please please let us know.

[Jon Langberg]

maybe this helps ?

If not i might be able to upgrade it if enough people want it.

 

https://www.prestashop.com/forums/topic/414010-free-module-dataswitchpro-automatic-onoff-switch-for-products-and-categories/?hl=%2Bdataswitchpro

 

Thanks, but it doesn't really solve the actual problem.

 

It does deactivate a product when its total stock reaches zero, but the problem at hand is that we need to deactivate a certain attribute from a product when the stock of that particular attribute reaches zero. Actually we don't need it to be deactivated, we just need the product to not appear with that attribute in the blocklayered modules filtered navigation.

 

I hope that clarifies things, and would absolutely love if you would be interested in looking at a solution for this problem - I am sure that you could charge people for this module.

 

Best regards!

[Jon Langberg]

This option, unfortunately, only affects the product-page, and not the filtering on category pages.

[Jon Langberg]

For your information, I solved the problem by talking to the developer (Oleg) behind this module: http://addons.prestashop.com/en/search-filters-prestashop-modules/18575-amazzing-filter.html

 

He made a custom version of his module to solve the problem. Works like a charm, and Oleg is offering great service.

[Nixnix]

For your information, I solved the problem by talking to the developer (Oleg) behind this module: http://addons.prestashop.com/en/search-filters-prestashop-modules/18575-amazzing-filter.html

 

He made a custom version of his module to solve the problem. Works like a charm, and Oleg is offering great service.

 

Wow, that's great Jon! The demo filtering looks really nice and quick.

So the author had to modify it for you to include "instant availability" for e.g. colour + size? At an extra cost, I assume?

[Jon Langberg]

Wow, that's great Jon! The demo filtering looks really nice and quick.

So the author had to modify it for you to include "instant availability" for e.g. colour + size? At an extra cost, I assume?

 

Yes, he made a modified version of the module ready for installation. No extra cost - As I said, Oleg is offering great customer service :-)

 

Just write to him that you would like the same version of his module that Jon bought, and I am sure that you will get it straight away.

[Nixnix]

For anyone who may be interested: Future versions of the Amazzing Filter module will apparently have this feature built in.

I can confirm Jon's opinion: Very high quality module and professional support. Highly recommended.

[Magdalena_Nowak]

Hi!

You've got this fo free? 

Can I get it too?

[Magdalena_Nowak]

I mean I would like to buy the modul too. Could you show me your shops, so I can see it live? Thank you.

[Jon Langberg]

I mean I would like to buy the modul too. Could you show me your shops, so I can see it live? Thank you.

http://startspring.dk - you have to choose one of the menu items in order to this module in action.

[Tahsin_85]

Hi,

 

you can change stock in table ps_stock_available to control display available combinations.

 

[vparadiso]

Hi,

I use ps 1.6.1.12 and I have a problem with the stock available for each attribute of products. For example, I have defined an attribute "shoes size" with value (34, 34.5, 35....). After I have inserted some shoes. All shoes have with combinations all value of shoes attribute, but only a few of them have quantity > 0. When I go to frontend I see all values of shoes size. The number in bracket is the count of product combinations and not the product that they have quantity > 0. For example, no product has a combination 34 but it looks the same with the total product counter.

I see that the problem is in blocklayered.php in line 2219 in this query:


                    $sql_query['select'] = '
                    SELECT COUNT(DISTINCT lpa.id_product) nbr, lpa.id_attribute_group,
                    a.color, al.name attribute_name, agl.public_name attribute_group_name , lpa.id_attribute, ag.is_color_group,
                    liagl.url_name name_url_name, liagl.meta_title name_meta_title, lial.url_name value_url_name, lial.meta_title value_meta_title';
                    $sql_query['from'] = '
                    FROM '._DB_PREFIX_.'layered_product_attribute lpa
                    INNER JOIN '._DB_PREFIX_.'attribute a
                    ON a.id_attribute = lpa.id_attribute
                    INNER JOIN '._DB_PREFIX_.'attribute_lang al
                    ON al.id_attribute = a.id_attribute
                    AND al.id_lang = '.(int)$id_lang.'
                    INNER JOIN '._DB_PREFIX_.'cat_restriction p
                    ON p.id_product = lpa.id_product
                    INNER JOIN '._DB_PREFIX_.'attribute_group ag
                    ON ag.id_attribute_group = lpa.id_attribute_group
                    INNER JOIN '._DB_PREFIX_.'attribute_group_lang agl
                    ON agl.id_attribute_group = lpa.id_attribute_group
                    AND agl.id_lang = '.(int)$id_lang.'
                    LEFT JOIN '._DB_PREFIX_.'layered_indexable_attribute_group_lang_value liagl
                    ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = '.(int)$id_lang.')
                    LEFT JOIN '._DB_PREFIX_.'layered_indexable_attribute_lang_value lial
                    ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = '.(int)$id_lang.') ';

                    $sql_query['where'] = 'WHERE lpa.id_attribute_group = '.(int)$filter['id_value'];
                    $sql_query['where'] .= ' AND lpa.`id_shop` = '.(int)$context->shop->id;
                    $sql_query['group'] = '
                    GROUP BY lpa.id_attribute
                    ORDER BY ag.`position` ASC, a.`position` ASC';
                    
If I try to edit this I do not reach the desired result.

Can someone help me.

Thanks.

[infisual]

 

I need help finding how to set up my BlockLayered filtering in Category view so that only available attribute variations are shown. I am using Prestashop 1.6.0.9.

 

For example, if I have a tshirt in color "Red" and Size S, M and L, the tshirt is shown when I filter to only show size S even if the available amount in stock for size S is = zero. This is horrible for the customers, because it is very hard for them to find any available sizes.

 

Is this setting hidden somewhere in Prestashop Backend?

Do I have to do it with a module...?

 

I could not find anything (module or forum thread) so I would greatly appreciate help on how to solve this crucial problem.

 

Thank you very much!

 

Nixnix

 

I had the same problem when I had 1.6x and it is also in 1.7.1.2

 

I made a change in the code for 1.6 and 1.7 and it works well for me. All my products have the same attributes S, M, L, etc. The change will now display only the product available with the attribute selected. I did not test it with simple products. I hope the developers can fix issue, it is also shared in BOOM-2025

 

for 1.6 the change in file:  modules/blocklayered/blocklayered.php

for 1.7 the change in file:  modules/ps_facetedsearch/ps_facetedsearch.php

 

you will need to add the following line 

JOIN `'._DB_PREFIX_.'stock_available` sa

ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`quantity`>0)

 

so the complete line will like this:

    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`)'.

         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).') ';

     }

 

good luck

Edited July 6, 2017 by infisual (see edit history)

[ellisandwren]

Hiya I'm getting complaints from my customers about the same issue...I've attempted to change the code as above, but I am a complete novice...I'm not sure I've done it correctly and, if I have, it has not solved the problem for me. Is anybody able to help? Thanks

[uzairansari1]

Hello All,

 

We had the same issue and thanks to infisual we updated the following code in 

for 1.7 the change in file:  modules/ps_facetedsearch/ps_facetedsearch.php

 

JOIN `'._DB_PREFIX_.'stock_available` sa

ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`quantity`>0)

 

This issue of filter for out of stock was solved.

 

Regards

 

Uzair Ansari

[gfliess]

Hello everybody, thanz to infisual  i made the change and works in 1.7, but in the filter if you show the number of products in that atributte the number in parentheses does not descend, can you tell me how to fix this?

 

Thankz!

[snakez]

On 4/8/2018 at 6:50 PM, gfliess said:

Hello everybody, thanz to infisual  i made the change and works in 1.7, but in the filter if you show the number of products in that atributte the number in parentheses does not descend, can you tell me how to fix this?

 

Thankz!

yeah waiting for this and we solved ! really hope there is a way to do it!

[acorn]

@vparadiso and @all

Regarding the count in brackets in the filterblock showing incorrect numbers, after making the suggested changes to the main query...

I was stuck on this for a good long while, eventually got to the solution:

this is for prestashop 1.6.1.17 but will likely work with minimal adaptation for other 1.6 versions...

in the function getFilterBlock (blocklayered.php) circa :2904

look for ` case 'id_attribute_group': `

and add the lines:

                    LEFT JOIN ps_product_attribute_combination pac ON pac.id_attribute = a.id_attribute
                    JOIN `ps_stock_available` sa ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`id_shop` = '.(int)$context->shop->id.' AND sa.`quantity`>0 AND sa.id_product = p.id_product) '

to the end of the from clause...

it should look like this:

     $sql_query['from'] = '

     FROM '._DB_PREFIX_.'layered_product_attribute lpa

                    INNER JOIN '._DB_PREFIX_.'attribute a

     ON a.id_attribute = lpa.id_attribute

     INNER JOIN '._DB_PREFIX_.'attribute_lang al

     ON al.id_attribute = a.id_attribute

     AND al.id_lang = '.(int)$id_lang.'

     INNER JOIN '._DB_PREFIX_.'cat_restriction p

     ON p.id_product = lpa.id_product

     INNER JOIN '._DB_PREFIX_.'attribute_group ag

     ON ag.id_attribute_group = lpa.id_attribute_group

     INNER JOIN '._DB_PREFIX_.'attribute_group_lang agl

     ON agl.id_attribute_group = lpa.id_attribute_group

     AND agl.id_lang = '.(int)$id_lang.'

     LEFT JOIN '._DB_PREFIX_.'layered_indexable_attribute_group_lang_value liagl

     ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = '.(int)$id_lang.')

     LEFT JOIN '._DB_PREFIX_.'layered_indexable_attribute_lang_value lial

     ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = '.(int)$id_lang.')

                    LEFT JOIN ps_product_attribute_combination pac ON pac.id_attribute = a.id_attribute

                    JOIN `ps_stock_available` sa ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`id_shop` = '.(int)$context->shop->id.' AND sa.`quantity`>0 AND sa.id_product = p.id_product) ';

 

This should show the correct count in the filter block.

 

IMPORTANT:
others here have suggested editing the core file directly to add these extra lines... this is actually not advisable since your changes will get lost if you upgrade the blocklayered module or your store... 

It is much better to create an override file in /overrides/modules/blocklayered/blocklayered.php, copy the original function there and add the new lines to that file. instructions for creating overrides can be found easily in the docs.

[Kamil]

On 7/6/2017 at 3:38 PM, infisual said:

I had the same problem when I had 1.6x and it is also in 1.7.1.2

you will need to add the following line 

JOIN `'._DB_PREFIX_.'stock_available` sa

ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`quantity`>0)

 

so the complete line will like this:

    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`)'.

         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).') ';

     }

 

good luck

 

Thanks for help!

Best regards

Edited January 10, 2019 by Kamil (see edit history)

[tanasi]

Quote

you will need to add the following line 

JOIN `'._DB_PREFIX_.'stock_available` sa

ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`quantity`>0)

 

Hi, 

I am on 1.6.1.23 and have implemented the above code. It seems that it works partially. That is if I select the size attribute then quantity is taken into account regardless of how many other attributes or features are selected.

But if I do the opposite e.g. start by selecting features then there are products that are visible (not filtered out) with 0 quantity. Imagine that 1 of these products (with no quantity) has a Small size, then if I select the Small size this product is dissapeared. 

Is there any chance that this code does not take into account the product features?

Thanks, 

[jeremiezip]

Hello guys,

In 1.6 don't forget to change the query of indexing attribute:

In override of blocklayered module, look at the public function "IndexAttribute".

Add the following lines in the query (same from above) 

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

So this is the result in my version of Prestashop 1.6 :

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_.'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
	JOIN `'._DB_PREFIX_.'stock_available` sa ON (sa.`id_product_attribute`= pac.`id_product_attribute` AND sa.`quantity` > 0)
	'.(is_null($id_product) ? '' : 'AND pa.id_product = '.(int)$id_product).'
	GROUP BY a.id_attribute, pa.id_product , product_attribute_shop.`id_shop`'
);

After that, clear cache and reindex attributes filters.

Edited March 21, 2019 by jeremiezip (see edit history)

[Trendek]

 

On 6/7/2017 at 15:38, infisual said:

Ho avuto lo stesso problema quando avevo 1.6x ed è anche in 1.7.1.2

 

Ho apportato una modifica al codice per 1.6 e 1.7 e funziona bene per me. Tutti i miei prodotti hanno gli stessi attributi S, M, L, ecc. La modifica ora mostrerà solo il prodotto disponibile con l'attributo selezionato. Non l'ho provato con prodotti semplici. Spero che gli sviluppatori possano risolvere il problema, è anche condiviso in BOOM-2025

 

per 1.6 la modifica del file: modules / blocklayered / blocklayered.php

per 1.7 la modifica del file: modules / ps_facetedsearch / ps_facetedsearch.php

 

dovrai aggiungere la seguente riga 

ISCRIVITI `` ._DB_PREFIX _. 'Stock_available` sa

ON (sa`id_product_attribute` = pac`id_product_attribute` AND sa`quantity`> 0)

 

quindi alla linea completa piacerà questo:

    foreach ($ sub_queries come $ sub_query) {

         $ query_filters_where. = 'AND p.id_product IN (SELEZIONA pa`id_product`

         FROM '' ._DB_PREFIX _. 'Product_attribute_combination` pac

         SINISTRA ISCRIVITI '' ._DB_PREFIX _. 'Product_attribute` pa

         ON (pa`id_product_attribute` = pac`id_product_attribute`) '.

         Acquista :: addSqlAssociation ('product_attribute', 'pa'). '

         ISCRIVITI `` ._DB_PREFIX _. 'Stock_available` sa

         ON (sa`id_product_attribute` = pac`id_product_attribute` AND sa`quantity`> 0)

         WHERE '.implode (' OR ', $ sub_query).') ';

     }

 

in bocca al lupo

Hi infisual,
I tried to make your change but I can't solve. Unfortunately it doesn't work on my site. I use the version of prestashop 1.7.6 and the "search by aspects" form 3.3.4. can you help me?

[cricket-hockey]

On 11/7/2019 at 8:30 AM, Trendek said:

 

Hi infisual,
I tried to make your change but I can't solve. Unfortunately it doesn't work on my site. I use the version of prestashop 1.7.6 and the "search by aspects" form 3.3.4. can you help me?

@Trendek Did you ever get this working? I am on 1.7.6.5 and the ps_facetedsearch.php file seems completely different now from what @infisual said to do a few years ago. It's ridiculous that it still filters unavailable combinations and really needs to be fixed by the developers.

[Spir]

I have last version I guess (v3.6.0) and yes code looks totally different. I don't know how to fix this with this new version.

[markovo]

On 7/6/2017 at 4:38 PM, infisual said:

I had the same problem when I had 1.6x and it is also in 1.7.1.2

 

I made a change in the code for 1.6 and 1.7 and it works well for me. All my products have the same attributes S, M, L, etc. The change will now display only the product available with the attribute selected. I did not test it with simple products. I hope the developers can fix issue, it is also shared in BOOM-2025

 

for 1.6 the change in file:  modules/blocklayered/blocklayered.php

for 1.7 the change in file:  modules/ps_facetedsearch/ps_facetedsearch.php

 

you will need to add the following line 

JOIN `'._DB_PREFIX_.'stock_available` sa

ON (sa.`id_product_attribute`=pac.`id_product_attribute` AND sa.`quantity`>0)

 

so the complete line will like this:

    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`)'.

         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).') ';

     }

 

good luck

This WORKED!!! Thank you! You saved my life!