THELIA Forum

$locale = $this->request->getSession()->getLang()->getLocale();

$search =TestQuery::create();

$search
  ->rightJoinTestAddress('tableaddress')
  ->withColumn('`tableaddress`.COUNTRY_ID', 'COUNTRY_ID')
  ->addJoin(
       '`tableaddress`.COUNTRY_ID',
       CountryI18nTableMap::ID,
       Criteria::RIGHT_JOIN
  )
 ->add(CountryI18nTableMap::LOCALE,$locale)
;

SELECT * FROM `test` AS A LEFT JOIN `test_address` AS B ON (A.address_id = B.id) LEFT JOIN `country` AS C ON (B.country_id = C.id) LEFT JOIN `country_i18n` AS D ON (C.id = D.id AND D.locale = 'fr_FR') ORDER BY D.title

$locale = $this->request->getSession()->getLang()->getLocale();

$search =TestQuery::create();

$search
    ->leftJoinTestAddress('tableaddress')
    ->withColumn('`tableaddress`.ID', 'ADDRESS_ID')
    ->addJoin(
        '`tableaddress`.COUNTRY_ID',
        CountryTableMap::ID,
        Criteria::LEFT_JOIN
    )
    ->addJoin(
        array(
            CountryTableMap::ID,
            CountryI18nTableMap::LOCALE
        ),
        array(
            CountryI18nTableMap::ID,
            "'".$locale."'"
        ),
        Criteria::LEFT_JOIN
    )
    ->addAscendingOrderByColumn(CountryI18nTableMap::TITLE)
;

SELECT test.ID, test.VISIBLE, test.POSITION, test.TITLE, test.ADDRESS_ID, `tableaddress`.ID AS ADDRESS_ID FROM `test` LEFT JOIN `test_address` `tableaddress` ON (test.ADDRESS_ID=tableaddress.ID) LEFT JOIN `country` ON (`tableaddress`.COUNTRY_ID=country.ID) LEFT JOIN `country_i18n` ON (country.ID=country_i18n.ID AND country_i18n.LOCALE='fr_FR') ORDER BY country_i18n.TITLE ASC LIMIT 2147483647