Bug #86405
open
querySetting setRespectSysLanguage (wrong implementation of "strict"-mode)
0%
Description
Hello,
in TYPO3 9.4 the use of "setRespectSysLanguage" returns no translated records in "strict"-mode. I'm developing an extension to search a list of media. Each medium has an english record and a translated german record in a table media, so for example
(English)
uid: 11
sys_language_uid: 0
l10n_parent: 0
medium: water
(German)
uid: 12
sys_language_uid: 1
l10n_parent: 11
medium: Wasser
In the media repository I set up a query like
$query = $this->createQuery();
$query->getQuerySettings()->setRespectSysLanguage(true);
$constraint = $query->like('medium', '%' . $medium . '%');
$query->matching($constraint);
return $query->execute();
The expected result is: record 11 on the english page and record 12 on the german page. Currently I get the follwing result: record 11 on the english page, no record on the german page.
Prompting the underlying SQL query (english, short version) looks like:
SELECT * FROM media media
WHERE (medium LIKE '%water%') AND (sys_language_uid IN (0, -1))
AND (pid = 278) AND ((deleted = 0) AND (t3ver_state <= 0) AND (pid <> -1)
AND (hidden = 0) AND (starttime <= 1538051940) AND ((endtime = 0) OR (endtime > 1538051940)))
and (german, short version)
SELECT * FROM media media
WHERE (medium LIKE '%wasser%')
AND ((sys_language_uid = -1) OR ((sys_language_uid = 1) AND (l10n_parent = 0))
OR ((sys_language_uid = 0) AND (uid IN (SELECT l10n_parent FROM media WHERE (l10n_parent > 0) AND (sys_language_uid = 1) AND (deleted = 0)))))
AND (pid = 278) AND ((deleted = 0) AND (t3ver_state <= 0) AND (pid <> -1)
AND (hidden = 0) AND (starttime <= 1538051940) AND ((endtime = 0) OR (endtime > 1538051940)))
And now my questions:
1. Why is the table media nested two times in the query "FROM media media" ?
2a. The term "OR ((sys_language_uid = 1) AND (l10n_parent = 0))" cause the empty result by excluding all records with sys_language_uid=1 and l10n_parent pointing to an uid of the english record. Why?
or
2b. The term "OR ((sys_language_uid = 0) AND (uid IN (SELECT l10n_parent FROM media WHERE (l10n_parent > 0) AND (sys_language_uid = 1) AND (deleted = 0))))" cause the empty result by excluding all records with l10n_parent>0 and usually sys_language_uid=1 (and not 0). Why?
Eventually this issue is related to Bug #45873 which started in 2013 for TYPO3 6.0 and isn't solved yet.
Thanks for your attention and help
Rainer
Updated by Rainer Roskothen about 6 years ago
- Assignee deleted (
Tymoteusz Motylewski)
Updated by
Updated by Rainer Roskothen about 6 years ago
- Subject changed from querySetting setRespectSysLanguage has no effect to querySetting setRespectSysLanguage (wrong implementation of "strict"-mode)
- Description updated (diff)
Updated by Rainer Roskothen about 6 years ago
OK, let's have a look on the function "getSysLanguageStatement" in "sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbQueryParser.php". In my opinion, the implementation of the mode "strict" leads to wrong results for translated records.
The code block for "strict" creates three conditions, combined by "OR".
First, we want to get all the records that apply in all languages. The generated condition looks like:
(sys_language_uid = -1) That's correct.
Secondly, we want to get all records of the translated language which have no related record in default language. The generated condition looks like:
((sys_language_uid = 1) AND (l10n_parent = 0)) That's correct as well.
Thirdly, we want to get all records of the translated language which have an existing record in the default language. The generated condition looks like:
((sys_language_uid = 0) AND (uid IN (SELECT l10n_parent FROM tablename WHERE (l10n_parent > 0) AND (sys_language_uid = 1) AND (deleted = 0))))
This condition is wrong! It only returns results in the default language not the records in the translated language! The condition has to look like:
((sys_language_uid = 1) AND (l10n_parent IN (SELECT uid FROM tablename WHERE (sys_language_uid = 0) AND (uid IN (SELECT l10n_parent FROM tablename WHERE (l10n_parent > 0) AND (sys_language_uid = 1) AND (deleted = 0))))))
or shorter
((sys_language_uid = 1) AND (l10n_parent IN (SELECT uid FROM tablename WHERE (sys_language_uid = 0) AND (deleted = 0))))
I've tried to make a proposal for a correct function "getSysLanguageStatement", but to admit the truth I'm confused by the use of the terms $tableName and $tableAlias.
I hope this will help
Rainer
Updated by Tymoteusz Motylewski about 6 years ago
- Status changed from New to Needs Feedback
Hi Reiner
few remarks:
1) the strict mode uses language overlay. This means that default language records are fetched from db and then passed through PageRepository->getrecordoverlay.
So it most probably is correct that default language records are returned (as they will be overlayed later on).
2) The main issue with setRespectSysLanguage currently is that Extbase Persistence Session uses the same uid as cache key for both translated and default language versions of the record.
See https://forge.typo3.org/issues/59992 and test https://github.com/TYPO3/TYPO3.CMS/blob/master/typo3/sysext/extbase/Tests/Functional/Persistence/QueryLocalizedDataTest.php#L1148
I want to fix this issue but it requires to first write good test coverage for propertyMapper and other places. If you want to help here, I really appreciate it, just let me know.
3) in v9 the query part of extbase was reworked, see https://review.typo3.org/#/c/53974/ (especially the rst file describing the change)
Updated by Tymoteusz Motylewski about 6 years ago
- Related to Bug #59992: Persistence session doesn't take overlays into account added
Updated by Tymoteusz Motylewski about 6 years ago
- Related to Bug #45873: querySettings setRespectSysLanguage or setSysLanguageUid does not work added
Updated by Rainer Roskothen about 6 years ago
Hi Tymoteusz,
I will try my best to help here finding a proper solution.
Regarding your first remark: In my case the default language records fetched from the db never reach the function "PageRepository->getrecordoverlay". The log of the sql-server shows no query for the translated records either.
Next I get into your mentioned issue about the Extbase Persistance Session and try to understand what's going on there and how can I can help.
Thanks
Rainer
Updated by Rainer Roskothen about 6 years ago
Hi Tymoteusz,
we have to take a step back. Your first remark doesn't hit the problem! The build of the query can't return even records in the default language! Take a look in the very simple exampe of my first post.
Record A (default):
uid: 11
sys_language_uid: 0
l10n_parent: 0
medium: water
Record B (translated german record):
uid: 12
sys_language_uid: 1
l10n_parent: 11
medium: Wasser
And now the queries for translated records:
1) sys_language_uid = 1 -> No result
OR
2) sys_language_uid = 1 AND l10n_parent = 0 --> No result
OR
3) sys_language_uid = 0 AND uid IN ... --> Result record with uid = 11
BUT (!) we have to consider the complete query
AND medium LIKE '%Wasser%' (you search a german term in a set of records in default language!) --> No result ever!
This can't work this way.
Rainer
Updated by Susanne Moog over 4 years ago
- Status changed from Needs Feedback to New