TYPO3 Core

Bug #79931

Updated by Markus Klein about 7 years ago

Consider a table tx_ext_domain_model_thing, which has a field categories for sys_categories. 
 The category relations are stored in the Core's MM table. 

 The generated TCA for the categories field contains MM_matchFields as follows: 

 <pre> 
 'MM_match_fields' => [ 
     'tablenames' => $tableName, 
     'fieldname' => $fieldName, 
 ], 
 </pre> 
 (created by @\TYPO3\CMS\Core\Category\CategoryRegistry::getTcaFieldConfiguration()@) 

 Running the following extbase query should result in a list of tx_ext_domain_model_thing Models ordered by their first assigned category's title (if any assigned): 

 <pre> 
 $q->setOrderings([ 
     'categories.title' => ... 
 ]); 
 </pre> 

 What actually happens is that the sorting seems to be rather "random". 

 The reason is the executed SQL statement, which is: 

 <pre> 
 SELECT tx_ext_domain_model_thing.uid, tx_ext_domain_model_thing.title, sys_category.title, sys_category_record_mm.* FROM tx_ext_domain_model_thing 
   LEFT JOIN sys_category_record_mm AS sys_category_record_mm ON tx_ext_domain_model_thing.uid=sys_category_record_mm.uid_foreign 
   LEFT JOIN sys_category AS sys_category ON sys_category_record_mm.uid_local=sys_category.uid AND sys_category_record_mm.tablenames = 'tx_ext_domain_model_thing' AND sys_category_record_mm.fieldname = 'categories' 
 WHERE 1=1 AND (tx_ext_domain_model_thing.sys_language_uid IN (0,-1)) 
       AND (((sys_category.sys_language_uid IN (0,-1))) OR sys_category.uid IS NULL) 
       AND tx_ext_domain_model_thing.deleted=0 
       AND ((sys_category.deleted=0) OR sys_category.uid IS NULL) 
 ORDER BY sys_category.title ASC 
 </pre> 

 As you can see the important part @sys_category_record_mm.tablenames = 'tx_ext_domain_model_thing'@ is attached to the second LEFT JOIN. This means that the first LEFT JOIN with the MM table actually includes all records without any limitation to the matchFields. 

 The correct query would be: 

 <pre> 
 SELECT tx_ext_domain_model_thing.uid, tx_ext_domain_model_thing.title, sys_category.title, sys_category_record_mm.* FROM tx_ext_domain_model_thing 
   LEFT JOIN sys_category_record_mm AS sys_category_record_mm ON tx_ext_domain_model_thing.uid=sys_category_record_mm.uid_foreign AND sys_category_record_mm.tablenames = 'tx_ext_domain_model_thing' AND sys_category_record_mm.fieldname = 'categories' 
   LEFT JOIN sys_category AS sys_category ON sys_category_record_mm.uid_local=sys_category.uid 
 WHERE 1=1 AND (tx_ext_domain_model_thing.sys_language_uid IN (0,-1)) 
       AND (((sys_category.sys_language_uid IN (0,-1))) OR sys_category.uid IS NULL) 
       AND tx_ext_domain_model_thing.deleted=0 
       AND ((sys_category.deleted=0) OR sys_category.uid IS NULL) 
 ORDER BY sys_category.title ASC 
 </pre> 

 \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::addUnionStatement() is building this code and the fix is a one line change.

Back