[HOW TO] Block Layered Display Out Of Stock Product Attributes

mozack · September 30, 2013

Here as small how to for Layered Navigation module. I found too much people asking for that mod, so i wrote this how to and it is also available in portuguese here: http://goo.gl/CCMjA7

So here we go. Block Layered works with product attributes filter (like size, color, etc) and when you have one attribute out-of-stock but still enabled, when customer check the attribute, the product still appear, even with this attribute with quantity 0.

The only way to change that without changing the core of module, is disabling this attribute when it is out-of-stock, which if you have a million products is a nightmare...

So, to avoid this problem i made some changes in blocklayered.php.

Note that my blocklayered version is 1.8.9 and my prestashop version is 1.5.4.

1 - Dont Show Products With Selected Filter Attribute Unavailable

To hide products which have the customer selected filter out of stock, open the file blocklayered.php and around line 2375 you find the code: case 'id_product_attribute' :

After that you find 2 foreach. In the second one you will find the code:

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 (version_compare(_PS_VERSION_,'1.5','>'))
$query_filters_where .= Shop::addSqlAssociation('product_attribute', 'pa');
$query_filters_where .= 'WHERE '.implode(' OR ', $sub_query).') ';
}

Replace with:

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 (version_compare(_PS_VERSION_,'1.5','>'))
$query_filters_where .= 'INNER JOIN `'._DB_PREFIX_.'stock_available` sa
ON (sa.`id_product_attribute` = pac.`id_product_attribute` AND sa.`quantity` > 0)';

if (version_compare(_PS_VERSION_,'1.5','>'))
$query_filters_where .= Shop::addSqlAssociation('product_attribute', 'pa');
$query_filters_where .= 'WHERE '.implode(' OR ', $sub_query).') ';
}

OK, now, navigating to category where this problem happens you can note that products with selected attribute unavailable will not be shown anymore.

But, looking to filter name you will se that the total products do not correspond with the ones presented, this is because the block layered table still add product attributes even when out of stock.

2 - Correction Of Out-Of-Stock Attributes in DB

To correct this problem, we need to index only attributes available. To do this, open again the blocklayered.php file and search for public function indexAttribute($id_product = null) (around line 255).

After that, change the code:

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, '.(version_compare(_PS_VERSION_,'1.5','>') ? 'product_attribute_shop.`id_shop`' : '1').'
FROM '._DB_PREFIX_.'product_attribute pa
'.(version_compare(_PS_VERSION_,'1.5','>') ? 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
'.(is_null($id_product) ? '' : 'AND pa.id_product = '.(int)$id_product).'
GROUP BY a.id_attribute, pa.id_product '.(version_compare(_PS_VERSION_,'1.5','>') ? ', product_attribute_shop.`id_shop`' : ''));

To:

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, '.(version_compare(_PS_VERSION_,'1.5','>') ? 'product_attribute_shop.`id_shop`' : '1').'
FROM '._DB_PREFIX_.'product_attribute pa
'.(version_compare(_PS_VERSION_,'1.5','>') ? Shop::addSqlAssociation('product_attribute', 'pa') : '').'
INNER JOIN '._DB_PREFIX_.'product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute
'.(version_compare(_PS_VERSION_,'1.5','>') ? '
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 '.(version_compare(_PS_VERSION_,'1.5','>') ? ', product_attribute_shop.`id_shop`' : ''));

Now, navigate your your Layered Navigation configuration page in admin and Build Attribute Index.

You should note that the number of products with attribute have been changed too.

Hope you enjoy this mod and dont forget to make a backup of your file...

Regards

Mozack

Edited August 31, 2015 by mozack (see edit history)

kristianmu · October 9, 2013

Work's like a charm!

marcko · October 10, 2013

Work's like a charm!

It works in PS 149? Any idea?

vekia · October 10, 2013

hello

this modification is for 1.5.x version

ecreeds · October 11, 2013

Thanks mozack !!! I was looking out for the solution since very long.

mozack · October 11, 2013

Hi,

