TYPO3 Core

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 for the joined table are build, but the *"OR ...uid IS NULL"* is missing.  
 So not properly nested. 
 They always appear as a conjunction outside all records of the main-table which do not have any child record in the joined table are excluded from the query result.  

 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 may create child-records, but these should NOT create child-records contain the word "who" for this demonstration purpose. 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 existence of a child record is a must have for this conditions two fields parent_table and type are used in a LEFt JOIN. 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

Back