Bug #32539
closed
Using a constraint or ordering on a property on the right side of a m:n relation causes records from the left side with no related elements (0 cardinality) to not show
0%
Description
On the following model:
- Model1
- Model2
- Model1.related is a m:n relation to Model2. Cardinality is 0..n
Example data:
- Model 1{a} -> "name" => "some value", "related" => Model2{a}
- Model 1{b} -> "name" => "some value"
- Model 2{a} -> "name" => "some value"
If you run a query on Extbase that runs a constraint on any property of Model2 through Model1:
(inside Model1 repository class)
$query = $this->createQuery();
$query->matching(
$query->logicalOr(
$query->like('name', 'some value'),
$query->like('related.name', 'some value'),
)
)->execute();
You will get only Model1 results which are related to matching Model2 rows, but according to the "logicalOr" you must get also Model1 results matching the criteria which are not related to Model2.
You currently get:
- Model 1{a}
But you should get:
- Model 1{a}
- Model 1{b}
By analysis of the generated SQL the problem is caused by the "enableFields" (the joins are ok):
The current generated SQL query part from the enableFields: AND tx_myext_domain_model_model2.deleted=0 AND [....]
...which forces elements from Model2 to be present on the relation.
This would be the right way: AND (tx_myext_domain_model_model2.uid IS NULL OR (tx_myext_domain_model_model2.deleted=0 AND [....]))
In the latter case we check wether the LEFT JOIN is not getting any values from Model2 and in that case don't apply the enableFields for Model2 on that row.
Files
Updated by Jose Antonio Guerra almost 13 years ago
While I manage to push the RFC onto Gerrit here´s the diff commit on my GitHub repo: https://github.com/jaguerra/extbase/commit/772c3bea6ef5f9ebb1c57ee92360814c97eb20b6
Updated by Jose Antonio Guerra almost 13 years ago
Commit prepared but my CLA is still in pending state so no push allowed... just sent a signed CLA by fax.
Updated by Gerrit Code Review almost 13 years ago
- Status changed from New to Under Review
Patch set 1 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/7285
Updated by Gerrit Code Review over 12 years ago
Patch set 2 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/7285
Updated by Gerrit Code Review about 12 years ago
Patch set 3 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/7285
Updated by Stefano Cecere almost 12 years ago
hi
http://review.typo3.org/7285 works "almost" well
it just misses to manage the
$GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField']
$sql['additionalWhereClause'][] = '(' .
$tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . ' IN (0,-1)' .
' OR (' .
$tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '=0 AND ' .
$tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=' . $GLOBALS['TSFE']->sys_language_content .
'))';
into
$statement = $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . ' IN (0,-1)' .
' OR (' .
$tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '=0 AND ' .
$tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=' . $GLOBALS['TSFE']->sys_language_content .
')';
$statement = ' ('.$tableName.'.uid IS NULL OR ('.$statement.')) ';
$sql['additionalWhereClause'][] = $statement;
unluckily i'm not able to push this into Gerrit
Updated by Tymoteusz Motylewski over 11 years ago
- Assignee changed from Jose Antonio Guerra to Tymoteusz Motylewski
Updated by Anja Leichsenring over 11 years ago
- Target version changed from Extbase 1.3 to Extbase 6.3
Updated by Anja Leichsenring over 11 years ago
- Target version changed from Extbase 6.3 to Extbase 6.2
Updated by Gerrit Code Review about 11 years ago
Patch set 1 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/21755
Updated by Gerrit Code Review about 11 years ago
Patch set 2 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/21755
Updated by Marc Bastian Heinrichs about 11 years ago
TODO: test patch with news backend module - see #49994
Updated by Stephan Großberndt over 10 years ago
Could this be completed since the last referred issue is closed?
Updated by Jigal van Hemert over 10 years ago
First of all the original report has a flaw in the logic:
the constraint "name like 'some value' OR related.name like 'some value'" will not be TRUE if there is no related record. In those cases the value for related.name is unknown (represented by NULL). Any comparison with a NULL value will result in NULL.
On the technical part:
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1=t2.col2 AND t1.deleted=0 AND t2.deleted=0
will include items without a matching t2 record
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1=t2.col2 WHERE t1.deleted=0 AND t2.deleted=0
will NOT include items without a matching t2 record
Moving the normal constraints (check for deleted, hidden, access restricted and time restricted records) to the ON clause would solve the problem for these constraints (which are not part of the logic, but must be included for internal, technical reasons)
The additional conditions belong to the WHERE clause and the programmer should implement the correct logic (including NULL values for related.name).
Updated by Alexander Opitz almost 10 years ago
- Project changed from 534 to TYPO3 Core
- Category changed from Extbase: Generic Persistence to Extbase
- Target version changed from Extbase 6.2 to 7.0
- TYPO3 Version set to 6.2
- Is Regression set to No
Hi Tymoteusz,
are you working on this?
Updated by Tymoteusz Motylewski almost 10 years ago
sorry, but no for the next few weeks at last. Please remove the assignment.
Updated by Alexander Opitz almost 10 years ago
- Assignee deleted (
Tymoteusz Motylewski)
Updated by Mathias Schreiber over 9 years ago
- Target version changed from 7.0 to 7.1 (Cleanup)
Updated by Gerrit Code Review over 9 years ago
Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/21755
Updated by Benni Mack over 9 years ago
- Target version changed from 7.1 (Cleanup) to 7.4 (Backend)
Updated by Gerrit Code Review about 9 years ago
Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/21755
Updated by Gerrit Code Review about 9 years ago
Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/21755
Updated by Stefan Neufeind about 9 years ago
- File dummy21755.tgz dummy21755.tgz added
Not working for me yet, it seems. But find attached a "dirty" test-extension.
It provides "Model1" and "Model2" as types, as well as a Frontend-plugin to call the show()-action. Modify the show-action to your liking for testing.
Updated by Gerrit Code Review about 9 years ago
Patch set 6 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/21755
Updated by Susanne Moog about 9 years ago
- Target version changed from 7.4 (Backend) to 7.5
Updated by Christian Kuhn over 8 years ago
- Status changed from Under Review to New
Updated by Susanne Moog over 4 years ago
- Is duplicate of Bug #88919: QueryBuilder with DefaultRestrictionContainer effectivly transforms outer joins to inner joins because the restrictions are applied to all join tables unconditionally added
Updated by Susanne Moog over 4 years ago
- Is duplicate of Bug #86385: QueryBuilder restrictions break leftJoin added
Updated by Susanne Moog over 4 years ago
- Status changed from New to Closed
Closing as duplicate (see related issue currently under review).