Why can I filter by an attribute in category view but cannot filter by the same attribute for my custom products collection?

Question

I want to filter my custom product collection. When I filter by an attribute with the following settings it doesn't work:

[attribute_model (Mage_Catalog_Model_Resource_Eav_Attribute)] => Array
                (
                    [entity_type_id] => 4
                    [attribute_code] => my_Attribute
                    [backend_type] => int
                    [frontend_input] => select
                    [frontend_label] => My Attribute
                    [source_model] => eav/entity_attribute_source_table
                    [is_required] => 0
                    [is_user_defined] => 1
                    [default_value] => 
                    [is_unique] => 0
                    [attribute_id] => 186
                    [is_global] => 2
                    [is_visible] => 1
                    [is_searchable] => 0
                    [is_filterable] => 1
                    [is_comparable] => 1
                    [is_visible_on_front] => 1
                    [is_html_allowed_on_front] => 1
                    [is_used_for_price_rules] => 0
                    [is_filterable_in_search] => 1
                    [used_in_product_listing] => 0
                    [used_for_sort_by] => 0
                    [is_configurable] => 0
                    [is_visible_in_advanced_search] => 1
                    [position] => 6
                    [is_wysiwyg_enabled] => 0
                    [is_used_for_promo_rules] => 0
                    [is_visible_on_checkout] => 0
                    [store_label] => My Attribute
                )

but as soon as I set used_in_product_listing to 1 and reindex everything it works.

Obviously, it only works if the attribute is indexed in flat tables which is also further explained in here: Filter product collection by non-flat attribute

But what is confusing me, why can I filter by this attribute in Category View. I think to enable that only [is_filterable] => 1 is needed. I want to keep my database as small as possible, so adding every attribute to flat_tables isn't really the best option. Additionally, filtering in category view already works pretty quick without every attribute in flat tables.

Looking at Mage_Catalog_Model_Layer following function is used:

public function prepareProductCollection($collection)
    {
        $collection
            ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
            ->addMinimalPrice()
            ->addFinalPrice()
            ->addTaxPercents()
            ->addUrlRewrite($this->getCurrentCategory()->getId());

        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
        Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

        return $this;
    }

However, Mage::getSingleton('catalog/config')->getProductAttributes() only contains attributes which are also in flat tables. I am using the same method in my Model/Layer and but filtering by that specific attribute does not work. It does get applied to my layered navigation though.

For filtering my attributes collection I just use

protected function _getProductCollection()
  {
        if (is_null($this->_productCollection)) 
        {
            $collection = Mage::getResourceModel('catalog/product_collection');
            Mage::getModel('Custom_Tag/Layer')->prepareProductCollection($collection);
            $_filters = Mage::getSingleton('Machholz_Tag/Layer')->getState()->getFilters();
             foreach ($_filters as $_filter) {
                if($_filter->getFilter()->getRequestVar() == "cat") {    
                  $category = Mage::getModel('catalog/category')->load($_filter->getValue());               
                  $collection->addCategoryFilter($category, true);
                } else {
                    if(is_array($_filter->getValue()))
                    {
                        foreach($_filter->getValue() as $value)
                        {
                            $collection->addAttributeToFilter($_filter->getFilter()->getRequestVar(), $value);
                        }
                    }

                    else    
                        $collection->addAttributeToFilter($_filter->getFilter()->getRequestVar(), $_filter->getValue());
                }

            }

            $this->_productCollection = $collection;
        }   

    return $this->_productCollection;

  }

So my question is, why can I filter by an attribute in category view, but it will not work for my custom product collection

Answer 1

I didn't find out why this doesn't work, but to bypass this limitation just use the code suggested by ColinM from the thread posted above:

$collection->addFieldToFilter(array(array('attribute' => 'my_attribute', 'eq' => 1)));

Answer 2

Make your prepareProductCollection method on your custom layer model include your attribute also:

public function prepareProductCollection($collection)
{
    $collection
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addAttributeToSelect('my_Attribute') //add your attribute here.
        ->addMinimalPrice()
        ->addFinalPrice()
        ->addTaxPercents()
        ->addUrlRewrite($this->getCurrentCategory()->getId());

    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
    Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

    return $this;
}