country not translated due to LEFT JOIN issue

[alain.roger]

Hi,

i run Joomla 1.5.20 with joomfish 2.0.4 and VM 1.1.5.
i've discovered that evern if countries are translated into a different language than english, country is always isplayed in english during checkout process.

digging a little bit i discovered that in ps_checkout.php, a function display_address has the following SQL statement:

$q  = "SELECT * FROM #__{vm}_user_info ";
      $q .= "INNER JOIN #__{vm}_country ON (#__{vm}_user_info.country = #__{vm}_country.country_3_code OR #__{vm}_user_info.country = #__{vm}_country.country_2_code) ";
      $q .= "LEFT JOIN #__{vm}_state ON (#__{vm}_user_info.state = #__{vm}_state.state_2_code AND #__{vm}_state.country_id = #__{vm}_country.country_id) ";
      $q .= "WHERE user_id='" . $auth["user_id"] . "' ";
      $q .= "AND address_type='BT' ";

in this case, country will remain always in english.

however, a few test and i found that if you comment the left join line, the country will be correctly discplay in the checkout page of your site.

however, in order to allow US, canada and other country having state indicated in checkout process, i'm not able to fix the issue with this left join.

any idea what could make joomfish not transating the country when LEFT JOIN is available ?

thanks a lot,

A.

[Krin]

Just replace the original SQL query

$q  = "SELECT * FROM #__{vm}_user_info ";
      $q .= "INNER JOIN #__{vm}_country ON (#__{vm}_user_info.country = #__{vm}_country.country_3_code OR #__{vm}_user_info.country = #__{vm}_country.country_2_code) ";
      $q .= "LEFT JOIN #__{vm}_state ON (#__{vm}_user_info.state = #__{vm}_state.state_2_code AND #__{vm}_state.country_id = #__{vm}_country.country_id) ";
      $q .= "WHERE user_id='" . $auth["user_id"] . "' ";
      $q .= "AND address_type='BT' ";

by this

   $q  = "SELECT i.*, c.country_id as key1, c.*, s.state_id as key2, s.* FROM #__{vm}_user_info i ";
   $q .= "LEFT JOIN #__{vm}_country c ON (i.country=c.country_3_code OR i.country=c.country_2_code) ";
   $q .= "LEFT JOIN #__{vm}_state s ON (i.state=s.state_2_code AND s.country_id=c.country_id) ";
   $q .= "WHERE user_id='" . $auth["user_id"] . "' ";
   $q .= "AND address_type='BT'";

For me it's work fine.