5

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.

  • What is the problem? – Fabian Blechschmidt May 28 '14 at 9:25
  • I get error : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'second.name' in 'field list' – VijayS91 May 28 '14 at 9:26
  • The country table doesn't have a name. The names come afaik from Zend_Directory: Mage::app()->getLocale()->getCountryTranslation($country_code); – Fabian Blechschmidt May 28 '14 at 9:27
  • yes its correct, using inner join how to get country name? – VijayS91 May 28 '14 at 9:30
  • 1
    There is no way, because the country names are in XML files: lib/Zend/Locale/Data/de.xml de is only one of the many XML files which are there. – Fabian Blechschmidt May 28 '14 at 9:40
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.