php - 违反完整性约束 : 1052 Column 'position' in order clause is ambiguous

Question

我的一个用户从我们自己的 magento 网站向我发送了这个链接。我们有这个异常(exception)，知道我该如何解决这个问题吗？

http://www.theprinterdepox.com/catalogsearch/result/index/?cat=100&q=1022&x=0&y=0

There has been an error processing your request


SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'position' in order clause is ambiguous

Trace:
#0 /home/theprint/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/theprint/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/theprint/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/theprint/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT DISTINCT...', Array)
#4 /home/theprint/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('SELECT DISTINCT...', Array)
#5 /home/theprint/public_html/lib/Zend/Db/Adapter/Abstract.php(791): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 /home/theprint/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php(878): Zend_Db_Adapter_Abstract->fetchCol(Object(Varien_Db_Select))
#7 /home/theprint/public_html/app/code/core/Mage/Catalog/Model/Layer.php(291): Mage_Catalog_Model_Resource_Product_Collection->getSetIds()
#8 /home/theprint/public_html/app/code/core/Mage/Catalog/Model/Layer.php(221): Mage_Catalog_Model_Layer->_getSetIds()
#9 /home/theprint/public_html/app/code/core/Mage/Catalog/Block/Layer/View.php(163): Mage_Catalog_Model_Layer->getFilterableAttributes()
#10 /home/theprint/public_html/app/code/core/Mage/Catalog/Block/Layer/View.php(122): Mage_Catalog_Block_Layer_View->_getFilterableAttributes()
#11 /home/theprint/public_html/app/code/core/Mage/Core/Block/Abstract.php(238): Mage_Catalog_Block_Layer_View->_prepareLayout()
#12 /home/theprint/public_html/app/code/core/Mage/Core/Model/Layout.php(430): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#13 /home/theprint/public_html/app/code/core/Mage/Core/Model/Layout.php(446): Mage_Core_Model_Layout->createBlock('catalogsearch/l...', 'catalogsearch.l...')
#14 /home/theprint/public_html/app/code/core/Mage/Core/Model/Layout.php(238): Mage_Core_Model_Layout->addBlock('catalogsearch/l...', 'catalogsearch.l...')
#15 /home/theprint/public_html/app/code/core/Mage/Core/Model/Layout.php(204): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#16 /home/theprint/public_html/app/code/core/Mage/Core/Model/Layout.php(209): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#17 /home/theprint/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(345): Mage_Core_Model_Layout->generateBlocks()
#18 /home/theprint/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(270): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#19 /home/theprint/public_html/app/code/core/Mage/CatalogSearch/controllers/ResultController.php(77): Mage_Core_Controller_Varien_Action->loadLayout()
#20 /home/theprint/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(420): Mage_CatalogSearch_ResultController->indexAction()
#21 /home/theprint/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
#22 /home/theprint/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#23 /home/theprint/public_html/app/code/core/Mage/Core/Model/App.php(347): Mage_Core_Controller_Varien_Front->dispatch()
#24 /home/theprint/public_html/app/Mage.php(640): Mage_Core_Model_App->run(Array)
#25 /home/theprint/public_html/www.theprinterdepo.com/index.php(55): Mage::run('printerdepo', 'website')
#26 {main}

Data.php

<?php
class Mage_CatalogSearch_Helper_Data extends Mage_Core_Helper_Abstract
{
    const QUERY_VAR_NAME = 'q';
    const MAX_QUERY_LEN  = 200;

    /**
     * Query object
     *
     * @var Mage_CatalogSearch_Model_Query
     */
    protected $_query;

    /**
     * Query string
     *
     * @var string
     */
    protected $_queryText;

    /**
     * Note messages
     *
     * @var array
     */
    protected $_messages = array();

    /**
     * Is a maximum length cut
     *
     * @var bool
     */
    protected $_isMaxLength = false;

    /**
     * Search engine model
     *
     * @var Mage_CatalogSearch_Model_Resource_Fulltext_Engine
     */
    protected $_engine;

    /**
     * Retrieve search query parameter name
     *
     * @return string
     */
    public function getQueryParamName()
    {
        return self::QUERY_VAR_NAME;
    }

    /**
     * Retrieve query model object
     *
     * @return Mage_CatalogSearch_Model_Query
     */
    public function getQuery()
    {
        if (!$this->_query) {
            $this->_query = Mage::getModel('catalogsearch/query')
                ->loadByQuery($this->getQueryText());
            if (!$this->_query->getId()) {
                $this->_query->setQueryText($this->getQueryText());
            }
        }
        return $this->_query;
    }

