Bug #81490
closedMissing DISTINCT when building query with left joins in extbase repository
0%
Description
While migrating an extbase extension from 6.2 to 8.7 I noticed that a custom repository query returned duplicate result rows in T3 V8.7.
In simple terms, the model hierarchy in this example is: a competition can have one or more editions and an edition can have one or more submissionslots.
The task is to retrieve all competitions with at least one valid submission slot (i.e. deadline must not be smaller than defined start date).
The deadline check in the competition repository is done with:
$query->greaterThanOrEqual('editions.submissionSlots.deadline', $start->format('Y-m-d'));
In T3 V6.2 this resulted in the following SQL statement (simplified for readability):
SELECT DISTINCT tx_myext_competition.*
FROM tx_myext_competition
LEFT JOIN tx_myext_edition ON tx_myext_competition.uid=tx_myext_edition.competition
LEFT JOIN tx_myext_submissionslot ON tx_myext_edition.uid=tx_myext_submissionslot.edition
WHERE tx_myext_submissionslot.deadline >= '2017-06-06' [...]
In T3 V8.7 the same query results in:
SELECT tx_myext_competition.*
FROM tx_myext_competition tx_myext_competition
LEFT JOIN tx_myext_edition tx_myext_edition ON tx_myext_competition.uid = tx_myext_edition.competition
LEFT JOIN tx_myext_submissionslot tx_myext_submissionslot ON tx_myext_edition.uid = tx_myext_submissionslot.edition
WHERE tx_myext_submissionslot.deadline >= '2017-06-06' [...]
The crucial difference is the missing DISTINCT, which results in duplicate rows in the result set as a competition can have multiple valid submission slots.
(The table name aliases are also a bit weird, but do no harm...)