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

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