1

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:

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 ).

  • Could you provide the full stack trace as in this question: magento.stackexchange.com/questions/9009/… – Raphael at Digital Pianism Jul 13 '16 at 14:30
  • @RaphaelatDigitalPianism I have edited my question and now you can see the full stack trace. – kanenas Jul 13 '16 at 19:38
  • 1
    I wonder if the cache is randomly merging the base and theme xml blocks relating to catalog/layer_view. Is it worth an experiment to remove the block from /base/default/layout/catalog.xml ? – Craig Dec 8 '16 at 16:40
  • @Craig, It looks like you were right! I have commented this <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
  • @kanenas That's awesome! Finally an answer to this age-old problem. I have included an answer for future reference. :) – Craig Dec 10 '16 at 1:50
2
+50

Be sure you are not adding the same attribute twice with addAttributeToSort in any of your collections. As you already mentioned be sure none of your layout's are including the catalog/layer_view block types.

<catalog_category_layered>         
 <reference name="left">
    <block type="mymodule/catalog_layer_view" name="mymodule.navleft" after="currency" template="catalog/layer/view.phtml"/> 
 </reference> 
 </catalog_category_layered>

... and the same block type included again within another layout XML, remember that the layout can be injected by code as well so be sure to look for 3rd party or local custom code that is injecting blocks into the layout for the layered navigation.

As a last resort you can try:

But I will definitely scour your code base as much as possible for any re setting of orders in collection or the double inclusion of layered navigation blocks within the layout XML files or possibly in 3rd party code.

Any list of 3rd party layered navigation or search modules you are using would also help in determining a fix possibly, as your stack dump doesn't show any local or 3rd party community code.

Hope this helps.

  • 1
    I used the "last resort" and it fixed the error, but now when selecting to filter by size (or color) I get the filter twice (screenshot: imgur.com/a/JFEeR). Any suggestions? – kanenas Dec 7 '16 at 9:36
  • Can you update your answer with the code you have now in place and maybe some of the module structure snippets? – B00MER Dec 7 '16 at 17:50
  • Can you please explain what module structure snippets do you need to see? – kanenas Dec 8 '16 at 11:13
0

I can reproduce this error sometimes (but very rarely) after clearing the cache. Almost like the Cache clearing failed for whatever reason. I get this on 2 separate installations of Magento.

The only obvious thing that both stacks have in common is that they both use Varnish Cache. Because I've never had this issue with Varnish turned off.

Quick Solution

I've found that you only need to clear Layouts under the Cache to resolve it.

System > Cache Management > Layouts > Refresh

  • Graig, thanks for sharing your experience, the thing is that you can't always be inside Magento administration to refresh cache. – kanenas Dec 3 '16 at 21:42
0

Failing all of the aforementioned options that you have mentioned in your question, a final approach would be worth commenting out the below line within this file:

Target File

/app/design/frontend/base/default/layout/catalog.xml

Comment Out

<reference name="left"><block type="catalog/layer_view" name="catalog.leftnav" after="currency" template="catalog/layer/view.phtml"/></reference>

Explanation

After executing Clear Cache within Magento, sometimes it can merge parts of the BASE Config.xml and THEME Config.xml. Why this is, I don't know. However, it does appear a few people are affected by this phenomenon. Commenting out this one line will ensure that the merge is incapable of happening.

  • you must mention that I have changed the /app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Attribute.php file. Also, although the error disappeared and when selecting to filter by size (or color) I get the filter twice ( screenshot: imgur.com/a/JFEeR ) is fixed, when filtering with size and then color the displayed results are like in the query is used OR and not AND. – kanenas Dec 12 '16 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.