I already made a mod to version 1.4.9, i'll try to find it and post here asap.

Thanks to all of you, hope that help you.

Regards

Mozack

YiannisK · October 17, 2013

hello

this is a great contribution! thank you very much for your help!

However I have a small problem.

After filtering size and shown the correct products, the color filters are not correct.
for example I have 3 shoes of size EU35 (black, white, brown) and the color filters are 4 (black, white, blue, brown)

mozack · October 17, 2013

Well Joss54,

First of all, thanks for your comment, i appreciate when people talks about contributions... Is the only way we know if we should keep working or not.

Regarding your question, i have not colors available in my websore, by this way i didn't change the color feature/attributes.

But as everything, is possible.

I'll check that and will post something here regarding your problem.

Thanks

Mozack

aneheli · October 24, 2013

THX i search this solution for long time, there is some small problems like joss54 have if You can search solution that was be great becouse we use many filters like category, size, colour, form ....

mozack · December 10, 2013

Hi Maarten T,

This was made for 1.8.9 block Layered. PS version, actually i don't think that have influence but i didn't saw yet.

Here a code that should work: 1st part of code:

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`)'; //Close Query

      if (version_compare(_PS_VERSION_,'1.5','>'))
          $query_filters_where .= 'INNER JOIN `'._DB_PREFIX_.'stock_available` sa ON (sa.`id_product_attribute` = pac.`id_product_attribute` AND sa.`quantity` > 0)';

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

For the second:

 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'.(version_compare(_PS_VERSION_,'1.5','>') ? '
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`');

What we do?

Here we add the code that check if feature have stock. You must re-index features after apply code.

Thanks

Mozack

P.S.: Please let me know if it works, since i'm not testing the code, i'm just seeing in this text area.

mozack · December 12, 2013

Hi,

Thank you for your support. Unfortunately your fix doesn't work. Now when i select 0mg i get a spinner with text "Loading..". The spinner stays there until i deselect 0mg. Next time when i select 0mg the spinner is not there anymore but when i select both 0mg and 30ml i still get 1 result where I expect none.

Hi Maarten

Just tell me. Did you re-index all attributes? Another question, is both with 0 quantities 0mg and 30ml in all products?

That's strange. In color propose, i didn't fix yet, but in size, etc this code should work as expected.

Can you provide your store url to check javascript errors, etc?

Thanks

x13_pl · December 31, 2013

Hi I have PrestaShop 1.5.6. and blocklayered 1.9.0

change this and this don't work

Charles T. · January 9, 2014

I did this :

