Jump to content

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


Recommended Posts

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

Share this post


Link to post
Share on other sites
  • 4 weeks later...

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? 

  • Like 1

Share this post


Link to post
Share on other sites
  • 2 months later...

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.

  • Like 1

Share this post


Link to post
Share on other sites

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. 

  • Like 1

Share this post


Link to post
Share on other sites
  • 4 weeks later...
  • 3 weeks later...

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:

  • Like 1

Share this post


Link to post
Share on other sites
  • 1 month later...
  • 2 months later...

 

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.

Share this post


Link to post
Share on other sites
  • 3 months later...

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites
  • 1 month later...
  • 1 month later...

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.

  • Like 1

Share this post


Link to post
Share on other sites
  • 2 weeks later...
  • 1 month later...
  • 1 month later...
  • 2 weeks later...
  • 10 months later...

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 ? :)

Share this post


Link to post
Share on other sites
  • 2 months later...

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;

 

Share this post


Link to post
Share on other sites
  • 1 year later...

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  

Share this post


Link to post
Share on other sites
  • 9 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More