Best seller - Prestashop 1.5.4.1 not visible

[JAISH]

Hi guys,

 

I hope you can help me. I am using prestashop 1.5.4.1 and the 'best seller' page shows errors. 

 

 

The url to my webpage :   http://www.zoschattig.com/best-sales

 

Please advise me guys....

 

The error message:

 

[PrestaShopDatabaseException]

Column 'date_upd' in order clause is ambiguous
 

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
                    pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
                    pl.`meta_keywords`, pl.`meta_title`, pl.`name`,
                    m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer,
                    MAX(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(NOW(),
                    INTERVAL 0 DAY)) > 0 AS new
                FROM `ps_product_sale` ps
                LEFT JOIN `ps_product` p ON ps.`id_product` = p.`id_product`
                 LEFT JOIN ps_product_shop product_shop
        ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
                LEFT JOIN `ps_product_lang` pl
                    ON p.`id_product` = pl.`id_product`
                    AND pl.`id_lang` = 1 AND pl.id_shop = 1
                LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop
        ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)
                LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1)
                LEFT JOIN `ps_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
                LEFT JOIN `ps_tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`)
                    AND tr.`id_country` = 245
                    AND tr.`id_state` = 0
                LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)
                 LEFT
            JOIN ps_stock_available stock
            ON (stock.id_product = p.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1 )
                WHERE product_shop.`active` = 1
                    AND p.`visibility` != 'none'
                    AND p.`id_product` IN (
                        SELECT cp.`id_product`
                        FROM `ps_category_group` cg
                        LEFT JOIN `ps_category_product` cp ON (cp.`id_category` = cg.`id_category`)
                        WHERE cg.`id_group` = 1
                    )
                GROUP BY product_shop.id_product
                ORDER BY `date_upd` desc
                LIMIT 0, 6
at line 607 in file classes/db/Db.php

 

601.             WebserviceRequest::getInstance()->setError(500, '

 '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
602.         }
603.         else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
604.         {
605.             if ($sql)
606.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
607.             throw new PrestaShopDatabaseException($this->getMsgError());
608.         }
609.     }
610.
611.     /**

• DbCore->displayError - [line 307 - classes/db/Db.php] - [1 Arguments]
• DbCore->query - [line 482 - classes/db/Db.php] - [1 Arguments]
• DbCore->executeS - [line 110 - classes/ProductSale.php] - [1 Arguments]
• ProductSaleCore::getBestSales - [line 40 - controllers/front/BestSalesController.php] - [5 Arguments]
• BestSalesControllerCore->initContent - [line 167 - classes/controller/Controller.php] - [0 Argument]
• ControllerCore->run - [line 349 - classes/Dispatcher.php] - [0 Argument]
• DispatcherCore->dispatch - [line 28 - index.php] - [0 Argument]

[Ittu]

You need to replace ORDER BY `date_upd` desc as ORDER BY p.date_upd desc in query.

[JAISH]

Dear Ittu,

 

Let me first of all thank you!

 

Could please guide me where i can find the query and how i can change this?

 

Rgds

[Ittu]

Please go on ROOT_OF_PRESTASHOP/classes folder and edit ProductSale.php file and find "getBestSales" function

find $order_table = ''; (approx line 69)

replace it with $order_table = 'product_shop';

then your issue will be solved.

[Ittu]

when your issue will be solve then make it [solved]

[JAISH]

Dear Ittu,

 

Thanks for so far!

 

I cannot find the code: $order_table = ''; 

 

see below:

 

public static function getBestSales($id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null)

{

if ($page_number < 0) $page_number = 0;

if ($nb_products < 1) $nb_products = 10;

$final_order_by = $order_by;

if (is_null($order_by) || $order_by == 'position' || $order_by == 'price') $order_by = 'sales';

if (is_null($order_way) || $order_by == 'sales') $order_way == 'DESC';

$groups = FrontController::getCurrentCustomerGroups();

$sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');

$interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;

[Ittu]

What is your prestashop version ?

[JAISH]

The version is 1.5.4.1

 

Rgds

The version is 1.5.4.1

 

Rgds

[Ittu]

Ok, then find this code "if (is_null($order_by) || $order_by == 'position' || $order_by == 'price') $order_by = 'sales';"

and replace it with

 

$order_table = '';         
if (is_null($order_by) || $order_by == 'position' || $order_by == 'price') $order_by = 'sales';
if ($order_by == 'date_add' || $order_by == 'date_upd')
       $order_table = 'product_shop';

 

and find this code ORDER BY `'.pSQL($order_by).'` '.pSQL($order_way).' and replace it with

ORDER BY '.(!empty($order_table) ? '`'.pSQL($order_table).'`.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'

 

Its all in getBestSales function in ProductSale.php file.

[Ittu]

Hi,

 

I see your site and it is working now. So your issue is resolve ?

[JAISH]

Hi Ittu,

 

Thanks for the great help!!

 

 

Rgds