$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`)'.
            'INNER JOIN `'._DB_PREFIX_.'stock_available` sa
            ON (sa.`id_product_attribute` = pac.`id_product_attribute` AND sa.`quantity` > 0)'.
Shop::addSqlAssociation('product_attribute', 'pa').'
WHERE '.implode(' OR ', $sub_query).') ';

and it's working like a charm

Dejv · January 21, 2014

Works great, thanks mozack!!

Torbz · February 25, 2014

Hi Mozack - bless you for working this one out, as we're now on our fourth filtering module (and after trying 3 3rd party modules, we're back to Layered Navigation, as it's the most effective).

I'm using PS v1.5.6.2 with v1.10.1 of the block layered module.

The code seems to have changed quite considerably (certainly with regards to positioning) and though I've been able to find the relevant parts of the code you've mentioned above, I've had strange results.

The first part is not that relevant, as I'm hiding the quantities of all the products on each filter option anyway in the F.O.

The second part, I implemented, though Dreamweaver complained about code errors, so I tried to fix them and then re-indexed.

The filter options for adult shoe size (www.sunproof.co.uk/dev) did change - but I was still getting false positives in the product search results - i.e. models of crocs being listed that were not actually in stock and available for that size attribute.

If you, or anyone out there would like to offer to help fix this, we'd be grateful, because the filter works so well otherwise (and I'd always be prepared to stick it in the jobs section as a paid fix)...

Of course, the final step to total 'filter domination' would be to get the damn thing displaying on the homepage for the whole catalogue - but that seems to be a different story altogether.

Do you fancy having another delve into this?

augis2003 · March 3, 2014

Same problem here with presta 1.5.6.2 and 1.10.1 .. why they can't make option in module configuration to disable this...

mozack · March 3, 2014

Hi Mozack - bless you for working this one out, as we're now on our fourth filtering module (and after trying 3 3rd party modules, we're back to Layered Navigation, as it's the most effective).

I'm using PS v1.5.6.2 with v1.10.1 of the block layered module.

The code seems to have changed quite considerably (certainly with regards to positioning) and though I've been able to find the relevant parts of the code you've mentioned above, I've had strange results.

The first part is not that relevant, as I'm hiding the quantities of all the products on each filter option anyway in the F.O.

The second part, I implemented, though Dreamweaver complained about code errors, so I tried to fix them and then re-indexed.

The filter options for adult shoe size (www.sunproof.co.uk/dev) did change - but I was still getting false positives in the product search results - i.e. models of crocs being listed that were not actually in stock and available for that size attribute.

If you, or anyone out there would like to offer to help fix this, we'd be grateful, because the filter works so well otherwise (and I'd always be prepared to stick it in the jobs section as a paid fix)...

Of course, the final step to total 'filter domination' would be to get the damn thing displaying on the homepage for the whole catalogue - but that seems to be a different story altogether.

Do you fancy having another delve into this?

Hi,

So, i'm using exactely the same version as yours (PS 1562 and BL 1.10.1) and it works. I cannot check the url your are giving me, so i cannot try that.

The only part that doesn't work is for color, i didn't change anything there cause i don't use color combination in my website.

But, there was some changes in code since my first post (PS 1.5.4 and BL 1.8.x), so there is the new code changes:

First you need to find the line (around 1846) inside case 'id_attribute_group': , in the second foreach and change with the code below

Part 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`)
						INNER JOIN `'._DB_PREFIX_.'stock_available` sa
ON (sa.`id_product_attribute` = pac.`id_product_attribute` AND sa.`quantity` > 0)'.
						Shop::addSqlAssociation('product_attribute', 'pa').'
						WHERE '.implode(' OR ', $sub_query).') ';
					}

After, go to line (around 872) the function indexAttribute and change the whole function with the code:

public function indexAttribute($id_product = null)
	{
		if (is_null($id_product))
			Db::getInstance()->execute('TRUNCATE '._DB_PREFIX_.'layered_product_attribute');
		else
			Db::getInstance()->execute('
				DELETE FROM '._DB_PREFIX_.'layered_product_attribute 
				WHERE id_product = '.(int)$id_product
			);
		
		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`'
		);
		
		return 1;
	}

Hope this solve the issue.

Don't forget to re-index all attributes and make a backup of your block layered.php file.

Please reply if your problem was solved...

Thanks

Mozack

mozack · March 3, 2014

Same problem here with presta 1.5.6.2 and 1.10.1 .. why they can't make option in module configuration to disable this...

Hi,

Please check my post that change the code to your version... (the quote before).

Don't forget to tell me if works

Thanks

Mozack

Torbz · March 3, 2014

The site has gone live now...2 days ago...but I will try those changes later tonight when it's not so busy.

The site is now at www.sunproof.co.uk/store

Thanks for taking the time out mate! Appreciated greatly...

Edited March 3, 2014 by Torbz (see edit history)

augis2003 · March 4, 2014

Hi,

Please check my post that change the code to your version... (the quote before).

Don't forget to tell me if works

Thanks

Mozack

Hi,

tested, and seems working fine!

mozack · March 4, 2014

Hi,

tested, and seems working fine!

Hi,

Glad to know...

Thanks

pos prestashop · April 7, 2014

THANK YOU, It works perfect!

anzcoin · April 22, 2014

NOt work in 1.6

mozack · April 22, 2014

NOt work in 1.6

Hi, it was designed to work on prestashop up to 1.5.6 and layered up to 1.9 (i think). But essentially, to work just in prestashop 1.5+

anzcoin · April 22, 2014

Hi, it was designed to work on prestashop up to 1.5.6 and layered up to 1.9 (i think). But essentially, to work just in prestashop 1.5+

Anh with PS 1.6 how to hide product when it out of stock, I don't want see product show for buyer when it out of stock

Thanks

afshop · May 28, 2014

Hi,

So, i'm using exactely the same version as yours (PS 1562 and BL 1.10.1) and it works. I cannot check the url your are giving me, so i cannot try that.

The only part that doesn't work is for color, i didn't change anything there cause i don't use color combination in my website.

But, there was some changes in code since my first post (PS 1.5.4 and BL 1.8.x), so there is the new code changes:

First you need to find the line (around 1846) inside case 'id_attribute_group': , in the second foreach and change with the code below

Part 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`)
						INNER JOIN `'._DB_PREFIX_.'stock_available` sa
