magento getting product id based on custom option value -

i need retrieve array of cross sell product ids product based on product id retrieved based on value in product custom option.

i have researched how using straight mysql statements. how using standard magento calls?

here mysql process:

select option_type_id mage_catalog_product_option_type_title option_type_id eq 'desired value'; 

then:

select option_id mage_catalog_product_option_type_value option_type_id eq option_type_id; (from above step) 

then:

select product_id mage_catalog_product_option option_id eq option id; (from previous step) 

then:

select linked_product_id mage_catalog_product_link product_id eq product_id (from previous step) , link_type_id ='5'; 

(i'm experienced in php/mysql barely past novice level in magento)

can help? thank very, much!

mark

try solution:

$collection = mage::getmodel('catalog/product_link')->getcollection()->addfieldtofilter('option_type_id','desired value’); $collection->join(array('option' => 'catalog/product_option'), 'main_table.product_id = option.product_id')            ->join(array('option_value' => 'catalog/product_option_type_value'), 'option.option_id = option_value.option_id'); var_dump($collection->getfirstitem()->getdata()); 

and how works:

$collection = mage::getmodel('catalog/product_option_value')->getcollection()->addfieldtofilter('option_type_id','desired value’); $collection = mage::getmodel('catalog/product_option')->getcollection()->addfieldtofilter('option_id',$collection->getfirstitem()->getoptionid()); $collection = mage::getmodel('catalog/product_link')->getcollection()->addfieldtofilter('product_id',$collection->getfirstitem()->getproductid()); var_dump($collection->getfirstitem()->getdata());