How do I filter a magento collection by a select drop-down attribute?

Question

In magento, I have an attribute called cl_designer, which is a select drop-down option. I want to filter the products collection on it, like this:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToFilter('cl_designer', array('like' => $filter));

But it doesn't work! When I print out the query with $collection->getselect(), I see that it is comparing $filter to catalog_product_entity_int.value. But this is wrong, because for select options, catalog_product_entity_int.value is the option_id, NOT the value. So how do I make it filter on the actual option value?

Answer 1

Assuming an example drop-down attribute named size contains the following options:

id    value
22    'small'
23    'medium'
24    'large'

and you want to filter your collection by 'medium' options:

Filter by drop-down option value

To filter a product collection by option value of a product's (custom) drop-down attribute:

$sAttributeName = 'size';
$mOptionValue = 'medium';
$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter(
        $sAttributeName,
        array(
            'eq' => Mage::getResourceModel('catalog/product')
                        ->getAttribute($sAttributeName)
                        ->getSource()
                        ->getOptionId($mOptionValue)
        )
    );

Filter by drop-down option id

To filter a product collection by a option id of a product's (custom) drop-down attribute:

$sAttributeName = 'size';
$mOptionId = 23;
$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter(
        $sAttributeName,
        array('eq' => $mOptionId)
    );

Answer 2

In short, like this:

$collection->
    addAttributeToFilter(
        array(
            array('attribute' => 'cl_designer', 'eq' => ''),
            array('attribute' => 'cl_designer', 'neq' => '')
        ))->
    joinTable(array('cl_designer_value'=>'eav_attribute_option_value'),'option_id = cl_designer', array('cl_designer_value' => 'value'))->
    addAttributeToFilter('cl_designer_value', array('like' => $filter));

The first addAttributeToFilter is needed to make it include the right catalog_product_entity_int table, and join it properly - by entity_id, attribute_id, and store_id. Next we use joinTable to connect to eav_attribute_option_value.

joinTable is complicated. The first argument is an array of tables to join, of the form alias => tablename. The tablename can be the raw name (like here), or the standard magento slash notation. The second argument is a string of the form "primary=attribute". Whatever is on the left of the = is assumed to be the column in this table that you want to use to join on, and whatever is after the = is assumed to be an attribute code. It then converts the attribute code given into a proper table.column to use in the join, BUT it does not add the table if missing - that's why we needed the first addAttributeToFilter.

The next argument to joinTable is also required, and is an array of the form alias => column, each entry of which is available for reference by its alias - so I specified array('cl_designer_value' => 'value'), which means that I can refer to cl_designer_value.value (tablealias.column) as cl_designer_value.

After the joinTable, I can now treat cl_designer_value as any other attribute code, and use it normally.

Keep in mind that joinTable joins a table by attribute code, but also that once you have joined one, the attribute code you specify in the fields array (third argument) is then available for use in your next join. So you can chain several calls to joinTable together, if you need to, although to be fair I can't really think of when you would.