ON (sa.`id_product_attribute` = pac.`id_product_attribute` AND sa.`quantity` > 0)'.
						Shop::addSqlAssociation('product_attribute', 'pa').'
						WHERE '.implode(' OR ', $sub_query).') ';
					}
After, go to line (around 872) the function indexAttribute and change the whole function with the code:
public function indexAttribute($id_product = null)
	{
		if (is_null($id_product))
			Db::getInstance()->execute('TRUNCATE '._DB_PREFIX_.'layered_product_attribute');
		else
			Db::getInstance()->execute('
				DELETE FROM '._DB_PREFIX_.'layered_product_attribute 
				WHERE id_product = '.(int)$id_product
			);
		
		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`'
		);
		
		return 1;
	}
Hope this solve the issue.

Don't forget to re-index all attributes and make a backup of your block layered.php file.

Please reply if your problem was solved...

Thanks

Mozack

Hi Mozack!!!

Thanks for your great contribution!!!

It' s works perfectly!!!

Angela

aparra · August 12, 2014

Dear Mozack

First of all thanks for this mod. I am trying to implement it but i could not get it to work.

I have (PS 1.5.4 and BL 1.8.x) I am following the instructions exactly the way you explain, and do the re-index all attributes as well, and it seems to work perfectly, but I do not see any changes in my drop down when the attribute has no stock, still show the attribute in the dropdown.

Any suggestions of what am I doing wrong?

Thnaks!

Kind Regards

Alberto Fernández · September 18, 2014

It works for me like a charm in PS 1.5.61 and BL 2.0.0 and for colors too!

In my case I'm using texture images for color values: http://cincelaser.com/38-complementos-deica

The last only thing I wish for that is when you select one of the filtered products, it will appear with the proper image.

I mean, if you are looking for blue things in a category, show me the product I choosen but with the blue color image instead of the default color value image.

Thanks!

Edited September 18, 2014 by Alberto Fernández (see edit history)

cafetango · September 25, 2014

Dear Mozack,

Thank you for the mod. I will apply them this weekend and hope they work for my site.

I have been have all sort of problem with the block layered module (to add to the complication, I work in a multi-shop environment) and have been unable to put the site into production because of these.

Cheers,

Cha

Edited September 25, 2014 by cafetango (see edit history)

Judith VDB · November 21, 2014

Hi everyone

Great topic and great work so far Mozack!

Glad to find others who are experiencing the same issues with the block layered module.

I'm currently working in version 1.6.0.9 of Prestashop. Does anyone have a work-around for this version already? I've been searching all over the net for a solution

Also I'm not a web developer, so I'd rather leave it to the more experienced...

Any help would be much appreciated!

Kind regards

Judith

Edited November 21, 2014 by Judith VDB (see edit history)

dulantziko · December 3, 2014

Hi,

I have the prestashop 1.5.6.1 and I try to make changes on code but it doesn´t go OK. I make the changes and after upload the CSV combinations.

I attach an image.

The link to view the error is http://www.ociomascotas.com/presta/comida-seca/237-applaws-perro-seco-alimento-completo-para-perros.html

Thanks

freeman78 · July 5, 2015

Anyone has a working fix for 1.6.0.14?

I tried to implement and the filter does the job, but when going to click the attribute (which shows the current totals for attributes) it will not return any product.

A big shame, maybe an easy one!

zaok · July 6, 2015

I'm going to try this for 1.6 too. Can anyone confirm if it works ?

I'm amazed how this option does not come out-of-the-box, and even more amazed that I didn't found one single module that does this.. Does this means that every PrestaShop store shows unavailable products on listing and no one cares ?

freeman78 · July 6, 2015

Works Bad to me, i ended up buying advanced search. Costy but worth if you're serious about it. From what i understood PS is nice but obviously keep the great features to be paid

zaok · July 6, 2015

That modules does that ?

freeman78 · July 6, 2015

It's an advanced layered module. Very professional and also work for stock. but costy.Check demo also for backoffice before thinking about it!

cafetango · July 12, 2015

I just applied the mod to PS 1.6.1.0 & BL 2.0.13 and it still works.

Thank you again Mozack.

Would be so nice if the BL team incorporate this in the standard release!

Stevieman · September 3, 2015

Unfortunately it doesn't work on my prestashop 1.6.1, module version 2.1.2. Anyone has a solution for this? I will have to buy an advanced filter if not.

ItalianBoutiqueShoes · September 14, 2015

I fix it like follow. I add also a filter to not exclude product not on stock but that can be order anyway and a fix for multistore.

ABOUT line 870 replace with:

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, pas.id_shop
            FROM '._DB_PREFIX_.'product_attribute pa
            INNER JOIN '._DB_PREFIX_.'product_attribute_shop pas ON pas.id_product_attribute = pa.id_product_attribute
            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
            
            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).'
            
            WHERE (sa.`quantity` > 0 OR sa.`out_of_stock` = 1)
            
            GROUP BY a.id_attribute, pa.id_product , pas.id_shop'
        );

ABOUT LINE 1844

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`
                        
                        INNER JOIN `'._DB_PREFIX_.'stock_available` sa
                        ON sa.`id_product_attribute` = pac.`id_product_attribute`'.
                        
                        Shop::addSqlAssociation('product_attribute', 'pa').'
                        WHERE (sa.`quantity` > 0 OR sa.`out_of_stock` = 1)
                        AND '.implode(' OR ', $sub_query).') ';
                    }

