[solved]Display all products with feature value = "X" only to one customer group

[Pedro Pinto]

hi,

Display all products  with feature value = "X" only to one customer group . 

Example:

 -customer group A can only see products without id_feature_value=X and display it on product-list.tpl
- customer group B only see products with id_feature_value=X and display it on product-list-pro.tpl (in this case)

 

At this point (and working), i've 2 product-list.tpl (one for the standart customer and the other for professional  customer )  using this on category.tpl

{if (int)Group::getCurrent()->id == 4}
	{include file="./product-list-pro.tpl" products=$products}
{else}
	{include file="./product-list.tpl" products=$products}
{/if}

inside phpmyadmin using this query i get products with id_feature_value ="X" ('251' in this case)

SELECT * FROM ps_product_lang pl,ps_product p,ps_feature_value_lang fvl,ps_feature_value fv,ps_feature f,ps_feature_lang fl WHERE pl.id_product=p.id_product AND fvl.id_feature_value=fv.id_feature_value AND fv.id_feature=f.id_feature AND fvl.id_lang=1 AND fl.id_feature =f.id_feature AND fl.id_lang=1 AND fv.id_feature_value=251

and now i'm stucked.....what files should i override to make this work?

Thank You.

 

[jgamio]

HI, take a look to these post  https://www.prestashop.com/forums/topic/321924-how-to-change-category-product-list-sql/  

[Pedro Pinto]

thanks, but... i dont understand... 

dont i need 2 sql to get it work? 

- one for get products WITH  feature value X to display to customer group A
- one to get products WITHOUT feature value X to display to others customer group

Thanks

Update=======================

i can do this work by using the above code in product-list.tpl and the opposite on product-list-pro.tpl

{foreach from=$products item=product name=products}
<!-- -->
	{if isset($product.features)}
    	{foreach from=$product.features item=feature}
        	{if $feature.name == 'Category'}
            	{if $feature.value != 'A4'}

but this is not the solution.... because this only hide the products but they continue to be part of the product count.

 

still searching....

thanks

Edited February 12, 2018 by Pedro Pinto
updated (see edit history)

[jgamio]

You need understand the struture first

 

1 - Views ( Show data )

tpls are just view of data you cant change the data itself at these point ( Maybe use some jquery to update based on what you want ). You can show or hide data but not handle the data itself

2- Controller ( Control the data )

You need change the controller to handle data. You can do in many ways

- Changing the code on the controller ( very bad because when you update the version you lost your changes )

- Creating a override to change the controller ( bad but something is not other way )

- Creating a module to use the hooks of prestashop to change the behavior

 

You have the feeling you solve your problem but you just hidden data on the tpl of the list

If a customer on the group A want to see a product on the group B is going to be able to see the product and buy it even if you hide from the list

 

 

[Pedro Pinto]

well...it works!

i made an override on the Category classe

inside public function getProducts(

/** Return only the number of products */
if ($get_total) {
	$sql = 'SELECT COUNT(cp.`id_product`) AS total
	FROM `'._DB_PREFIX_.'product` p
	'.Shop::addSqlAssociation('product', 'p').'
	LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`
	LEFT JOIN `'._DB_PREFIX_.'feature_product` fp ON p.`id_product` = fp.`id_product`
	WHERE p.`id_product` = fp.`id_product`
	AND cp.`id_category` = '.(int)$this->id.
	($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').
	($active ? ' AND product_shop.`active` = 1' : '').
	($id_supplier ? 'AND p.id_supplier = '.(int)$id_supplier : '');
	
	if ((int)Group::getCurrent()->id == 4) {
		$sql .= ' AND fp.`id_feature` = 2  AND fp.`id_feature_value` = 251 AND p.`id_product` = fp.`id_product`';
	} else {
		$sql .= ' AND fp.`id_feature` = 2  AND fp.`id_feature_value` != 251 AND p.`id_product` = fp.`id_product`';
	}

	return (int)Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
}

and

$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity'.(Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute,
                    product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').', pl.`description`, pl.`description_short`, pl.`available_now`,
                    pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image,
                    il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
                    DATEDIFF(product_shop.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00",
                    INTERVAL '.(int)$nb_days_new_product.' DAY)) > 0 AS new, product_shop.price AS orderprice
                FROM `'._DB_PREFIX_.'category_product` cp
                LEFT JOIN `'._DB_PREFIX_.'product` p
                    ON p.`id_product` = cp.`id_product`
                '.Shop::addSqlAssociation('product', 'p').
                (Combination::isFeatureActive() ? ' LEFT JOIN `'._DB_PREFIX_.'product_attribute_shop` product_attribute_shop
                ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id.')':'').'
                '.Product::sqlStock('p', 0).'
                LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
                    ON (product_shop.`id_category_default` = cl.`id_category`
                    AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
                LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
                    ON (p.`id_product` = pl.`id_product`
                    AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').')
                LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop
                    ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop='.(int)$context->shop->id.')
                LEFT JOIN `'._DB_PREFIX_.'image_lang` il
                    ON (image_shop.`id_image` = il.`id_image`
                    AND il.`id_lang` = '.(int)$id_lang.')
                LEFT JOIN `'._DB_PREFIX_.'manufacturer` m
                    ON m.`id_manufacturer` = p.`id_manufacturer`
                LEFT JOIN `'._DB_PREFIX_.'feature_product` fp
                    ON p.`id_product` = fp.`id_product`
                WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
                    AND cp.`id_category` = '.(int)$this->id
                    .($active ? ' AND product_shop.`active` = 1' : '')
                    .($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
                    .($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '');


         if ((int)Group::getCurrent()->id == 4) {
            $sql .= ' AND fp.`id_feature` = 2 AND fp.`id_feature_value` = 251 AND p.`id_product` = fp.`id_product`';
        } else {
            $sql .= ' AND fp.`id_feature` = 2 AND fp.`id_feature_value` != 251 AND p.`id_product` = fp.`id_product`';
        }    

 

Thanks jgamio.

 

Edited February 14, 2018 by Pedro Pinto
correction (see edit history)

[Rachit Agarwal]

Hi,

I want to override the Product List SQL so I have overridden the getProducts method under Category.php class but nothing happened. I'm using 1.7 version.

Please suggest on the same.

Thanks.