Project

General

Profile

Actions

Bug #57629

closed

Wrong SQL-Query with tow joins on same table

Added by Hubertus Golf almost 10 years ago. Updated almost 10 years ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Extbase
Target version:
-
Start date:
2014-04-03
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
6.2
PHP Version:
5.3
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

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


Related issues 1 (0 open1 closed)

Is duplicate of TYPO3 Core - Feature #55959: Joins on SubClassesClosed2014-02-13

Actions
Actions #1

Updated by Marc Bastian Heinrichs almost 10 years ago

  • Status changed from New to Closed
  • Target version deleted (next-patchlevel)

close this as duplicate of #55959

Actions #2

Updated by Felix Oertel almost 10 years ago

Thanks Hubertus for reporting that. We know this has been broken for a long time now. If you have any idea, how to fix it, please feel free to push a patch or get in touch with us. We will have a look eventaully, but it is not a high prio topic right now.

Actions #3

Updated by Hubertus Golf almost 10 years ago

Hi Felix,
I could help you to patch it.
is there a chance to come in contact with some one, who can explain me a little bit the structure of the querybuilder.

Felix Oertel wrote:

Thanks Hubertus for reporting that. We know this has been broken for a long time now. If you have any idea, how to fix it, please feel free to push a patch or get in touch with us. We will have a look eventaully, but it is not a high prio topic right now.

Actions

Also available in: Atom PDF