Alexandru Brulea Posted August 2, 2016 Share Posted August 2, 2016 (edited) Hi, Is it possible to show on homepage only bestseller products that are in stock? Thanks Edited August 2, 2016 by Alexandru Brulea (see edit history) Link to comment Share on other sites More sharing options...
rocky Posted August 3, 2016 Share Posted August 3, 2016 The easiest solution is to override ProductSale::getBestSalesLight and add p.quantity > 0 to the WHERE clause, but that will affect best sellers throughout the entire website. If you want to change just the homepage, you'll need to copy the function to a separate function like ProductSale::getBestSalesHome and then call that from the hookDisplayHome function in modules/blockbestsellers/blockbestsellers.php. Link to comment Share on other sites More sharing options...
Alexandru Brulea Posted August 8, 2016 Author Share Posted August 8, 2016 Hi, It ok i want to show on all site only bestseller where p.quantity>0, i did this but still not working... I have put AND p.quantity>0 in "public static function getBestSalesLight" from classes/ProductSale.php $sql .= 'WHERE product_shop.`active` = 1AND p.quantity > 0AND p.`visibility` != \'none\''; Link to comment Share on other sites More sharing options...
rocky Posted August 9, 2016 Share Posted August 9, 2016 The getBestSalesLight function is only used by the "Top sellers block". If you want to change the entire website, you'll need to change the getBestSales function in a similar way. Link to comment Share on other sites More sharing options...
Alexandru Brulea Posted August 9, 2016 Author Share Posted August 9, 2016 (edited) The getBestSalesLight function is only used by the "Top sellers block". If you want to change the entire website, you'll need to change the getBestSales function in a similar way. Hi, It does not work i have put "AND p.quantity > 0" in getBestSales and getBestSalesLight like this and still doesnt work WHERE product_shop.'active' = 1 AND p.quantity > 0 AND p.'visibility' != \'none\''; This two function where i have put p.quantity > 0 are in classes/ProductSale.php Edited August 9, 2016 by Alexandru Brulea (see edit history) Link to comment Share on other sites More sharing options...
rocky Posted August 10, 2016 Share Posted August 10, 2016 Try using the following instead: AND stock.quantity > 0 1 Link to comment Share on other sites More sharing options...
Alexandru Brulea Posted August 10, 2016 Author Share Posted August 10, 2016 Try using the following instead: AND stock.quantity > 0 It works now, but i have put "AND stock.quantity > 0" in "modules/ptspagebuilder/classes/widget/product_list.php" in public static function getBestSalesLight. Hope thats corect. Link to comment Share on other sites More sharing options...
Alexandru Brulea Posted August 10, 2016 Author Share Posted August 10, 2016 Can you please tell me if i want the entire site to work on stock.quantity > 0 ? where should i change ? Link to comment Share on other sites More sharing options...
rocky Posted August 10, 2016 Share Posted August 10, 2016 Everything in the Front Office and Back Office should be using the ProductSale class to get best sellers, so changing every function in that file should work. If you have third-party code that's not using that class, then you'll need to update those queries too. Link to comment Share on other sites More sharing options...
Alexandru Brulea Posted August 10, 2016 Author Share Posted August 10, 2016 Everything in the Front Office and Back Office should be using the ProductSale class to get best sellers, so changing every function in that file should work. If you have third-party code that's not using that class, then you'll need to update those queries too. The ideea is that whatever i modify in ProductSale.php from classes/ProductSale.php has no effect and the class from ProductSale.php is called ProductSaleCore. As i said earlier i have made modifications into modules/ptspagebuilder/classes/widget/product_list.php for bestseller. So what is the path to modify the query or query's where i should modify and add "AND stock.quantity > 0". Link to comment Share on other sites More sharing options...
rocky Posted August 10, 2016 Share Posted August 10, 2016 So it's not reading the override? Did you delete cache/class_index.php? I'll paste my override in case it helps: <?php class ProductSale extends ProductSaleCore { public static function getBestSales($id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null) { $context = Context::getContext(); if ($page_number < 0) { $page_number = 0; } if ($nb_products < 1) { $nb_products = 10; } $final_order_by = $order_by; $order_table = ''; if (is_null($order_by)) { $order_by = 'quantity'; $order_table = 'ps'; } if ($order_by == 'date_add' || $order_by == 'date_upd') { $order_table = 'product_shop'; } if (is_null($order_way) || $order_by == 'sales') { $order_way = 'DESC'; } $interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20; // no group by needed : there's only one attribute with default_on=1 for a given id_product + shop // same for image with cover=1 $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, '.(Combination::isFeatureActive()?'product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity,IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute,':'').' pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer, image_shop.`id_image` id_image, il.`legend`, ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`, DATEDIFF(p.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$interval.' DAY)) > 0 AS new' .' FROM `'._DB_PREFIX_.'product_sale` ps LEFT JOIN `'._DB_PREFIX_.'product` p ON ps.`id_product` = p.`id_product` '.Shop::addSqlAssociation('product', 'p', false); if (Combination::isFeatureActive()) { $sql .= ' 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.')'; } $sql .= ' 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_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`) AND tr.`id_country` = '.(int)$context->country->id.' AND tr.`id_state` = 0 LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`) '.Product::sqlStock('p', 0); $sql .= ' WHERE product_shop.`active` = 1 AND stock.quantity > 0 AND p.`visibility` != \'none\''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql .= ' AND EXISTS(SELECT 1 FROM `'._DB_PREFIX_.'category_product` cp JOIN `'._DB_PREFIX_.'category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').') WHERE cp.`id_product` = p.`id_product`)'; } if ($final_order_by != 'price') { $sql .= ' ORDER BY '.(!empty($order_table) ? '`'.pSQL($order_table).'`.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).' LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products; } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if ($final_order_by == 'price') { Tools::orderbyPrice($result, $order_way); } if (!$result) { return false; } return Product::getProductsProperties($id_lang, $result); } /* ** Get required informations on best sales products ** ** @param int $id_lang Language id ** @param int $page_number Start from (optional) ** @param int $nb_products Number of products to return (optional) ** @return array keys : id_product, link_rewrite, name, id_image, legend, sales, ean13, upc, link */ public static function getBestSalesLight($id_lang, $page_number = 0, $nb_products = 10, Context $context = null) { if (!$context) { $context = Context::getContext(); } if ($page_number < 0) { $page_number = 0; } if ($nb_products < 1) { $nb_products = 10; } // no group by needed : there's only one attribute with default_on=1 for a given id_product + shop // same for image with cover=1 $sql = ' SELECT p.id_product, IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`, image_shop.`id_image` id_image, il.`legend`, ps.`quantity` AS sales, p.`ean13`, p.`upc`, cl.`link_rewrite` AS category, p.show_price, p.available_for_order, IFNULL(stock.quantity, 0) as quantity, p.customizable, IFNULL(pa.minimal_quantity, p.minimal_quantity) as minimal_quantity, stock.out_of_stock, product_shop.`date_add` > "'.date('Y-m-d', strtotime('-'.(Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int)Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY')).'" as new, product_shop.`on_sale`, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity FROM `'._DB_PREFIX_.'product_sale` ps LEFT JOIN `'._DB_PREFIX_.'product` p ON ps.`id_product` = p.`id_product` '.Shop::addSqlAssociation('product', 'p').' 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.') LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (product_attribute_shop.id_product_attribute=pa.id_product_attribute) 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_.'category_lang` cl ON cl.`id_category` = product_shop.`id_category_default` AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').Product::sqlStock('p', 0); $sql .= ' WHERE product_shop.`active` = 1 AND stock.quantity > 0 AND p.`visibility` != \'none\''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql .= ' AND EXISTS(SELECT 1 FROM `'._DB_PREFIX_.'category_product` cp JOIN `'._DB_PREFIX_.'category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').') WHERE cp.`id_product` = p.`id_product`)'; } $sql .= ' ORDER BY ps.quantity DESC LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products; if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql)) { return false; } return Product::getProductsProperties($id_lang, $result); } } Link to comment Share on other sites More sharing options...
Alexandru Brulea Posted August 10, 2016 Author Share Posted August 10, 2016 So it's not reading the override? Did you delete cache/class_index.php? I'll paste my override in case it helps: <?php class ProductSale extends ProductSaleCore { public static function getBestSales($id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null) { $context = Context::getContext(); if ($page_number < 0) { $page_number = 0; } if ($nb_products < 1) { $nb_products = 10; } $final_order_by = $order_by; $order_table = ''; if (is_null($order_by)) { $order_by = 'quantity'; $order_table = 'ps'; } if ($order_by == 'date_add' || $order_by == 'date_upd') { $order_table = 'product_shop'; } if (is_null($order_way) || $order_by == 'sales') { $order_way = 'DESC'; } $interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20; // no group by needed : there's only one attribute with default_on=1 for a given id_product + shop // same for image with cover=1 $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, '.(Combination::isFeatureActive()?'product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity,IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute,':'').' pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer, image_shop.`id_image` id_image, il.`legend`, ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`, DATEDIFF(p.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$interval.' DAY)) > 0 AS new' .' FROM `'._DB_PREFIX_.'product_sale` ps LEFT JOIN `'._DB_PREFIX_.'product` p ON ps.`id_product` = p.`id_product` '.Shop::addSqlAssociation('product', 'p', false); if (Combination::isFeatureActive()) { $sql .= ' 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.')'; } $sql .= ' 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_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`) AND tr.`id_country` = '.(int)$context->country->id.' AND tr.`id_state` = 0 LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`) '.Product::sqlStock('p', 0); $sql .= ' WHERE product_shop.`active` = 1 AND stock.quantity > 0 AND p.`visibility` != \'none\''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql .= ' AND EXISTS(SELECT 1 FROM `'._DB_PREFIX_.'category_product` cp JOIN `'._DB_PREFIX_.'category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').') WHERE cp.`id_product` = p.`id_product`)'; } if ($final_order_by != 'price') { $sql .= ' ORDER BY '.(!empty($order_table) ? '`'.pSQL($order_table).'`.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).' LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products; } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if ($final_order_by == 'price') { Tools::orderbyPrice($result, $order_way); } if (!$result) { return false; } return Product::getProductsProperties($id_lang, $result); } /* ** Get required informations on best sales products ** ** @param int $id_lang Language id ** @param int $page_number Start from (optional) ** @param int $nb_products Number of products to return (optional) ** @return array keys : id_product, link_rewrite, name, id_image, legend, sales, ean13, upc, link */ public static function getBestSalesLight($id_lang, $page_number = 0, $nb_products = 10, Context $context = null) { if (!$context) { $context = Context::getContext(); } if ($page_number < 0) { $page_number = 0; } if ($nb_products < 1) { $nb_products = 10; } // no group by needed : there's only one attribute with default_on=1 for a given id_product + shop // same for image with cover=1 $sql = ' SELECT p.id_product, IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`, image_shop.`id_image` id_image, il.`legend`, ps.`quantity` AS sales, p.`ean13`, p.`upc`, cl.`link_rewrite` AS category, p.show_price, p.available_for_order, IFNULL(stock.quantity, 0) as quantity, p.customizable, IFNULL(pa.minimal_quantity, p.minimal_quantity) as minimal_quantity, stock.out_of_stock, product_shop.`date_add` > "'.date('Y-m-d', strtotime('-'.(Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int)Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY')).'" as new, product_shop.`on_sale`, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity FROM `'._DB_PREFIX_.'product_sale` ps LEFT JOIN `'._DB_PREFIX_.'product` p ON ps.`id_product` = p.`id_product` '.Shop::addSqlAssociation('product', 'p').' 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.') LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (product_attribute_shop.id_product_attribute=pa.id_product_attribute) 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_.'category_lang` cl ON cl.`id_category` = product_shop.`id_category_default` AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').Product::sqlStock('p', 0); $sql .= ' WHERE product_shop.`active` = 1 AND stock.quantity > 0 AND p.`visibility` != \'none\''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql .= ' AND EXISTS(SELECT 1 FROM `'._DB_PREFIX_.'category_product` cp JOIN `'._DB_PREFIX_.'category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').') WHERE cp.`id_product` = p.`id_product`)'; } $sql .= ' ORDER BY ps.quantity DESC LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products; if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql)) { return false; } return Product::getProductsProperties($id_lang, $result); } } Yes i have deleted the cache/class_index.php but still nothing, i have empty the content from lasses/ProductSale.php where is class ProductSaleCore and put print '111' and nothing. As i said i have fallow your instructions with AND stock.quantity > 0 and the only difference is that i have modify the query from here modules/ptspagebuilder/classes/widget/product_list.php and in there i have no getBestSales function.The code that u have provided above is found in ProductSale.php but nothing that i change works, i have even remaned this ProductSale.php file and still the site works fine. Link to comment Share on other sites More sharing options...
rocky Posted August 10, 2016 Share Posted August 10, 2016 Sorry, I'm not familiar with the ptspagebuilder module. It must be querying the database directly instead of calling ProductSale. You'll need to look through the module and see how it works and modify its queries. Link to comment Share on other sites More sharing options...
Alexandru Brulea Posted August 10, 2016 Author Share Posted August 10, 2016 Sorry, I'm not familiar with the ptspagebuilder module. It must be querying the database directly instead of calling ProductSale. You'll need to look through the module and see how it works and modify its queries. Thanks mate for the help so far. 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