    /**
     * Is a minimum query length
     *
     * @return bool
     */
    public function isMinQueryLength()
    {
        if (Mage::helper('core/string')->strlen($this->getQueryText()) < $this->getMinQueryLength()) {
            return true;
        }
        return false;
    }

    /**
     * Retrieve search query text
     *
     * @return string
     */
    public function getQueryText()
    {
        if (is_null($this->_queryText)) {
            $this->_queryText = $this->_getRequest()->getParam($this->getQueryParamName());
            if ($this->_queryText === null) {
                $this->_queryText = '';
            } else {
                if (is_array($this->_queryText)) {
                    $this->_queryText = null;
                }
                $this->_queryText = trim($this->_queryText);
                $this->_queryText = Mage::helper('core/string')->cleanString($this->_queryText);

                if (Mage::helper('core/string')->strlen($this->_queryText) > $this->getMaxQueryLength()) {
                    $this->_queryText = Mage::helper('core/string')->substr(
                        $this->_queryText,
                        0,
                        $this->getMaxQueryLength()
                    );
                    $this->_isMaxLength = true;
                }
            }
        }
        return $this->_queryText;
    }

    /**
     * Retrieve HTML escaped search query
     *
     * @return string
     */
    public function getEscapedQueryText()
    {
        return $this->htmlEscape($this->getQueryText());
    }

    /**
     * Retrieve suggest collection for query
     *
     * @return Mage_CatalogSearch_Model_Resource_Query_Collection
     */
    public function getSuggestCollection()
    {
        return $this->getQuery()->getSuggestCollection();
    }

    /**
     * Retrieve result page url and set "secure" param to avoid confirm
     * message when we submit form from secure page to unsecure
     *
     * @param   string $query
     * @return  string
     */
    public function getResultUrl($query = null)
    {
        return $this->_getUrl('catalogsearch/result', array(
            '_query' => array(self::QUERY_VAR_NAME => $query),
            '_secure' => Mage::app()->getFrontController()->getRequest()->isSecure()
        ));
    }

    /**
     * Retrieve suggest url
     *
     * @return string
     */
    public function getSuggestUrl()
    {
        return $this->_getUrl('catalogsearch/ajax/suggest', array(
            '_secure' => Mage::app()->getFrontController()->getRequest()->isSecure()
        ));
    }

    /**
     * Retrieve search term url
     *
     * @return string
     */
    public function getSearchTermUrl()
    {
        return $this->_getUrl('catalogsearch/term/popular');
    }

    /**
     * Retrieve advanced search URL
     *
     * @return string
     */
    public function getAdvancedSearchUrl()
    {
        return $this->_getUrl('catalogsearch/advanced');
    }

    /**
     * Retrieve minimum query length
     *
     * @param mixed $store
     * @return int
     */
    public function getMinQueryLength($store = null)
    {
        return Mage::getStoreConfig(Mage_CatalogSearch_Model_Query::XML_PATH_MIN_QUERY_LENGTH, $store);
    }

    /**
     * Retrieve maximum query length
     *
     * @param mixed $store
     * @return int
     */
    public function getMaxQueryLength($store = null)
    {
        return Mage::getStoreConfig(Mage_CatalogSearch_Model_Query::XML_PATH_MAX_QUERY_LENGTH, $store);
    }

    /**
     * Retrieve maximum query words count for like search
     *
     * @param mixed $store
     * @return int
     */
    public function getMaxQueryWords($store = null)
    {
        return Mage::getStoreConfig(Mage_CatalogSearch_Model_Query::XML_PATH_MAX_QUERY_WORDS, $store);
    }

    /**
     * Add Note message
     *
     * @param string $message
     * @return Mage_CatalogSearch_Helper_Data
     */
    public function addNoteMessage($message)
    {
        $this->_messages[] = $message;
        return $this;
    }

    /**
     * Set Note messages
     *
     * @param array $messages
     * @return Mage_CatalogSearch_Helper_Data
     */
    public function setNoteMessages(array $messages)
    {
        $this->_messages = $messages;
        return $this;
    }

    /**
     * Retrieve Current Note messages
     *
     * @return array
     */
    public function getNoteMessages()
    {
        return $this->_messages;
    }

