Fixes an Extbase localization issue for a very limited set of use cases

README.md

Why

If you use Extbase with sys_language_mode=strict it automatically creates a query similar to this:

SELECT * FROM tx_news_domain_model_news
WHERE tx_news_domain_model_news.title LIKE '%house%'
AND (
    -- language set to all languages
    tx_news_domain_model_news.sys_language_uid = -1
    
    -- all translated records but without a language parent
    OR (
        tx_news_domain_model_news.sys_language_uid = 1 AND tx_news_domain_model_news.l10n_parent = 0
        
    -- records in the default language that have translations
    ) OR (
        tx_news_domain_model_news.sys_language_uid = 0 AND tx_news_domain_model_news.uid IN(
            SELECT tx_news_domain_model_news.l10n_parent
            FROM tx_news_domain_model_news
            WHERE tx_news_domain_model_news.l10n_parent > 0 AND tx_news_domain_model_news.sys_language_uid = 1 AND tx_news_domain_model_news.deleted = 0
        )
    )
)

That makes it impossible to search e.g. for a title of a properly translated news record because you either search records that have their language set to all, that have no language parent or are in the default language. It is quite difficult to support all the options that come with l10n_mode that is why this issue is still not resolved. For further information see https://forge.typo3.org/issues/77298 and Typo3DbQueryParser::getSysLanguageStatement.

What does it do?

The following fix extends the class Typo3DbQueryParser, takes the initial where clause (tx_news_domain_model_news.title LIKE '%house%' in the example) and moves it into a subquery where it looks for the conditions in a translated record and returns the uids of the records in the default language. That allows for a proper language overlay to take place afterwards.

What does it NOT do?

It does not solve all your problems that come with Extbase and localization. This fix only works in sys_language_mode=strict when you want to search fields that are explicitly set on the translated record. It does not work when you search fields that rely on l10n_mode settings, e.g. if you only set a category relation in the default language. I have also not tested it with multiple tables, e.g. in join queries, that is why the fix is not applied in such cases. You should activate this fix only for classes of which you know that these preconditions are met.

How to use it

1. Copy the code from the files to the corresponding files in your template or extension
2. Adjust the namespaces to match your needs
3. Add the class names that should use this fix to the array $queryTypesToFix

ext_localconf.php

$GLOBALS['TYPO3_CONF_VARS']['SYS']['Objects']['TYPO3\\CMS\\Extbase\\Persistence\\Generic\\Storage\\Typo3DbQueryParser'] = [
    'className' => 'Bithost\\BithostTemplate\\Xclass\\Typo3DbQueryParser'
];

Typo3DbQueryParser.php

<?php
namespace Bithost\BithostTemplate\Xclass;

use TYPO3\CMS\Extbase\Persistence\QueryInterface;

class Typo3DbQueryParser extends \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser {
    /**
     * List of query types that should use the fix explained in self::parseQuery
     *
     * @var array
     */
    protected $queryTypesToFix = [
        'GeorgRinger\News\Domain\Model\News',
        'Bithost\BithostTemplate\Domain\Model\YourCustomModel',
    ];

    /**
     * Fixes Extbase issue where translated records can not be searched correctly
     *
     * @see https://forge.typo3.org/issues/77298
     *
     * @param QueryInterface $query
     *
     * @return array
     */
    public function parseQuery(QueryInterface $query) {
        $sql = parent::parseQuery($query);

        if (in_array($query->getType(), $this->queryTypesToFix)
            && is_array($sql['tableAliasMap'])
            && count($sql['tableAliasMap']) === 1
            && $query->getQuerySettings()->getLanguageUid() > 0
            && $query->getQuerySettings()->getLanguageMode() === 'strict'
        ) {
            $tableAlias = key($sql['tableAliasMap']);
            $tableName = $sql['tableAliasMap'][$tableAlias];

            if (!empty($GLOBALS['TCA'][$tableName]['ctrl']['languageField'])) {
                $originalWhere = $sql['where'];
                $languageField = $GLOBALS['TCA'][$tableName]['ctrl']['languageField'];
                $transOrigPointerField = $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'];
                $queryLanguageUid = (int) $query->getQuerySettings()->getLanguageUid();
                $deleteClause = isset($GLOBALS['TCA'][$tableName]['ctrl']['delete']) ? ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['delete'] . '=0' : '';

                $where = '(' . $tableName . '.' . $languageField .'=0 AND ' . $tableName . '.uid IN(' .
                    'SELECT ' . $tableName . '.' . $transOrigPointerField . ' ' .
                    'FROM ' . $tableName . ' ' .
                    'WHERE ' . $tableName . '.' . $languageField . '=' . $queryLanguageUid . $deleteClause . ' AND ' . implode('', $originalWhere) . ')' .
                ')';

                $sql['where'] = [$where];
            }
        }

        return $sql;
    }
}