Actions
Bug #93337
closed
Querybuilder builds conditions for foreign_table_field and/or foreign_match_fields without "OR ...uid IS NULL" for joined Table
Status:
Resolved
Priority:
Must have
Assignee:
-
Category:
Extbase
Target version:
-
Start date:
2021-01-21
Due date:
% Done:
100%
Estimated time:
TYPO3 Version:
12
PHP Version:
7.4
Tags:
Complexity:
hard
Is Regression:
Sprint Focus:
Description
BTW: Unfortunately I cannot change the Priority to must have .
QueryBuilder creates wrong query when using foreign_table_field and/or foreign_match_fields in a 1:n inline relation.
The conditions for foreign_table_field and/or foreign_match_fields for the joined table are build, but the "OR ...uid IS NULL" is missing.
So all records of the main-table which do not have any child record in the joined table are excluded from the query result.
Here's my detailled demonstration. I'll use my own table names here ...
ext_tables.sql:
#
# Table structure for table 'tx_intranet_domain_model_childtable'
#
CREATE TABLE tx_intranet_domain_model_childtable (
uid int(11) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL,
tstamp int(11) DEFAULT '0' NOT NULL,
crdate int(11) DEFAULT '0' NOT NULL,
cruser_id int(11) DEFAULT '0' NOT NULL,
parent_table varchar(255) DEFAULT '' NOT NULL,
parent_uid int(11) DEFAULT '0' NOT NULL,
type smallint(5) unsigned DEFAULT '0' NOT NULL,
child_content varchar(255) DEFAULT '' NOT NULL,
PRIMARY KEY (uid),
KEY `parent` (`parent_table`,`parent_uid`)
);
#
# Table structure for table 'tx_intranet_domain_model_parenttable'
#
CREATE TABLE tx_intranet_domain_model_parenttable (
uid int(11) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL,
tstamp int(11) DEFAULT '0' NOT NULL,
crdate int(11) DEFAULT '0' NOT NULL,
cruser_id int(11) DEFAULT '0' NOT NULL,
children int(11) DEFAULT '0' NOT NULL,
parent_content varchar(255) DEFAULT '' NOT NULL,
PRIMARY KEY (uid)
);
The important TCA-part of the column children of the table tx_intranet_domain_model_parenttable
'children' => [
'exclude' => false,
'label' => 'Child records',
'config' => [
'type' => 'inline',
'foreign_table' => 'tx_intranet_domain_model_childtable',
'foreign_field' => 'parent_uid',
'foreign_table_field' => 'parent_table',
'foreign_match_fields' => [ 'type' => 3, ],
'overrideChildTca' => [
'columns' => [ 'type' => [ 'config' => [ 'default' => 3, ], ], ],
'types' => [ '3' => [ 'showitem' => '--div--;General,parent_content,child_content' ], ], // only an example here
],
'appearance' => [
'newRecordLinkTitle' => 'New child record',
]
],
],
Now we have to create the domain models, a Controller with an action. It's not shown here.
Please create a parent record in the backend with "who is who" in the field parent_content.
You MAY NOT create child-records for this demonstration purpose.
So I build this query in a method of my repositoty:
class ParenttableRepository extends \TYPO3\CMS\Extbase\Persistence\Repository
{
/**
* @var \TYPO3\CMS\Extbase\Persistence\Generic\Typo3QuerySettings
*/
protected $defaultQuerySettings;
public function initializeObject()
{
$this->defaultQuerySettings = $this->objectManager->get(\TYPO3\CMS\Extbase\Persistence\Generic\Typo3QuerySettings::class);
$this->defaultQuerySettings->setRespectStoragePage(false);
}
public function bugDemo() {
$query = $this->createQuery();
$query->matching(
$query->logicalOr(
$query->like('parent_content', '%who%'),
$query->like('children.child_content', '%who%')
)
)
->execute();
// BEGIN: Let's debug
\TYPO3\CMS\Core\Utility\DebugUtility::debug($result);
$queryParser = $this->objectManager->get(\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::class);
echo $queryParser->convertQueryToDoctrineQueryBuilder($query)->getSQL();
// END: Let's debug
return $result;
}
}
The result is empty and we got the following SQL-Query:
SELECT `tx_intranet_domain_model_parenttable`.*
FROM `tx_intranet_domain_model_parenttable` `tx_intranet_domain_model_parenttable`
LEFT JOIN `tx_intranet_domain_model_childtable` `tx_intranet_domain_model_childtable`
ON `tx_intranet_domain_model_parenttable`.`uid` = `tx_intranet_domain_model_childtable`.`parent_uid`
WHERE
(
(`tx_intranet_domain_model_childtable`.`type` = :dcValue2)
AND
(`tx_intranet_domain_model_childtable`.`parent_table` = :dcValue3)
)
AND
(
(`tx_intranet_domain_model_parenttable`.`parent_content` LIKE :dcValue1)
OR
(`tx_intranet_domain_model_childtable`.`child_content` LIKE :dcValue4)
)
As you can see, the existence of a child record is a must have for this conditions in a LEFt JOIN.
The correct SQL-Statement should be:
SELECT `tx_intranet_domain_model_parenttable`.*
FROM `tx_intranet_domain_model_parenttable` `tx_intranet_domain_model_parenttable`
LEFT JOIN `tx_intranet_domain_model_childtable` `tx_intranet_domain_model_childtable`
ON `tx_intranet_domain_model_parenttable`.`uid` = `tx_intranet_domain_model_childtable`.`parent_uid`
WHERE
(
(
(`tx_intranet_domain_model_childtable`.`type` = :dcValue2)
AND
(`tx_intranet_domain_model_childtable`.`parent_table` = :dcValue3)
)
OR
(`tx_intranet_domain_model_childtable`.`uid` IS NULL)
)
AND
(
(`tx_intranet_domain_model_parenttable`.`parent_content` LIKE :dcValue1)
OR
(`tx_intranet_domain_model_childtable`.`child_content` LIKE :dcValue4)
)
I see only two workarounds:
- I could built the complete statement manually. But that would be very complicated because there are lots of other fields in my case.
- I don't use parent_table and type anymore. In that case I'd have to maintain 8 tables instead of one.
So I hope you'll accept this as a bug and will fix it ASAP.
Greetings
Harald
Updated by 
Updated by 
Updated by
Actions