    /**
     * Check query of a warnings
     *
     * @param mixed $store
     * @return Mage_CatalogSearch_Helper_Data
     */
    public function checkNotes($store = null)
    {
        if ($this->_isMaxLength) {
            $this->addNoteMessage($this->__('Maximum Search query  length is %s. Your query was cut.', $this->getMaxQueryLength()));
        }

        $stringHelper = Mage::helper('core/string');
        /* @var $stringHelper Mage_Core_Helper_String */

        $searchType = Mage::getStoreConfig(Mage_CatalogSearch_Model_Fulltext::XML_PATH_CATALOG_SEARCH_TYPE);
        if ($searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE ||
            $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_LIKE) {

            $wordsFull = $stringHelper->splitWords($this->getQueryText(), true);
            $wordsLike = $stringHelper->splitWords($this->getQueryText(), true, $this->getMaxQueryWords());

            if (count($wordsFull) > count($wordsLike)) {
                $wordsCut = array_diff($wordsFull, $wordsLike);

                $wordsCut = array_map(array($this, 'htmlEscape'), $wordsCut);
                $this->addNoteMessage(
                    $this->__('Maximum words count is %1$s. In your search query was cut next part: %2$s.',
                        $this->getMaxQueryWords(),
                        join(' ', $wordsCut)
                    )
                );
            }
        }
    }

    /**
     * Join index array to string by separator
     * Support 2 level array gluing
     *
     * @param array $index
     * @param string $separator
     * @return string
     */
    public function prepareIndexdata($index, $separator = ' ')
    {
        $_index = array();
        foreach ($index as $key => $value) {
            if (!is_array($value)) {
                $_index[] = $value;
            }
            else {
                $_index = array_merge($_index, $value);
            }
        }
        return join($separator, $_index);
    }

    /**
     * Get current search engine resource model
     *
     * @return object
     */
    public function getEngine()
    {
        if (!$this->_engine) {
            $engine = Mage::getStoreConfig('catalog/search/engine');

            /**
             * This needed if there already was saved in configuration some none-default engine
             * and module of that engine was disabled after that.
             * Problem is in this engine in database configuration still set.
             */
            if ($engine && Mage::getConfig()->getResourceModelClassName($engine)) {
                $model = Mage::getResourceSingleton($engine);
                if ($model && $model->test()) {
                    $this->_engine = $model;
                }
            }
            if (!$this->_engine) {
                $this->_engine = Mage::getResourceSingleton('catalogsearch/fulltext_engine');
            }
        }

        return $this->_engine;
    }
}

ResultController.php

<?php

class Mage_CatalogSearch_ResultController extends Mage_Core_Controller_Front_Action
{
    /**
     * Retrieve catalog session
     *
     * @return Mage_Catalog_Model_Session
     */
    protected function _getSession()
    {
        return Mage::getSingleton('catalog/session');
    }
    /**
     * Display search result
     */
    public function indexAction()
    {
        $query = Mage::helper('catalogsearch')->getQuery();
        /* @var $query Mage_CatalogSearch_Model_Query */

        $query->setStoreId(Mage::app()->getStore()->getId());

        if ($query->getQueryText()) {
            if (Mage::helper('catalogsearch')->isMinQueryLength()) {
                $query->setId(0)
                    ->setIsActive(1)
                    ->setIsProcessed(1);
            }
            else {
                if ($query->getId()) {
                    $query->setPopularity($query->getPopularity()+1);
                }
                else {
                    $query->setPopularity(1);
                }

                if ($query->getRedirect()){
                    $query->save();
                    $this->getResponse()->setRedirect($query->getRedirect());
                    return;
                }
                else {
                    $query->prepare();
                }
            }

            Mage::helper('catalogsearch')->checkNotes();

            $this->loadLayout();
            $this->_initLayoutMessages('catalog/session');
            $this->_initLayoutMessages('checkout/session');
            $this->renderLayout();

            if (!Mage::helper('catalogsearch')->isMinQueryLength()) {
                $query->save();
            }
        }
        else {
            $this->_redirectReferer();
        }
    }
}

Answer 1

错误表明在您的搜索查询中有多个表将“position”作为列名。

您应该做的是打印出查询和分析。

1- 打开文件:/home/theprint/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php(第 878 行)
2- 在 return 语句之前的方法 getAllIds($limit = null, $offset = null) 中。

/* remember to make this output to you only */echo $idsSelect; /* print out the query string */var_dump($this->_bindParams); /* query parameters values */die;

3- 然后将查询发布到您的问题中。