Issue Bug Closed

#57629Wrong SQL-Query with tow joins on same table

Hubertus Golf 04/03/14, 22:37 updated 06/12/14, 00:06

Description

error in TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser

*method*
632 protected function parseJoin(Qom\JoinInterface $join, array &$sql)

if I have to properties, which join to the same object and you want to search for both properties, the sql-join is wrong.

*example:*
homecountry and guestcountry are country-objecta

PHP-CODE
$query = $this->createQuery();
$constraints[] = $query->equals(‘homecountry.code’, $properties[‘homecountry’]);
$constraints[] = $query->equals(‘guestcountry.code’, $properties[‘guestcountry’]);
$query->matching($query->logicalAnd($constraints));
$match = $query->execute()->getFirst();

*SQL Query builded*
SELECT DISTINCT tx_vsoevvscout_domain_model_match.*
from tx_vsoevvscout_domain_model_match
LEFT JOIN tx_vsoevvscout_domain_model_country
ON tx_vsoevvscout_domain_model_match.guestcountry=tx_vsoevvscout_domain_model_country.uid
where ((tx_vsoevvscout_domain_model_country.code = ‘BEL’
AND tx_vsoevvscout_domain_model_country.code = ‘ISR’)

*Resault*
empty result

correct SQL*
SELECT DISTINCT tx_vsoevvscout_domain_model_match.*
from tx_vsoevvscout_domain_model_match
LEFT JOIN tx_vsoevvscout_domain_model_country *AS homecountry

ON tx_vsoevvscout_domain_model_match.homecountry=homecountry.uid
LEFT JOIN tx_vsoevvscout_domain_model_country AS guestcountry
ON tx_vsoevvscout_domain_model_match.guestcountry=guestcountry.uid
where (homecountry.code = ‘BEL’
AND guestcountry.code = ‘ISR’)

*result of correct SQL*
one row as exspected

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.