I have a Magento CE-1.9.2.4 installation and when accessing category lists sometimes I get this error when clicking on the color filter or size filter:
You cannot define a correlation name 'size_idx' more than once
#0 /lib/Varien/Db/Select.php(281): Zend_Db_Select->_join('inner join', Array, 'size_idx.entity...', Array, NULL)
#1 /app/code/core/Zend/Db/Select.php(352): Varien_Db_Select->_join('inner join', Array, 'size_idx.entity...', Array, NULL)
#2 /app/code/core/Zend/Db/Select.php(331): Zend_Db_Select->joinInner(Array, 'size_idx.entity...', Array, NULL)
#3 /app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Attribute.php(70): Zend_Db_Select->join(Array, 'size_idx.entity...', Array)
#4 /app/code/core/Mage/Catalog/Model/Layer/Filter/Attribute.php(94): Mage_Catalog_Model_Resource_Layer_Filter_Attribute->applyFilterToCollection(Object(Mage_Catalog_Model_Layer_Filter_Attribute), '14')
#5 /app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(91): Mage_Catalog_Model_Layer_Filter_Attribute->apply(Object(Mage_Core_Controller_Request_Http), Object(Mage_Catalog_Block_Layer_Filter_Attribute))
#6 /app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(73): Mage_Catalog_Block_Layer_Filter_Abstract->_initFilter()
#7 /app/code/core/Mage/Catalog/Block/Layer/View.php(136): Mage_Catalog_Block_Layer_Filter_Abstract->init()
#8 /app/code/core/Mage/Core/Block/Abstract.php(293): Mage_Catalog_Block_Layer_View->_prepareLayout()
#9 /app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#10 /app/code/core/Mage/Core/Model/Layout.php(472): Mage_Core_Model_Layout->createBlock('catalog/layer_v...', 'catalog.leftnav')
#11 /app/code/core/Mage/Core/Model/Layout.php(239): Mage_Core_Model_Layout->addBlock('catalog/layer_v...', 'catalog.leftnav')
#12 /app/code/core/Mage/Core/Model/Layout.php(205): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#13 /app/code/core/Mage/Core/Model/Layout.php(210): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#14 /app/code/core/Mage/Core/Controller/Varien/Action.php(344): Mage_Core_Model_Layout->generateBlocks()
#15 /app/code/core/Mage/Catalog/controllers/CategoryController.php(148): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#16 /app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Catalog_CategoryController->viewAction()
#17 /app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('view')
#18 /app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#19 /app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#20 /app/Mage.php(684): Mage_Core_Model_App->run(Array)
#21 /index.php(83): Mage::run('', 'store')
#22 {main}
I have read the following posts:
- You cannot define a correlation name 'enable_googlecheckout_idx' more than once
- https://stackoverflow.com/questions/10524017/magento-layered-navigation-you-cannot-define-a-correlation-name-mycustomattrib
- https://support.amasty.com/index.php?/Knowledgebase/Article/View/12/5/you-cannot-define-a-correlation-name-_idx-more-than-once
and searched my installation for type="catalog/layer_view
to see if there is more than once in the same .xml file (there isn't).
The most "strange" thing is that when I refresh cache from:
Magento Admin Panel > System > Cache Management
the error disappears!
Is there something else I must look for?
UPDATE (2016-12-08)
I have replaced the code inside the file
/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Attribute.php
with this
/**
* Magento
*
* NOTICE OF LICENSE
*
* This source file is subject to the Open Software License (OSL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/osl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to license@magento.com so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade Magento to newer
* versions in the future. If you wish to customize Magento for your
* needs please refer to http://www.magento.com for more information.
*
* @category Mage
* @package Mage_Catalog
* @copyright Copyright (c) 2006-2016 X.commerce, Inc. and affiliates (http://www.magento.com)
* @license http://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0)
*/
/**
* Catalog Layer Attribute Filter Resource Model
*
* @category Mage
* @package Mage_Catalog
* @author Magento Core Team <core@magentocommerce.com>
*/
class Mage_Catalog_Model_Resource_Layer_Filter_Attribute extends Mage_Core_Model_Resource_Db_Abstract
{
/**
* Initialize connection and define main table name
*
*/
protected function _construct()
{
$this->_init('catalog/product_index_eav', 'entity_id');
}
/**
* Apply attribute filter to product collection
*
* @param Mage_Catalog_Model_Layer_Filter_Attribute $filter
* @param int $value
* @return Mage_Catalog_Model_Resource_Layer_Filter_Attribute
*/
public function applyFilterToCollection($filter, $value)
{
$filterSingleton = FilterSingleton::singleton();
if (!isset($filterSingleton->return)) {
$collection = $filter->getLayer()->getProductCollection();
$attribute = $filter->getAttributeModel();
$connection = $this->_getReadAdapter();
$tableAlias = $attribute->getAttributeCode() . '_idx';
$conditions = array(
"{$tableAlias}.entity_id = e.entity_id",
$connection->quoteInto("{$tableAlias}.attribute_id = ?", $attribute->getAttributeId()),
$connection->quoteInto("{$tableAlias}.store_id = ?", $collection->getStoreId()),
$connection->quoteInto("{$tableAlias}.value = ?", $value)
);
$collection->getSelect()->join(
array($tableAlias => $this->getMainTable()),
join(' AND ', $conditions),
array()
);
$filterSingleton->return = $this;
return $this;
} else {
return $filterSingleton->return;
}
}
/**
* Retrieve array with products counts per attribute option
*
* @param Mage_Catalog_Model_Layer_Filter_Attribute $filter
* @return array
*/
public function getCount($filter)
{
// clone select from collection with filters
$select = clone $filter->getLayer()->getProductCollection()->getSelect();
// reset columns, order and limitation conditions
$select->reset(Zend_Db_Select::COLUMNS);
$select->reset(Zend_Db_Select::ORDER);
$select->reset(Zend_Db_Select::LIMIT_COUNT);
$select->reset(Zend_Db_Select::LIMIT_OFFSET);
$connection = $this->_getReadAdapter();
$attribute = $filter->getAttributeModel();
$tableAlias = sprintf('%s_idx', $attribute->getAttributeCode());
$conditions = array(
"{$tableAlias}.entity_id = e.entity_id",
$connection->quoteInto("{$tableAlias}.attribute_id = ?", $attribute->getAttributeId()),
$connection->quoteInto("{$tableAlias}.store_id = ?", $filter->getStoreId()),
);
$select
->join(
array($tableAlias => $this->getMainTable()),
join(' AND ', $conditions),
array('value', 'count' => new Zend_Db_Expr("COUNT({$tableAlias}.entity_id)")))
->group("{$tableAlias}.value");
return $connection->fetchPairs($select);
}
}
class FilterSingleton {
static private $instance;
public $return = null;
private function __construct() {
}
static public function singleton() {
if (!isset(self::$instance)) {
$c = __CLASS__;
self::$instance = new $c;
}
return self::$instance;
}
}
and I don't get the error anymore, but now when selecting to filter by size (or color) I get the filter twice ( screenshot: http://imgur.com/a/JFEeR ).
<reference name="left"><block type="catalog/layer_view" name="catalog.leftnav" after="currency" template="catalog/layer/view.phtml"/></reference>
in/app/design/frontend/base/default/layout/catalog.xml
and it worked! – kanenas Dec 9 '16 at 14:48