How i can join eav_attribute_option_value table into my collection and get from him "frontend_label" of colors?

Question

I have a products collections. But in "color" i see option_id number.

Mage::getModel('catalog/product')
                ->getCollection()
                ->addStoreFilter(Mage::app()->getStore())
                ->addAttributeToSelect('color')
                ->addAttributeToSelect('url_path')
                ->addAttributeToFilter('name', $name)
                ->addAttributeToFilter('status', 1)
                ->addAttributeToFilter('visibility', 4);

How i can join eav_attribute_option_value table into my collection and get from him "frontend_label" of colors?

Answer 1

You can not use "eav_attribute_option_value" directly its store only store_id, option_id and value SO before join with eav_attribute_option_value table you have to join with "catalog_product_entity_int"

$coll = Mage::getModel('catalog/product')
            ->getCollection()
            ->addStoreFilter(Mage::app()->getStore())
            //->addAttributeToFilter('test_option_value' , 232)
            ->addAttributeToFilter('status', 1);
    $coll->getSelect()->joinLeft(array('c_p_e_i'=>'catalog_product_entity_int'),'e.entity_id = c_p_e_i.entity_id',array('value'));
    $coll->getSelect()->joinLeft(array('attribute_option'=>'eav_attribute_option_value'),'c_p_e_i.value =       attribute_option.option_id and attribute_option.store_id = 0',array('eaov_val'=>'value' , 'eaov_option'=>'option_id'));

    $attribute_name = 'test1';
    echo $coll->getSelect()->where("`attribute_option`.`value` = '$attribute_name'");
    die();

cheers