#79286Multiple wrong joins in extbase database query if constraints use property paths
Description
I have the following constraint in my extbase repository:
$query->matching(
$query->logicalOr([
// start and end are inside the period
$query->logicalAnd([
$query->greaterThanOrEqual('dateCycles.dateStart', $start),
$query->lessThanOrEqual('dateCycles.dateEnd', $end),
]),
// starts before the period but ends in period
$query->logicalAnd([
$query->lessThanOrEqual('dateCycles.dateStart', $start),
$query->greaterThanOrEqual('dateCycles.dateEnd', $start),
]),
// starts before period and ends after period
$query->logicalAnd([
$query->lessThanOrEqual('dateCycles.dateStart', $end),
$query->greaterThanOrEqual('dateCycles.dateEnd', $end),
]),
// starts in period and ends after period
$query->logicalAnd([
$query->greaterThanOrEqual('dateCycles.dateStart', $start),
$query->lessThanOrEqual('dateCycles.dateEnd', $end),
]),
])
);
a got a pretty confusing result. I had absolutely no idea why this query returned just results that dont match any of this constraints, so i checked the Raw SQL query that was executed on the database:
SELECT `tx_myextension_domain_model_appointment`.*
FROM `tx_myextension_domain_model_appointment` `tx_myextension_domain_model_appointment`
LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle`
ON `tx_myextension_domain_model_appointment`.`uid` = tx_myextension_domain_model_datecycle.appointment
LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle0`
ON `tx_myextension_domain_model_datecycle`.`uid` = tx_myextension_domain_model_datecycle0.appointment
LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle1`
ON `tx_myextension_domain_model_datecycle0`.`uid` = tx_myextension_domain_model_datecycle1.appointment
LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle2`
ON `tx_myextension_domain_model_datecycle1`.`uid` = tx_myextension_domain_model_datecycle2.appointment
LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle3`
ON `tx_myextension_domain_model_datecycle2`.`uid` = tx_myextension_domain_model_datecycle3.appointment
LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle4`
ON `tx_myextension_domain_model_datecycle3`.`uid` = tx_myextension_domain_model_datecycle4.appointment
LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle5`
ON `tx_myextension_domain_model_datecycle4`.`uid` = tx_myextension_domain_model_datecycle5.appointment
LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle6`
ON `tx_myextension_domain_model_datecycle5`.`uid` = tx_myextension_domain_model_datecycle6.appointment
LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle7`
ON `tx_myextension_domain_model_datecycle6`.`uid` = tx_myextension_domain_model_datecycle7.appointment
WHERE (((((`tx_myextension_domain_model_datecycle`.`date_start` >= 1483225200) AND
(`tx_myextension_domain_model_datecycle0`.`date_end` <= 1485817200)) OR
((`tx_myextension_domain_model_datecycle1`.`date_start` <= 1483225200) AND
(`tx_myextension_domain_model_datecycle2`.`date_end` >= 1483225200))) OR
((`tx_myextension_domain_model_datecycle3`.`date_start` <= 1485817200) AND
(`tx_myextension_domain_model_datecycle4`.`date_end` >= 1485817200))) OR
((`tx_myextension_domain_model_datecycle5`.`date_start` >= 1483225200) AND
(`tx_myextension_domain_model_datecycle6`.`date_end` <= 1485817200))) AND
(`tx_myextension_domain_model_appointment`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_appointment`.`deleted` = 0) AND
(`tx_myextension_domain_model_appointment`.`hidden` = 0) AND
(`tx_myextension_domain_model_appointment`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_appointment`.`endtime` = 0) OR
(`tx_myextension_domain_model_appointment`.`endtime` > 1484216220)) AND ((
(`tx_myextension_domain_model_appointment`.`fe_groups` = '') OR
(`tx_myextension_domain_model_appointment`.`fe_groups` IS NULL) OR
(`tx_myextension_domain_model_appointment`.`fe_groups` = 0) OR
(FIND_IN_SET('0', `tx_myextension_domain_model_appointment`.`fe_groups`)) OR
(FIND_IN_SET('-1', `tx_myextension_domain_model_appointment`.`fe_groups`))))) AND (
((`tx_myextension_domain_model_datecycle`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_datecycle`.`deleted` = 0) AND
(`tx_myextension_domain_model_datecycle`.`hidden` = 0) AND
(`tx_myextension_domain_model_datecycle`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_datecycle`.`endtime` = 0) OR
(`tx_myextension_domain_model_datecycle`.`endtime` > 1484216220)))) OR
(`tx_myextension_domain_model_datecycle`.`uid` IS NULL)) AND (
((`tx_myextension_domain_model_datecycle0`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_datecycle0`.`deleted` = 0) AND
(`tx_myextension_domain_model_datecycle0`.`hidden` = 0) AND
(`tx_myextension_domain_model_datecycle0`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_datecycle0`.`endtime` = 0) OR
(`tx_myextension_domain_model_datecycle0`.`endtime` > 1484216220)))) OR
(`tx_myextension_domain_model_datecycle0`.`uid` IS NULL)) AND (
((`tx_myextension_domain_model_datecycle1`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_datecycle1`.`deleted` = 0) AND
(`tx_myextension_domain_model_datecycle1`.`hidden` = 0) AND
(`tx_myextension_domain_model_datecycle1`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_datecycle1`.`endtime` = 0) OR
(`tx_myextension_domain_model_datecycle1`.`endtime` > 1484216220)))) OR
(`tx_myextension_domain_model_datecycle1`.`uid` IS NULL)) AND (
((`tx_myextension_domain_model_datecycle2`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_datecycle2`.`deleted` = 0) AND
(`tx_myextension_domain_model_datecycle2`.`hidden` = 0) AND
(`tx_myextension_domain_model_datecycle2`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_datecycle2`.`endtime` = 0) OR
(`tx_myextension_domain_model_datecycle2`.`endtime` > 1484216220)))) OR
(`tx_myextension_domain_model_datecycle2`.`uid` IS NULL)) AND (
((`tx_myextension_domain_model_datecycle3`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_datecycle3`.`deleted` = 0) AND
(`tx_myextension_domain_model_datecycle3`.`hidden` = 0) AND
(`tx_myextension_domain_model_datecycle3`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_datecycle3`.`endtime` = 0) OR
(`tx_myextension_domain_model_datecycle3`.`endtime` > 1484216220)))) OR
(`tx_myextension_domain_model_datecycle3`.`uid` IS NULL)) AND (
((`tx_myextension_domain_model_datecycle4`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_datecycle4`.`deleted` = 0) AND
(`tx_myextension_domain_model_datecycle4`.`hidden` = 0) AND
(`tx_myextension_domain_model_datecycle4`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_datecycle4`.`endtime` = 0) OR
(`tx_myextension_domain_model_datecycle4`.`endtime` > 1484216220)))) OR
(`tx_myextension_domain_model_datecycle4`.`uid` IS NULL)) AND (
((`tx_myextension_domain_model_datecycle5`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_datecycle5`.`deleted` = 0) AND
(`tx_myextension_domain_model_datecycle5`.`hidden` = 0) AND
(`tx_myextension_domain_model_datecycle5`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_datecycle5`.`endtime` = 0) OR
(`tx_myextension_domain_model_datecycle5`.`endtime` > 1484216220)))) OR
(`tx_myextension_domain_model_datecycle5`.`uid` IS NULL)) AND (
((`tx_myextension_domain_model_datecycle6`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_datecycle6`.`deleted` = 0) AND
(`tx_myextension_domain_model_datecycle6`.`hidden` = 0) AND
(`tx_myextension_domain_model_datecycle6`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_datecycle6`.`endtime` = 0) OR
(`tx_myextension_domain_model_datecycle6`.`endtime` > 1484216220)))) OR
(`tx_myextension_domain_model_datecycle6`.`uid` IS NULL)) AND (
((`tx_myextension_domain_model_datecycle7`.`sys_language_uid` IN (0, -1)) AND (
(`tx_myextension_domain_model_datecycle7`.`deleted` = 0) AND
(`tx_myextension_domain_model_datecycle7`.`hidden` = 0) AND
(`tx_myextension_domain_model_datecycle7`.`starttime` <= 1484216220) AND
((`tx_myextension_domain_model_datecycle7`.`endtime` = 0) OR
(`tx_myextension_domain_model_datecycle7`.`endtime` > 1484216220)))) OR
(`tx_myextension_domain_model_datecycle7`.`uid` IS NULL))
ORDER BY `tx_myextension_domain_model_datecycle7`.`date_start` ASC
I think there should be just one “LEFT JOIN” for each unique property. Here is the result from debugging the File /typo3/sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbQueryParser.php and its property $this->tablePropertyMap:
array(9) {
["dateCycles"] => string(38) "tx_myextension_domain_model_datecycle"
["dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle0"
["dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle1"
["dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle2"
["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle3"
["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle4"
["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle5"
["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle6"
["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"]=> string(39) "tx_myextension_domain_model_datecycle7"
}
So it seems that the following method call is responsible for this issue:
(/typo3/sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbQueryParser.php @Line #903)
$childTableAlias = $this->getUniqueAlias($childTableName, $fullPropertyPath);