Join Collection With Country Table

Question

I have stored country id in my custom table. How to join the my custom collection with country collection to get a Country Name.

I have tried below code but its not working :

$collection = Mage::getModel('XXX/XXX')->getCollection()->addFieldToSelect(array('city','country_id')); 
$collection->getSelect()->join( 
    array('second'=>$collection->getTable('directory/country')),
    'main_table.country_id = second.code',
    array('second.name') 
)->getData();

Hope someone will provide some useful information.

Answer 1

The country table doesn't have a name.

The names come afaik from Zend_Directory, you can use this code to get the name from the code which is in second.iso2_code

Mage::app()->getLocale()->getCountryTranslation($country_code);

Sadly, there is no way to "join" the information into any sql query, because all the data is saved in files:

lib/Zend/Locale/Data/de.xml // de is only one of the many XML files which are there.