Issue Bug Closed

#93337Querybuilder builds conditions for foreign_table_field and/or foreign_match_fields without "OR ...uid IS NULL" for joined Table

Harald Witt 01/21/21, 12:05 updated 07/09/24, 12:45

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.

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:

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:
  1. I could built the complete statement manually. But that would be very complicated because there are lots of other fields in my case.
  2. 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

Reviews 3