Bug #79931
closedExtbase ordering of results via child record (through MM) wrong
100%
Description
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:
'MM_match_fields' => [ 'tablenames' => $tableName, 'fieldname' => $fieldName, ],
(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):
$q->setOrderings([ 'categories.title' => ... ]);
What actually happens is that the sorting seems to be rather "random".
The reason is the executed SQL statement, which is:
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
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:
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
\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::addUnionStatement() is building this code and the fix is a one line change.
Updated by Markus Klein almost 8 years ago
Keep in mind that master (8) is affected as well, but can't be reproduced currently due to #79932.
Updated by Gerrit Code Review almost 8 years ago
- Status changed from New to Under Review
Patch set 1 for branch TYPO3_7-6 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/51775
Updated by Gerrit Code Review almost 8 years ago
Patch set 2 for branch TYPO3_7-6 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/51775
Updated by Gerrit Code Review almost 8 years ago
Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/51782
Updated by Gerrit Code Review almost 8 years ago
Patch set 3 for branch TYPO3_7-6 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/51775
Updated by Markus Klein almost 8 years ago
- Sprint Focus set to Stabilization Sprint
Updated by Gerrit Code Review almost 8 years ago
Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/51782
Updated by Gerrit Code Review almost 8 years ago
Patch set 4 for branch TYPO3_7-6 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/51775
Updated by Johannes Kasberger almost 8 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset adb45e1d7bcb6a445c0e66dbd936092a72a52cde.
Updated by Gerrit Code Review almost 8 years ago
- Status changed from Resolved to Under Review
Patch set 5 for branch TYPO3_7-6 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/51775
Updated by Markus Klein almost 8 years ago
- Status changed from Under Review to Resolved
Applied in changeset dabee749b8b2b9da01df95b8f2cdbc69fb75e032.