Issue Bug Closed

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

Harald Witt 01/31/21, 09:23 updated 10/22/26, 20:04

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

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

History 3

  1. Harald Witt changed subject SQL-Query-Results are incomplete when using foreign_table_field and/or foreign_match_fields [BUGFIX] SQL-Query-Results are incomplete when using foreign_table_field and/or foreign_match_fields target version Tags TCA foreign_table_field foreign_match_fields getAdditionalMatchFieldsStatement expressionBuilder <span data-processor="relativetime" data-value="2021-02-04T09:16:04+00:00" > 02/04/21, 09:16 </span>

  2. Harald Witt commented <span data-processor="relativetime" data-value="2021-02-11T17:35:25+00:00" > 02/11/21, 17:35 </span>

    Hi, It’s me again.
    The purposed bugfix works fine for the descibed usecase. But if you are using a child-table with different record-types and want to make some different conditions for different types, this will fail again.
    This is bacause the same table is joined multiple times. And so the additionalMatchFieldsStatements will exclude each other.

    This makes clear, that the additionalMatchFieldsStatements have nothing to do in the WHERE-clause, but in the ON-clause of the JOIN.

    Dear core team:
    You made this very fine for the intermediate table of MM-relations. Congratulations, that functions properly.
    But why isn’t it done for 1:n relations the same way? Heaven knows :-(

    So guys, if you are familiar with SQL, forgot the $query. It’s better to use the $queryBuilder. That way you can fine adjust your query as you like and some magic with enable-fields is also done.

    For the above eaxample the following code functions absolutely perfect:

    public function noBugDemo() {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_intranet_domain_model_parenttable');
        $queryBuilder->select('parent.*')->from('tx_intranet_domain_model_parenttable', 'parent');
        $queryBuilder->leftJoin('parent', 'tx_intranet_domain_model_childtable', 'child', 
            '((parent.uid = child.parent_uid) AND ( (child.parent_table IS NULL) OR (child.type=3 AND child.parent_table=\'tx_intranet_domain_model_parenttable\' ) ))');
        $constraints[] = $queryBuilder->expr()->like('parent.parent_content', $queryBuilder->createNamedParameter('%who%'));
        $constraints[] = $queryBuilder->expr()->like('child.child_content', $queryBuilder->createNamedParameter('%who%'));
        $queryBuilder->where($queryBuilder->expr()->orX(...$constraints));
        // map results to objects if you like or need it
        $objectManager = GeneralUtility::makeInstance(\TYPO3\CMS\Extbase\Object\ObjectManager::class);
        $dataMapper = $objectManager->get(\TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper::class);
        $result = $dataMapper->map(\DPFA\Intranet\Domain\Model\Contact\Person::class, $queryBuilder->execute()->fetchAll());
        return $result;
    }

    Since nobody is really interrested in this issue I purpose to close this.

    Harald

  3. Georg Ringer commented and changed status New Closed <span data-processor="relativetime" data-value="2026-10-22T20:04:08+00:00" > 10/22/26, 20:04 </span>

    the issue couldn’t be reproduced in v15. Therefore closing it.

    if this is still valid for you in TYPO3 v14, please open a new issue and provide information to reproduce it!