TYPO3 Core

Add an order clause changes the results of an extbase query if contents are multilanguages.

Order clause must be on relation content.

In this example (in attachment test_language), an element have one category. The order clause is one the name of the category.

//without/with order
$this->elementRepository->setDefaultOrderings(array(
'categorie.nom' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING
));

$this->view->assign('elements', $this->elementRepository->findAll());

Result (Exemple in .t3d, visible on content.png):

Default version :
Nom     Categorie (no ordering)
Elément 1 FR     Catégorie 1 FR
Elemet 2 only FR     Catégorie 3 FR
Element 3 FR     Categorie 2 only FR
Element 4 FR     Catégorie 3 FR

Nom     Categorie (ordering on category)
Elément 1 FR     Catégorie 1 FR
Element 3 FR     Categorie 2 only FR
Elemet 2 only FR     Catégorie 3 FR
Element 4 FR     Catégorie 3 FR

English version :
Nom     Categorie (no ordering)
Element 1 EN     Categorie 1 EN
Elemet 2 only FR     Catégorie 3 EN
Element 3 EN     Categorie 2 only FR
Element 4 EN     Catégorie 3 EN

Nom     Categorie (ordering on category)   Here the problem !
Element 3 EN     Categorie 2 only FR
Element 4 EN     Catégorie 3 EN

The query generated is (clean of hidden, deleted, startime ...):

FROM tx_testlanguage_domain_model_element
LEFT JOIN tx_testlanguage_domain_model_categorie ON tx_testlanguage_domain_model_element.categorie=tx_testlanguage_domain_model_categorie.uid
WHERE (tx_testlanguage_domain_model_element.sys_language_uid IN (1,-1)
       OR (tx_testlanguage_domain_model_element.sys_language_uid=0
           AND tx_testlanguage_domain_model_element.uid NOT IN
               (SELECT tx_testlanguage_domain_model_element.l10n_parent
                FROM tx_testlanguage_domain_model_element
                WHERE tx_testlanguage_domain_model_element.l10n_parent>0
                    AND tx_testlanguage_domain_model_element.sys_language_uid>0)))
    AND (tx_testlanguage_domain_model_categorie.sys_language_uid IN (1,-1)
         OR (tx_testlanguage_domain_model_categorie.sys_language_uid=0
             AND tx_testlanguage_domain_model_categorie.uid NOT IN
                 (SELECT tx_testlanguage_domain_model_categorie.l10n_parent
                  FROM tx_testlanguage_domain_model_categorie
                  WHERE tx_testlanguage_domain_model_categorie.l10n_parent>0
                      AND tx_testlanguage_domain_model_categorie.sys_language_uid>0)))
ORDER BY tx_testlanguage_domain_model_categorie.nom ASC

But the idea of ​​the query should be:

SELECT DISTINCT tx_testlanguage_domain_model_element.*
FROM tx_testlanguage_domain_model_element
LEFT JOIN tx_testlanguage_domain_model_categorie ON tx_testlanguage_domain_model_element.categorie=tx_testlanguage_domain_model_categorie.uid
WHERE (tx_testlanguage_domain_model_element.sys_language_uid IN (1,-1)
       OR (tx_testlanguage_domain_model_element.sys_language_uid=0
           AND tx_testlanguage_domain_model_element.uid NOT IN
               (SELECT tx_testlanguage_domain_model_element.l10n_parent
                FROM tx_testlanguage_domain_model_element
                WHERE tx_testlanguage_domain_model_element.l10n_parent>0
                    AND tx_testlanguage_domain_model_element.sys_language_uid>0)))
    AND (tx_testlanguage_domain_model_categorie.sys_language_uid IN (1,-1)
         OR tx_testlanguage_domain_model_categorie.sys_language_uid=0)
ORDER BY tx_testlanguage_domain_model_categorie.nom ASC

The translated version is specifically search. But not work, if the translated content points on original version of related content.

Element 4 works, it points on the good version of related content. But an order clause should never change the result of query, just the order. And what about the element 2 whose related content is not translated, and so it appears in the result.