Bug #93337
Updated by Harald Witt over 3 years ago
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 are not properly nested.
They always appear as a conjunction outside all other conditions.
Here's my detailled demonstration. I'll use my own table names here ...
ext_tables.sql:
<pre>
#
# 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)
);
</pre>
The important TCA-part of the column children of the table tx_intranet_domain_model_parenttable
<pre>
'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',
]
],
],
</pre>
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 create child-records, but these should NOT contain the word "who" for now.
Now I'd like to search for the word "who" which could be in both, the field parent_content and/or the field child_content.
So I build this query in a method of my repositoty:
<pre>
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;
}
}
</pre>
The result is empty and we got the following SQL-Query:
<pre>
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)
)
</pre>
As you can see, the two fields parent_table and type are used in a conjuction (AND) outside the OR-clause. This completely destroys the Query-Result.
The correct SQL-Statement should be:
<pre>
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_parenttable`.`parent_content` LIKE :dcValue1)
OR
(
(`tx_intranet_domain_model_childtable`.`type` = :dcValue2)
AND
(`tx_intranet_domain_model_childtable`.`parent_table` = :dcValue3)
AND
(`tx_intranet_domain_model_childtable`.`child_content` LIKE :dcValue4)
)
)
</pre>
Another possibility (and surely easier to implement) wold be:
<pre>
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)
)
</pre>
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