List products by dropdown attribute

Question

I want to have a cms page where I will list all products that have special_offer dropdown attribute with yes value. I found something here but this is work only with the Yes/No attribute. I need to use dropdown attribute because I want to use this attribute in the layered navigation too.

So how I can display dropdown special_offer attribute where options are Yes and No?

This is the public function where the products are sorted:

public function getFeaturedProducts() 
{
    $resource = Mage::getSingleton('core/resource');
    $read = $resource->getConnection('catalog_read');
    $productEntityIntTable = (string)Mage::getConfig()->getTablePrefix() . 'catalog_product_entity_int';
    $eavAttributeTable = $resource->getTableName('eav/attribute');
    $categoryProductTable = $resource->getTableName('catalog/category_product');

    $select = $read->select()
        ->distinct(true)
        ->from(array('cp'=>$categoryProductTable), 'product_id')
        ->join(array('pei'=>$productEntityIntTable), 'pei.entity_id=cp.product_id', array())
        ->joinNatural(array('ea'=>$eavAttributeTable))
        ->where('pei.value=1')
        ->where('ea.attribute_code="special_offer"');

    $res = $read->fetchAll($select);

    return $res;
}

Thank you

Answer 1

@Robert the product drop-down attribute values also stored in table catalog_product_entity_int so you want find the attribute option id value of Yes and No from Magento backend using firebug or from database eav_attribute_option table. Then use that values in your where condition like below.

For example if Yes = 155 and No =156

$select = $read->select()
    ->distinct(true)
    ->from(array('cp'=>$categoryProductTable), 'product_id')
    ->join(array('pei'=>$productEntityIntTable), 'pei.entity_id=cp.product_id', array())
    ->joinNatural(array('ea'=>$eavAttributeTable))
    ->where('pei.value=155')
    ->where('pei.value=156')
    ->where('ea.attribute_code="special_offer"');