addFieldToFilter() not considering associated products

Question

I'm using below code to get product collection,

$products = Mage::getResourceModel('catalog/product_collection')
                  ->AddAttributeToSelect('name')
                  ->addAttributeToSelect('price')
                  ->addAttributeToSelect('small_image')
                  ->addAttributeToSelect('image')
                  ->addFieldToFilter('size', array('eq' => '40'))
                  ->AddCategoryFilter($category);

Using ->addFieldToFilter('size', array('eq' => '130')) i'm trying to filter product collection by it's size.

As a result i'm getting empty result coz, my configurable product don't have attribute size

I've tried to include child product collection by

->joinTable('catalog/product_relation', 'child_id=entity_id', array(
                            'parent_id' => 'parent_id'
                    ), null, 'left')

but not working, did anyone know how to consider associated products while filtering ?

Answer 1

Try with following code, it's basically how layered navigation works:

    $category = Mage::getModel('catalog/category')->load(14);
    $collection = Mage::getResourceModel('catalog/product_collection');

    $collection->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addMinimalPrice()
        ->addFinalPrice()
        ->addTaxPercents()
        ->addUrlRewrite($category->getId())
        ->AddCategoryFilter($category);

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

    $attribute = Mage::getModel('eav/entity_attribute')->loadByCode('catalog_product', 'size');
    $options = $attribute->getSource()->getAllOptions(false);

    /*
     * Find value for given attribute label
     */
    $value = null;
    $label = 'XS';

    foreach ($options as $option) {
        if ($option['label'] == $label) {
            $value = $option['value'];
            break;
        }
    }

    if (!$value) {
        die('Whatever');
    }

    $connection = $collection->getConnection();
    $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 => 'catalog_product_index_eav'),
        implode(' AND ', $conditions),
        array()
    );

    /**
     * Debug
     */
    Zend_Debug::dump($collection->getSize());
    Zend_Debug::dump($collection->getSelectSql(true));

    foreach ($collection as $product) {
        Zend_Debug::dump($product->getName());
    }