Project

General

Profile

Actions

Bug #79931

closed

Extbase ordering of results via child record (through MM) wrong

Added by Markus Klein about 7 years ago. Updated over 5 years ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Extbase
Target version:
Start date:
2017-02-20
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
7
PHP Version:
Tags:
Complexity:
medium
Is Regression:
No
Sprint Focus:
Stabilization Sprint

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.


Related issues 2 (0 open2 closed)

Related to TYPO3 Core - Bug #79932: extbase: wrong doctrine migration yields wrong DB resultClosed2017-02-21

Actions
Related to TYPO3 Core - Bug #79988: Fix ensureCorrectPostOrderingByCategoryTitle extbase test on postgresClosed2017-02-24

Actions
Actions #1

Updated by Markus Klein about 7 years ago

Keep in mind that master (8) is affected as well, but can't be reproduced currently due to #79932.

Actions #2

Updated by Markus Klein about 7 years ago

  • Description updated (diff)
Actions #3

Updated by Gerrit Code Review about 7 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

Actions #4

Updated by Gerrit Code Review about 7 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

Actions #5

Updated by Gerrit Code Review about 7 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

Actions #6

Updated by Gerrit Code Review about 7 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

Actions #7

Updated by Markus Klein about 7 years ago

  • Sprint Focus set to Stabilization Sprint
Actions #8

Updated by Gerrit Code Review about 7 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

Actions #9

Updated by Gerrit Code Review about 7 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

Actions #10

Updated by Johannes Kasberger about 7 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
Actions #11

Updated by Gerrit Code Review almost 7 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

Actions #12

Updated by Markus Klein almost 7 years ago

  • Status changed from Under Review to Resolved
Actions #13

Updated by Benni Mack over 5 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF