Bad SQL query Column 'quantity' in order clause is ambiguous

[markjenkins]

Hi

please can someone help I was doing some work in my products and was selecting products to change quanities, when I went to go back into products I get the following error

• Bad SQL query
  Column 'quantity' in order clause is ambiguous

I have logged out and back in but keep getting this message now

My product settings has display products by quantity as default in settings.

Really appreciate any help

 

[vekia]

please turn on error reporting and show FULL error code (with sql query)

the problem is easy to fix, but i need sql query first.

[1sweetman]

Hi I am having the same problem as Mark above......this is what it shows me.....any help gratefully accepted.

 

[PrestaShopDatabaseException]

Column 'quantity' in order clause is ambiguous
 

		SELECT SQL_CALC_FOUND_ROWS		a.`id_product`,b.name as name,`reference`,a.price as price,sa.active as active		, shop.name as shopname, MAX(image_shop.id_image) id_image, cl.name `name_category`, sa.`price`, 0 AS price_final, sav.`quantity` as sav_quantity, sa.`active`		FROM `ps_product` a		LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1)				LEFT JOIN `ps_image` i ON (i.`id_product` = a.`id_product`)		LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0		 AND sav.id_shop = 1 )  JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default)				LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default)				LEFT JOIN `ps_shop` shop ON (shop.id_shop = a.id_shop_default) 				LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = a.id_shop_default) 				WHERE 1 		GROUP BY sa.id_product 				ORDER BY quantity desc LIMIT 0,300

at line 613 in file classes/db/Db.php

607. 			WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);608. 		}609. 		else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))610. 		{611. 			if ($sql)612. 				throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');613. 			throw new PrestaShopDatabaseException($this->getMsgError());614. 		}615. 	}616. 617. 	/**

• DbCore->displayError - [line 313 - classes/db/Db.php] - [1 Arguments]
• DbCore->query - [line 488 - classes/db/Db.php] - [1 Arguments]
• DbCore->executeS - [line 2292 - classes/controller/AdminController.php] - [1 Arguments]
• AdminControllerCore->getList - [line 328 - controllers/admin/AdminProductsController.php] - [6 Arguments]
• AdminProductsControllerCore->getList - [line 1604 - classes/controller/AdminController.php] - [1 Arguments]
• AdminControllerCore->renderList - [line 2294 - controllers/admin/AdminProductsController.php] - [0 Argument]
• AdminProductsControllerCore->renderList - [line 1500 - classes/controller/AdminController.php] - [0 Argument]
• AdminControllerCore->initContent - [line 2286 - controllers/admin/AdminProductsController.php] - [0 Argument]
• AdminProductsControllerCore->initContent - [line 167 - classes/controller/Controller.php] - [0 Argument]
• ControllerCore->run - [line 348 - classes/Dispatcher.php] - [0 Argument]
• DispatcherCore->dispatch - [line 53 - Stuart/index.php] - [0 Argument]

 

 

Thanks in advance for your assistance

[tuk66]

This is a bug. Just don't sort by quantity.

 

Which PrestaShop version?

[1sweetman]

wow thanks for that helpful bit of advice.......if I didn't need to search by quantity I wouldn't.

 

1.5.6

[gjanssens]

Hi,

I had the same problem.
I know the Query should end with ORDER BY sav.quantity desc LIMIT 0,300, But I don't know how to correct this...

Anyway, you can clear your cookies (who store your preferred orderBy parameters) and it should work then.
Does it? (work for me)

Edited December 13, 2013 by gjanssens (see edit history)

[vekia]

Hi,

I had the same problem.

I know the Query should and with ORDER BY sav.quantity desc LIMIT 0,300, But I don't know how to correct this...

Anyway, you can clear your cookies (who store your preferred orderBy parameters) and it should work then.

Does it? (work for me)

 

the question is: in what query you've got this problem?

[gjanssens]

I personally got this error by trying to access product list in BO.
I didn't backup the debug trace, but It was the same as 1sweetman one