Jump to content

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


r@ph
 Share

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...
  • 2 months later...

Thanks [email protected], 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
 Share

×
×
  • Create New...

Important Information

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