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 about 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
 #1

Updated by Harald Witt about 3 years ago

  • Subject changed from SQL-Query-Results are incomplete when using foreign_table_field and/or foreign_match_fields to [BUGFIX] SQL-Query-Results are incomplete when using foreign_table_field and/or foreign_match_fields
  • Target version set to next-patchlevel
  • Tags set to TCA foreign_table_field foreign_match_fields getAdditionalMatchFieldsStatement expressionBuilder
Actions
Copy link
 #2

Updated by Harald Witt about 3 years ago

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

Actions
Copy link

Also available in: Atom PDF