Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Integrity constraint violation: 1052 Column ‘position’ in order clause is ambiguous
 
WMechelle
Member
 
Avatar
Total Posts:  52
Joined:  2009-12-16
 

I am getting this error when I try to use the search.  I have reloaded Magento, and reloaded my database.  I only get this error when I am searching for a product that I know is in the database. It goes to a default error page and gives an error page. --- See code below ---

Any help would be great.
site url: www.saes-online.com or saes-online.com

a:5:{i:0;s:100:"SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘position’ in order clause is ambiguous";i:1;s:3411:"#0 /home/saes/public_html/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/saes/public_html/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /home/saes/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’SELECT DISTINCT...’, Array)
#3 /home/saes/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query(’SELECT DISTINCT...’, Array)
#4 /home/saes/public_html/lib/Zend/Db/Adapter/Abstract.php(706): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#5 /home/saes/public_html/lib/Varien/Data/Collection/Db.php(696): Zend_Db_Adapter_Abstract->fetchAll(Object(Varien_Db_Select), Array)
#6 /home/saes/public_html/lib/Varien/Data/Collection/Db.php(609): Varien_Data_Collection_Db->_fetchAll(Object(Varien_Db_Select))
#7 /home/saes/public_html/lib/Varien/Data/Collection/Db.php(579): Varien_Data_Collection_Db->getData()
#8 /home/saes/public_html/app/code/core/Mage/Core/Model/Mysql4/Collection/Abstract.php(195): Varien_Data_Collection_Db->load(false, false)
#9 /home/saes/public_html/app/code/core/Mage/Catalog/Model/Layer.php(233): Mage_Core_Model_Mysql4_Collection_Abstract->load()
#10 /home/saes/public_html/app/code/core/Mage/Catalog/Block/Layer/View.php(102): Mage_Catalog_Model_Layer->getFilterableAttributes()
#11 /home/saes/public_html/app/code/core/Mage/Catalog/Block/Layer/View.php(63): Mage_Catalog_Block_Layer_View->_getFilterableAttributes()
#12 /home/saes/public_html/app/code/core/Mage/Core/Block/Abstract.php(211): Mage_Catalog_Block_Layer_View->_prepareLayout()
#13 /home/saes/public_html/app/code/core/Mage/Core/Model/Layout.php(457): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#14 /home/saes/public_html/app/code/core/Mage/Core/Model/Layout.php(241): Mage_Core_Model_Layout->addBlock(’Mage_CatalogSea...’, ‘catalogsearch.l...’)
#15 /home/saes/public_html/app/code/core/Mage/Core/Model/Layout.php(207): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#16 /home/saes/public_html/app/code/core/Mage/Core/Model/Layout.php(212): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#17 /home/saes/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(343): Mage_Core_Model_Layout->generateBlocks()
#18 /home/saes/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(270): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#19 /home/saes/public_html/app/code/core/Mage/CatalogSearch/controllers/ResultController.php(77): Mage_Core_Controller_Varien_Action->loadLayout()
#20 /home/saes/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_CatalogSearch_ResultController->indexAction()
#21 /home/saes/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch(’index’)
#22 /home/saes/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(177): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#23 /home/saes/public_html/app/code/core/Mage/Core/Model/App.php(304): Mage_Core_Controller_Varien_Front->dispatch()
#24 /home/saes/public_html/app/Mage.php(596): Mage_Core_Model_App->run(Array)
#25 /home/saes/public_html/index.php(78): Mage::run(’’, ‘store’)
#26 {main}”;s:3:"url”;s:47:"/index.php/catalogsearch/result/?q=nc15&x;=0&y;=0”;s:11:"script_name”;s:10:"/index.php”;s:4:"skin”;s:4:"saes";}

Wendy

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jeremiah
Member
 
Avatar
Total Posts:  34
Joined:  2008-01-10
 

Go to magentoroot/app/code/core/Mage/Catalogue/Model/Layer.php

Do a search for “public function getFilterableAttributes()”

Inside that, look for these lines:

->setAttributeSetFilter($setIds)
->
addStoreLabel(Mage::app()->getStore()->getId())
->
setOrder('position''ASC');

Change to the following:

->setAttributeSetFilter($setIds)
->
addStoreLabel(Mage::app()->getStore()->getId());
//->setOrder('position', 'ASC');

You’ll want to save this and place it in your local folder (/app/code/local/Mage/Catalog/Model) so it doesn’t overwrite core Magento files.
You may have to clear cache.

 
Magento Community Magento Community
Magento Community
Magento Community
 
WMechelle
Member
 
Avatar
Total Posts:  52
Joined:  2009-12-16
 

You are awesome!  Thank you so very much that did the trick. and saves my sites.  Thank you!!

 
Magento Community Magento Community
Magento Community
Magento Community
 
elfling
Enthusiast
 
Avatar
Total Posts:  901
Joined:  2008-10-21
 

Yep, worked here too smile.

 
Magento Community Magento Community
Magento Community
Magento Community
 
bolasevich
Sr. Member
 
Avatar
Total Posts:  235
Joined:  2008-03-04
Newington, CT USA
 

the solution is WAY easier then changing core code. This happens on upgrade because from 1.3.x to 1.4 they change eav_attribute to also have catalog_eav_attribute and when they did this they moved the POSITION field from the eav_attribute to catalog_eav_attribute and this error happens because on upgrade you still have position in the eav_attribute as well as your new catalog_eav_attribute thus it errors because you have 2 columns of the same name and the sql doesn\’t specify it nor should it because really on 1.4 you just want position in the catalog_eav_attribute table

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jeremiah
Member
 
Avatar
Total Posts:  34
Joined:  2008-01-10
 

What is the fix, then?

 
Magento Community Magento Community
Magento Community
Magento Community
 
WMechelle
Member
 
Avatar
Total Posts:  52
Joined:  2009-12-16
 

OK this makes sense but I agree with Jeremiah ..... What is the fix?
THANK YOU!!!!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jeremiah
Member
 
Avatar
Total Posts:  34
Joined:  2008-01-10
 

Oh, I guess what Scott meant to say was that you can use PHPMyAdmin to access your Magento database and alter it for 1.4 friendliness. Look for the table eav_attribute. Drop the “position” column. You should see that error go away.

 
Magento Community Magento Community
Magento Community
Magento Community
 
thesun4you
Jr. Member
 
Total Posts:  11
Joined:  2009-03-14
 

@Jeremiah thank you so much for this I’ve been trying to get my site back to what it was since before i upgraded it to 1.4.1.1 and it went to pooh, for a few days now.  even my host, hostgator, has been helping me with some things. First my categories will no longer show on my menu navigation bar at the top and side, it still won’t, then the search started giving me those fatal errors, but at least you helped me with the search solution so thank you for that. If by chance you know how to help with the first problem please let me know. My categories were working just fine before and I did everything the other post told me to do, but to no avail. Anyway thank you for your contribution it means it a lot.

 
Magento Community Magento Community
Magento Community
Magento Community
 
thesun4you
Jr. Member
 
Total Posts:  11
Joined:  2009-03-14
 

@Jeremiah. Thank you so much for your contribution to the solution of this problem. It really solved one of the issues I was having since I upgraded to 1.4.1.1. For a few days now I’ve been trying to find out how to get my, once showing, categories to display on my top menu bar. I have tried everything on the post but to no avail. But this eased at least some of my new wrinkles. If anyone in this thread by chance knows of a way to show your categories in the top navigational bar please let me know. Please don’t tell me something I already tried like the hierarchy tree of the root category folder. Thank you again Jeremiah.

 
Magento Community Magento Community
Magento Community
Magento Community
 
carroll12
Jr. Member
 
Total Posts:  15
Joined:  2010-10-07
 

I conjecture what Scott meant to say was that you can use PHPMyAdmin to right of entry your Magento database and alter it for 1.4 friendliness. Look for the table eav_attribute. Drop the “position” column.

 
Magento Community Magento Community
Magento Community
Magento Community
 
carroll12
Jr. Member
 
Total Posts:  15
Joined:  2010-10-07
 

I guess what Scott meant to say was that you can use PHPMyAdmin to admission your Magento database and alter it for 1.4 friendliness. give the impression of being for the table eav_attribute. Drop the “location” column. You should see that error go away.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jeremiah
Member
 
Avatar
Total Posts:  34
Joined:  2008-01-10
 

@thesun4you I can\’t assume you\’ve done all of these, so just disregard the ones you\’ve already done.

1) Delete Magento cache and reindex all data
1. Go to System>Index Management
2. Click on “select all” in the upper left corner
3. Under “Action” select “Reindex Data” and click submit

2) Manually delete all files under var/cache and var/sessions.

 
Magento Community Magento Community
Magento Community
Magento Community
 
eComHut
Jr. Member
 
Avatar
Total Posts:  10
Joined:  2011-06-23
Virginia Beach
 
Jeremiah - 03 July 2010 12:18 PM

Go to magentoroot/app/code/core/Mage/Catalogue/Model/Layer.php

Do a search for “public function getFilterableAttributes()”

Inside that, look for these lines:

->setAttributeSetFilter($setIds)
->
addStoreLabel(Mage::app()->getStore()->getId())
->
setOrder('position''ASC');

Change to the following:
->setAttributeSetFilter($setIds)
->
addStoreLabel(Mage::app()->getStore()->getId());
//->setOrder('position', 'ASC');

You’ll want to save this and place it in your local folder (/app/code/local/Mage/Catalog/Model) so it doesn’t overwrite core Magento files.
You may have to clear cache.

Thumbs up dear.

I was trying to figure out the issue from past 3 hours. Your this little help saved my plenty of time.

Once again, thanks

Regards,
Farhan Islam

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top