Jan 16, 2015

Strange localisation code in the Extbase persistence layer

Quite often I see that people do mistakes when they work with localisation in TYPO3. This topic is quite complex and often misunderstood.

TYPO3 localisation is defined by the Frontend Localisation Guide document, which was written by Kasper. I was one of the early reviewers of the document before it was published. While screenshots there are old, the document is still accurate. Let's have a quick look how localisation of records is handled in TYPO3.

Record localisation in TYPO3

Note that we are talking about records here, not pages. Pages are localised slightly differently.

All localisable records can be logically divided into the records in the main language and in other languages. This is an important distinction. Main language in TYPO3 always had id equals to zero. Other languages are defined on the tree top level and have id values starting from one.

The record in the main language is very important because it contains all fields. Records in other languages may either contain all fields or only some fields that depend on the localisation. For example, a record like Event can have the following fields:

  • Event id
  • Event title
  • Event description
  • Event place
  • Event date and time
  • Language id
There are six fields but only two of them are really subject to localisation: title and description. Id, place, date and time are the same regardless of the translation.

Thus, if properly configured, the record in the main language will look like:

idtitledescriptionplacedate and timelanguage id
1TYPO3 Meet upThis is the event for all TYPO3 users in...Zurich2015-01-25 15:30-16:300

For the localized record it will look like:

idtitledescriptionplacedate and timelanguage id
2TYPO3 만나 위로이것은 모든 사용자에 대한 TYPO3 이벤트2

I used Google Translate to make Korean translation, so do not hit me hard if you know Korean.

What is the wrong way to handle translated records?

The wrong way is to fetch the record by its language id and use it like that.

What is the right way to handle translated records?

The right way is to fetch the record in the main and target languages and overlay a record in the target language to the record in the main language.

While sounds scary and routine, it is easy to do. There is a function in the \TYPO3\CMS\Frontend\Page\PageRepository called getRecordOverlay. It accepts table name, the row in the main language and the id of the language. In return you get overlaid versions of all passed records. You can even give this function some flags, like “hide non-translated records”.

So, what is wrong with Extbase?

I stumbled upon the issue when a localised record could not be fetched. There was a relation between two records, which was was added to the query like this:

protected function filterByBeCatalogFilter(QueryInterface $query, array &$andParts) {
 $catalogList = $this->getCatalogList();
 if ($catalogList) {
  $orParts = array();
  foreach (explode(',', $catalogList) as $catalogId) {
   $orParts[] = $query->contains('catalogs', $catalogId);
  }
  if (count($orParts) > 1) {
   $andParts[] = $query->logicalOr($orParts);
  } else {
   $andParts[] = $orParts[0];
  }
 }
}

It worked fine for records in the main language but when switching to another language, zero results were returned from the query.
I started to dig. The query produced by Extbase looked like:

select * from tx_myext_event where
(tx_myext_event.uid IN (SELECT uid_local FROM tx_myext_event_catalog_mm WHERE uid_foreign='7') AND tx_myext_event.start_date >= '1421280000')
    AND
  (tx_myext_event.sys_language_uid IN (2,-1) OR (tx_myext_event.sys_language_uid=0 AND tx_myext_event.uid NOT IN (SELECT tx_myext_event.l10n_parent FROM tx_myext_event WHERE tx_myext_event.l10n_parent>0 AND tx_myext_event.sys_language_uid=2 AND tx_myext_event.deleted=0)))
    AND
tx_myext_event.deleted=0 AND tx_myext_event.hidden=0
order by tx_myext_event.start_date ASC, tx_myext_event.start_time ASC, tx_myext_event.title ASC
limit 10

The SQL part in bold looks problematic to me. This is exactly how records should not be fetched according to the TYPO3 localisation model.

Field catalog definition from $TCA:

'catalogs' => array(
    'label' => 'LLL:EXT:myext/locallang_db.xml:tx_myext_event.catalogs',
    'l10n_mode' => 'exclude',
 'config' => array(
  'type' => 'select',
  'foreign_table' => 'tx_myext_catalog',
  'foreign_table_where' => 'AND tx_myext_catalog.sys_language_uid IN (-1,0) ORDER BY tx_myext_catalog.uid',
  'size' => 5,
  'minitems' => 0,
  'maxitems' => 20,
  'MM' => 'tx_myext_event_catalog_mm'
    ),
),

