Jump to content

Block bestsellers module. Show product in block from same category

trace

By trace
in Configuring and using PrestaShop

 Share

Recommended Posts

Scully Newbie

Scully

Posted
Posted

Check the current context within the existing module. Adapt the query to add id_category from the same context the user is in.

With this code, you get the cat id.

$id_category = $this->context->category->id_category;

Use it where the bestsellers are loaded as parameter or as added SQL where statement. I have no code to look into at the moment to find out how bestsellers are queried.

Link to comment
Share on other sites
trace Newbie

trace

Posted
  • Author
Posted

I have no code to look into at the moment to find out how bestsellers are queried.

As far as I understand, the module uses this:

classes/ProductSale.php

 

 

    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`, p.`reference`, 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 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);
    }

 

 

and modules/blockbestsellers/blockbestsellers.php:

 

 

	protected function getBestSellers($params)
	{
		if (Configuration::get('PS_CATALOG_MODE'))
			return false;

		if (!($result = ProductSale::getBestSalesLight((int)$params['cookie']->id_lang, 0, (int)Configuration::get('PS_BLOCK_BESTSELLERS_TO_DISPLAY'))))
			return (Configuration::get('PS_BLOCK_BESTSELLERS_DISPLAY') ? array() : false);

		$currency = new Currency($params['cookie']->id_currency);
		$usetax = (Product::getTaxCalculationMethod((int)$this->context->customer->id) != PS_TAX_EXC);
		foreach ($result as &$row)
			$row['price'] = Tools::displayPrice(Product::getPriceStatic((int)$row['id_product'], $usetax), $currency);

		return $result;
	} 

 

 

Link to comment
Share on other sites
Scully Newbie

Scully

Posted
Posted (edited)

Ok, use the public function above - if it's the right one - get the id needed and add one more where clause.

Insert this right after the first part of the WHERE clause is already set.

$id_category = $context->category->id_category;
if (isset($id_category) and $id_category > 0) {
  $sql .= " AND p.id_category_default = " . (int)$id_category;
}

Code is untested - some work up to you. And also note - this checks only the default category. If you wanted to have the whole category tree checked the code above needed some enhancement.

Edited by Scully (see edit history)
  • Like 1
Link to comment
Share on other sites
trace Newbie

trace

Posted
  • Author
Posted (edited)

Maybe, it is not semantic, but it is works.

I used this topic: https://www.prestashop.com/forums/topic/41945-solved-need-help-editing-new-products-block-pls/page-2

 

.../override/classes/ProductSale.php:

 

 

<?php

class ProductSale extends ProductSaleCore
{

    static public function getBestSalesLightCat($id_lang, $page_number = 0, $nb_products = 10, $id_category = 0, Context $context = null)
	{
            if (!$context)
                    $context = Context::getContext();
            if ($page_number < 0) $page_number = 0;
            if ($nb_products < 1) $nb_products = 10;

            $sql_groups = '';
            if (Group::isFeatureActive())
            {
                    $groups = FrontController::getCurrentCustomerGroups();
                    $sql_groups = 'AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
            }
            //Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage
            $products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
                SELECT cp.`id_product`
                FROM `'._DB_PREFIX_.'category_product` cp
                LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = cp.`id_category`)
                WHERE cg.`id_group` '.$sql_groups.' AND cp.id_category = '.(int)Tools::getValue('id_category'));

            $ids = array();
            foreach ($products as $product)
                    $ids[$product['id_product']] = 1;

            $ids = array_keys($ids);		
            sort($ids);
            $ids = count($ids) > 0 ? implode(',', $ids) : 'NULL';

            //Main query
            $sql = '
                SELECT
                        p.id_product,  MAX(product_attribute_shop.id_product_attribute) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`,
                        MAX(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`
                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` pa
                        ON (p.`id_product` = pa.`id_product`)
                '.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
                '.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
                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` i ON (i.`id_product` = p.`id_product`)'.
                Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
                LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`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').'
                WHERE product_shop.`active` = 1
                AND p.`visibility` != \'none\'
                AND p.`id_product` IN ('.$ids.')
                GROUP BY product_shop.id_product
                ORDER BY sales 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);
	}

    public static function getBestSalesLightProd($id_lang, $pageNumber = 0, $nbProducts = 10, $id_category = 0)
	{
            global $link, $cookie;
            $context = Context::getContext();
            if ($pageNumber < 0) $pageNumber = 0;
            if ($nbProducts < 1) $nbProducts = 10;
            $sql = '
                    SELECT p.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`,
                    MAX(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`, MAX(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` pa
                    ON (p.`id_product` = pa.`id_product`)
                    '.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
                    '.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
                    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` i ON (i.`id_product` = p.`id_product`)'.
                    Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
                    LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`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');
            
                    if (Group::isFeatureActive())
                    {
                        $groups = FrontController::getCurrentCustomerGroups();
                        $sql .= '
                                JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
                                JOIN `'._DB_PREFIX_.'category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').')';
                    }
                    
                    $sql.= '
                            WHERE p.`active` = 1' . ($id_category == 0 ? '' : ' AND p.`id_category_default` = '.$id_category) . '
                            AND p.`id_product` IN (
                                                SELECT cp.`id_product`
                                                FROM `'._DB_PREFIX_.'category_group` cg
                                                LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
                                                WHERE cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (SELECT id_group
                                                FROM '._DB_PREFIX_.'customer_group WHERE id_customer = '.intval($cookie->id_customer).')').')
                                                GROUP BY p.`id_product`
                                                ORDER BY sales DESC
                                                LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts);
            if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))
                    return false;

            return Product::getProductsProperties($id_lang, $result);
    }

}
 

