Could not filter product collection in Mage_Catalog_Block_Product_List by a custom attribute in magento 1.9

Question

I have an issue similar to this question , and i checked the 'Already answered link' too. But my issue is bit different.

The error is similar in the above link but with my custom attribute :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.product_store' in 'where clause',. .... ..

Im trying to filter collection by two attributes as shown below :

Mage_Catalog_Block_Product_List

$this->_productCollection = $layer->getProductCollection()
                    ->addAttributeToFilter(array(array('attribute' => 'hidden_countries', 'nlike' => '%'.$current_country.'%')))
                    ->addAttributeToFilter(array(array('attribute' => 'product_store', 'in' => $optionToFilter)))
                    ->setPageSize($limit)
                    ->setCurPage($currPage);

The first filter for : hidden_countries is working fine, and i have the issue with the second attribute.

The issue happens if i switch to flat product table. i checked the catalog_product_flat_x tables & i found the custom attribute ('product_store') and its values already there.

Did a complete re-index . But that doesn't solved the issue.

I can filter the product collection by getModel('catalog/product'):

$products = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToFilter(array(array('attribute' => 'hidden_countries', 'nlike' => '%'.$current_country.'%')))
            ->addFieldToFilter(array(array('attribute' => 'product_store', 'in' => $optionToFilter)))
            ->setCurPage($this->get_cur_page());

AS i can see the variable $layer is an instance of Mage_Catalog_Model_Layer and i can see that the getLayer returns Mage::getSingleton('catalog/layer').

Does any body knows why i can filter on 
Mage::getModel('catalog/product') but not on Mage::getSingleton('catalog/layer') ?

Answer 1

Edit: I would not advise this if you require using the price filter in layered navigation. The price range is trying to be calculated, however if you reset the WHERE clause to avoid the errors, your price range in the filters may be off from the actual price range of the resulting collection set.

eg. Your collection has 10 items between $10-100. If your simples are all under $50 and you filter so configurables only show and the configurables are all above $50, if you merely reset the where clause, the price range would still show $10-$100 as it was unable to apply your conditional filter to the collection.

To ensure the price filter range is correct and based on your customized collection, you would need to extend

app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php

and reset the WHERE clause due to the 'e' table aliase changing tables, and reapply your WHERE clause with a proper join to filter the resulting set pulled from price index table.

Original answer:

I am not sure of any caveats with this fix what did the trick for me was;

Overriding

app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php

to

app/code/local/Mage/Catalog/Model/Resource/Layer/Filter/Price.php

and adding a single line in _getSelect method @L105

$select->reset(Zend_Db_Select::WHERE);

so it should look like

    // 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);
    $select->reset(Zend_Db_Select::WHERE);

As I am modifying my category collection by overwriting (as I need to override visibility filters, and they are added in this method)

Mage_Catalog_Model_Layer
app/code/core/Mage/Catalog/Model/Layer.php
prepareProductCollection

It seems to be an oversight where the code does not expect the category collection select statement's WHERE clause to be modified?

Not sure if anyone else can chime in on this to clear things up. looks for Alan Storm