Here you can see why the original Extbase query above could never work: the field is empty in localised records. Extbase incorrectly assumes that all localised records have all values in them and fails.

How the query should look like? Here it is:

select * from tx_myext_event where
(tx_myext_event.uid IN (SELECT uid_local FROM tx_myext_event_catalog_mm WHERE uid_foreign='7') AND tx_myext_event.start_date >= '1421280000')
    AND
  tx_myext_event.sys_language_uid = 0
    AND
tx_myext_event.deleted=0 AND tx_myext_event.hidden=0
order by tx_myext_event.start_date ASC, tx_myext_event.start_time ASC, tx_myext_event.title ASC
limit 10

“But that fetches only the main language!” – I hear you saying to me. Yes, that's right! Later in the code there is a function named doLanguageAndWorkspaceOverlay, which correctly overlays translated records on top of records in the main language.

Incorrect query part is added by the function named \TYPO3\CMS\Extbase\Persistence\Generic\StorageTypo3DbQueryParser::addSysLanguageStatement().

Why does this problem happen?


Of that I cannot be sure. Either there is something very deep here, which I do not understand (unlikely), or simply the person, who wrote this piece of code, did not know how to handle localisation properly in TYPO3.

I welcome your thoughts and comments about the issue.

How to avoid it without changing Extbase?

See update #2 below.

If you ran into the same problem, use $query->getQuerySettings()->setRespectSysLanguage(FALSE) to block the wrong function. Yes, the name name “getRespectSysLanguage()” is also wrong because it does not stop doLanguageAndWorkspaceOverlay but it stops Extbase from applying wrong localisation part of the query.

Interesting thing: if you look into the TYPO3 bug tracker, you will see bug reports about getRespectSysLanguage. They expect certain behaviour from this function, which it does not provide. It seems that the original author was sure he was doing the right thing and named the function accordingly but in fact the function just blocks the wrong behavior of Extbase.

Update #1 [01.01.2015 12:50]: the harm was done here. Handling was correct before this code was merged. Original issue on Forge is gone but its webcache is available here.

Update #2 [14:17]: I added a gist with an XCLASS, which solves the issue.

8 comments:

  1. Thank you! Did you file a bug report already?

    ReplyDelete
    Replies
    1. Not yet because:
      1. I want to be sure it is an error
      2. I see some side effects after using setRespectSysLanguage(FALSE) and investigating that.

      Delete
  2. Hello Dmitry,

    when using your xclass overwrite, my findAll-Method finds only translated records that have a reference in standard language.
    Records that only exist in non-standard language are not shown, although the non-standard language is configured in typoscript.

    I was hoping your fix would help me with my initial problem: I tried to overwrite a mn-Relation in the translated records. But always the relation of the standard-record is used (although correctly translated). I tried with inline-IRRE-mn and with normal mn-select-configuration.
    In the backend the overwrite works perfectly, but in the frontend the relation uses the uid of the standard-record => no overwrite of relation.

    Maybe you have any hints for me. =)

    Kind Regards,
    Phil

    ReplyDelete
    Replies
    1. > Records that only exist in non-standard language are not shown, although the non-standard language is configured in typoscript.

      This is correct. In TYPO3 records in the default language must always exist (see the FE localization guide document). If you want to hide records in the default language but keep only localized records, you need a special table/TS setup.

      Delete
    2. Thanks! Do you have a clue, why overlaying/overwriting a mn-Relation in the translation is not working in the frontend? In the backend there seems to be no problem. (see screenshot: postimg.org/image/77ocwv3tb/)

      Might this be related to the bug you found? Or is it simly not possible?

      Delete
    3. This is because of the bug. In Backend there are other functions (not Extbase) that fetch records correctly. In FE Extbase does it in a wrong way.

      Delete
  3. The biggest PITA of this method is when sorting: it doesn't appear that you can use the regular ExtBase orderings for the overlaid records, so we've had to implement a ViewHelper to sort the translated records by name for a List view. This seems mad to me.

    ReplyDelete
  4. Hi there! This post is about two years old now, but as far I as I can tell, the problem still exists. Are there any workarounds/patches for the current LTS version (7.6)?

    ReplyDelete