.

and in .../modules/blockbestsellers/blockbestsellers.php - getBestSellers function:

 

 

	protected function getBestSellers($params)
	{
		if (Configuration::get('PS_CATALOG_MODE'))
			return false;

                $id_category = 0;
                $product = new Product((int)Tools::getValue('id_product'));

                if (isset($_GET['id_category']) AND intval($_GET['id_category']) > 0) {
                $category = new Category(intval($_GET['id_category']), intval($params['cookie']->id_lang));
                if (Validate::isLoadedObject($category)) {
                    $id_category = intval($_GET['id_category']);
                }
                if (!($result = ProductSale::getBestSalesLightCat((int)$params['cookie']->id_lang, 0, (int)Configuration::get('PS_BLOCK_BESTSELLERS_TO_DISPLAY'), $id_category)))
                    return (Configuration::get('PS_BLOCK_BESTSELLERS_DISPLAY') ? array() : false);    
                }
                else if (Validate::isLoadedObject($product)) {
                    $id_category_prod = intval($product->id_category_default);
                        if (!($result = ProductSale::getBestSalesLightProd((int)$params['cookie']->id_lang, 0, (int)Configuration::get('PS_BLOCK_BESTSELLERS_TO_DISPLAY'), $id_category_prod)))
                    return (Configuration::get('PS_BLOCK_BESTSELLERS_DISPLAY') ? array() : false);    
                }
                else {
                    if (!($result = ProductSale::getBestSalesLight((int)$params['cookie']->id_lang, 0, (int)Configuration::get('PS_BLOCK_BESTSELLERS_TO_DISPLAY'))))
                    return (Configuration::get('PS_BLOCK_BESTSELLERS_DISPLAY') ? array() : false);
                }

		$currency = new Currency($params['cookie']->id_currency);
		$usetax = (Product::getTaxCalculationMethod((int)$this->context->customer->id) != PS_TAX_EXC);
		foreach ($result as &$row)
			$row['price'] = Tools::displayPrice(Product::getPriceStatic((int)$row['id_product'], $usetax), $currency);

		return $result;
	} 

 

Edited by trace (see edit history)
Link to comment
Share on other sites

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
Go to topic listing
×
×
  • Create New...