I hope it will help :-)

mr.pz · September 22, 2015

Hi,

I have found the solution for the problem (I have ver. 1.6.1.1) in this post:

https://www.prestashop.com/forums/topic/447213-layered-navigation-block-module-dont-display-out-of-stock-products-for-choosen-attributes/

CarpalWatch.com · September 27, 2015

Just want to highlight that if you have multi-stores enabled but only using one store, you need to check that the sa.id_shop=<id of the shop in use>, otherwise it will just scan through all stores and the above code will not work...

kNife77 · August 16, 2016

Works for 1.6.0.9!

system_error86 · January 26, 2017

Hello,

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

SilviaPimenta · April 3, 2017

Good Morning, I've been watching this post, and I have to accomplish something that I think what you have said is possible with the resolution here post.

But I wanted to be sure.

My case is as follows:

-I want to insert a layered filter on the AdminProducts page, the page where you have the listing of all the products in my store, but I want this filtering to be saved for each person who accesses the backoffice (saves the filtering of each person),

-And only wanted to be able to add at most 15 filters, "I also wanted the filters to be a bit different from the ones that already exist.

Can I adapt this example to myself?

Thank you for the clarification.

PS: My version of prestashop is 1.6

Edited April 3, 2017 by SilviaPimenta (see edit history)

ellisandwren · July 12, 2017

Hello! I need to do this on my site also, unfortunately I'm a complete novice and have no idea where to start to add this code - is anybody able to help me please? Thanks

Gilles6688 · December 3, 2020

Hello,

I have make those modifications but I have the good answer in the left side but it don't show me the good answer, Why?

Thanks

[HOW TO] Block Layered Display Out Of Stock Product Attributes

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment