#80752Extbase Repository: No results when joining deleted (hidden, ...) child records
Description
I have two tables, let’s call them parent and child, in a 1:n relation, which I query like this:
$constraints[] = $query->like('title', $q);
$constraints[] = $query->like('child.title', $q);
$query->matching(
$query->logicalOr(
$constraints
),
);
The generated SQL looks something like this, no problem so far:
SELECT DISTINCT parent.* FROM parent LEFT JOIN child AS child ON parent.uid = child.parent where parent.title LIKE '%query%' OR child.title LIKE '%query%'
Typo3 now adds checks for all the deleted, hidden, … fields:
AND ((child.deleted=0 AND child.t3ver_state<=0 AND child.pid<>-1 AND child.hidden=0 AND child.starttime<=1491496260
AND (child.endtime=0 OR child.endtime>1491496260))
OR child.uid IS NULL)
If I have a deleted child record, this record gets joined:
LEFT JOIN child AS child ON parent.uid = child.parent
But even if the like query would match, I get no results, because of this:
WHERE ... AND ((child.deleted = 0 ...
I think, this check (and maybe all the hidden, enabled, … fields too) should be in the join:
LEFT JOIN child AS child ON (parent.uid = child.parent and child.deleted = 0 and child.hidden = 0 AND ...)
Reviews 2
Files 1
History 69
-
Morton Jonuschat commented and changed status
NewNeeds Feedback <span data-processor="relativetime" data-value="2017-04-08T20:44:53+00:00" > 04/08/17, 20:44 </span> -
Christian Chabek commented and changed attached test.zip <span data-processor="relativetime" data-value="2017-04-10T10:40:40+00:00" > 04/10/17, 10:40 </span>
The IS NULL works if there is no child. If there is a deleted child, it gets joined but as it is deleted, the whole row doesn’t pass the where clause :)
I made a small sample extension. SQL dump is also provided.
- insert frontend plugin, there should be one item “ParentObjectWithDemoTitel“
- delete or hide the IRRE child object in the backend
- no results -
Alexander Opitz changed status
Needs FeedbackNew <span data-processor="relativetime" data-value="2017-08-28T10:46:15+00:00" > 08/28/17, 10:46 </span> -
Benni Mack commented and changed status
NewNeeds Feedback <span data-processor="relativetime" data-value="2025-07-24T15:07:49+00:00" > 07/24/25, 15:07 </span>Hey Christian,
I cannot reproduce this anymore in latest main / 13 LTS. Can you re-check please?
-
Gerrit Code Review pushed patch set 1 for main to change 96301 status
Needs FeedbackUnder Review <span data-processor="relativetime" data-value="2026-10-02T08:29:19+00:00" > 10/02/26, 08:29 </span> -
Gerrit Code Review pushed patch set 1 for 14.3 to change 97063 <span data-processor="relativetime" data-value="2026-10-23T09:15:28+00:00" > 10/23/26, 09:15 </span>
-
Georg Ringer commented and changed status
Under ReviewResolved done0 %100 % <span data-processor="relativetime" data-value="2026-10-23T09:20:16+00:00" > 10/23/26, 09:20 </span>Applied in changeset commit:2d58c55ab4de9d74427263de63cdd9312170134c.
-
Gerrit Code Review pushed patch set 2 for 14.3 to change 97063 status
ResolvedUnder Review <span data-processor="relativetime" data-value="2026-10-23T14:08:44+00:00" > 10/23/26, 14:08 </span> -
Georg Ringer commented and changed status
Under ReviewResolved <span data-processor="relativetime" data-value="2026-10-23T15:30:14+00:00" > 10/23/26, 15:30 </span>Applied in changeset commit:ba1423e89b403b02eaa747ab34f57580ac34d29f.
Can you provide a bit more detail, from the results these two statements (slightly simplified) should be identical:
With the first query the size of the join result might be slightly larger, but the IS NULL should ensure identical results for the final query.
Maybe you can provide some sample dataset (SQL dump) and the full query that shows the problem?