Project

General

Profile

Actions
Copy link

Bug #93387

open

[BUGFIX] SQL-Query-Results are incomplete when using foreign_table_field and/or foreign_match_fields

Added by Harald Witt over 3 years ago. Updated about 3 years ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
-
Target version:
next-patchlevel
Start date:
2021-01-31
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
10
PHP Version:
7.3
Tags:
TCA foreign_table_field foreign_match_fields getAdditionalMatchFieldsStatement expressionBuilder
Complexity:
Is Regression:
Sprint Focus:

Description

If using foreign_table_field and/or foreign_match_fields this will result in the following additional condition in WHERE:
((childtable.foreign_match_field1 = value1) AND (...) AND (childtable.parent_table_field = parenttable))

Obviously this condition excludes generally all records of parenttable with no child-records.
The correct condition should be:
(((childtable.foreign_match_field1 = value1) AND (...) AND (childtable.parent_table_field = parenttable))
OR (childtable.anyExistingField IS NULL))

The reason for this issue can be found in:
\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser->getAdditionalMatchFieldsStatement()
My purpose / patch is (lines marked wit /*NEW*/ in front):
protected function getAdditionalMatchFieldsStatement($exprBuilder, $columnMap, $childTableAlias, $parentTable = null)
{
    $additionalWhereForMatchFields = [];
    $relationTableMatchFields = $columnMap->getRelationTableMatchFields();
    if (is_array($relationTableMatchFields) && !empty($relationTableMatchFields)) {
        foreach ($relationTableMatchFields as $fieldName => $value) {
            $additionalWhereForMatchFields[] = $exprBuilder->eq($childTableAlias . '.' . $fieldName, $this->queryBuilder->createNamedParameter($value));
/*NEW*/     if (!$isNullExpr) {
/*NEW*/         $isNullExpr = $exprBuilder->isNull($childTableAlias . '.' . $fieldName);
/*NEW*/     }
        }
    }

    if (isset($parentTable)) {
        $parentTableFieldName = $columnMap->getParentTableFieldName();
        if (!empty($parentTableFieldName)) {
            $additionalWhereForMatchFields[] = $exprBuilder->eq($childTableAlias . '.' . $parentTableFieldName, $this->queryBuilder->createNamedParameter($p
/*NEW*/     if (!$isNullExpr) {
/*NEW*/         $isNullExpr = $exprBuilder->isNull($childTableAlias . '.' . $parentTableFieldName);
/*NEW*/     }
        }
    }

    if (!empty($additionalWhereForMatchFields)) {
/*NEW*/ return $exprBuilder->orX($isNullExpr, $exprBuilder->andX(...$additionalWhereForMatchFields));
/*OLD*/ //return $exprBuilder->andX(...$additionalWhereForMatchFields);
    }
    return '';
}

There are only 2 methods who call getRelationTableMatchFields()
  1. Three times in addUnionStatement(...) for each relation type: RELATION_HAS_ONE, RELATION_HAS_MANY and RELATION_HAS_AND_BELONGS_TO_MANY
  2. One time in parseComparison(...)

So it should not be a big task to review this bugfix purpose / patch
Harald

Actions
Copy link

Also available in: Atom PDF