addAttributeToFilter with dropdown values

Question

Given a dropdown attribute such as color:

color:
   Red
   Green
   Blue

When loading a product collection how can i load the collection based on either red, blue or green values without knowing the actual option ids?

For example:

Mage::getModel('catalog/product')->getCollection()->addAttributeToFilter('color', 'Red');

Will not work as i need to know the option id.

This means loading the attribute and getting the options id - which is going to be innefficient for a large number of attributes/options.

Marius · Answer 1 · 2014-02-24T08:06:08.753

by default you cannot filter by color (or any dropdown attribute) like that. The cleanest way to do it is to get the option id for the red color.

$collection = Mage::getModel('catalog/product')->getCollection();
$options = Mage::getModel('eav/config')->getAttribute('catalog_product', 'color')->getSource()->getAllOptions(); //get all options
$optionId = false;
foreach ($options as $option) {
    if (strtolower($option['label']) == 'red'){ //find the Red option
        $optionId = $option['value']; //get it's id
        break;
    }
}
if ($optionId) { //if there is an id...
    $collection->addAttributeToFilter('color', $optionId);
}

But this could backfire if you have 2 options with the same name.

There is also this option:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->getSelect()->where('color_value = ?', 'Red');

But it works only when you have the flat catalog enabled and when the color attribute is set to be "Used in product listing".
I don't recommend this approach. It can get you into trouble.

One of the things I'd like to add to this is you can "cache" the heavy part (Getting options ID and Label relation) for efficiency - http://www.bubblecode.net/en/2012/02/03/how-to-cache-custom-data-into-magento/ , and another thing would be change the case of letter to UPPER or lower before comparing. — Subesh Pokhrel, Feb 24 '14 at 08:05
@SubeshPokhrel. You are right. Caching is a good idea. I was just trying to prove a concept. I also changed the comparison to use lowercase strings. Good call. — Marius, Feb 24 '14 at 08:06

score 1 · Answer 2 · answered Jul 22 '16 at 13:28

Here is an alternative to Marius's answer that works well for us...

<?php
$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter(
        'click_and_collect',
        array(
            'eq' => Mage::getResourceModel('catalog/product')
                        ->getAttribute('click_and_collect')
                        ->getSource()
                        ->getOptionId('Yes')
        )
    );
?>

<ul>
<?php foreach ($collection as $product): ?>
    <li><a href="<?php echo $product->getProductUrl(); ?>"><span class="product-name"><?php echo $product->getName(); ?></span></a></li>
<?php endforeach; ?>
</ul>

addAttributeToFilter with dropdown values

2 Answers2