Pedro Pinto Posted February 9, 2018 Share Posted February 9, 2018 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. Link to comment Share on other sites More sharing options...
jgamio Posted February 11, 2018 Share Posted February 11, 2018 HI, take a look to these post https://www.prestashop.com/forums/topic/321924-how-to-change-category-product-list-sql/ Link to comment Share on other sites More sharing options...
Pedro Pinto Posted February 12, 2018 Author Share Posted February 12, 2018 (edited) 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) Link to comment Share on other sites More sharing options...
jgamio Posted February 13, 2018 Share Posted February 13, 2018 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 Link to comment Share on other sites More sharing options...
Pedro Pinto Posted February 14, 2018 Author Share Posted February 14, 2018 (edited) 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) 1 Link to comment Share on other sites More sharing options...
Rachit Agarwal Posted May 10, 2019 Share Posted May 10, 2019 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now