Issue Bug Resolved

#80752Extbase Repository: No results when joining deleted (hidden, ...) child records

Christian Chabek 04/07/17, 15:44 updated 10/23/26, 15:30

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

  1. Morton Jonuschat commented and changed status New Needs Feedback <span data-processor="relativetime" data-value="2017-04-08T20:44:53+00:00" > 04/08/17, 20:44 </span>

    Can you provide a bit more detail, from the results these two statements (slightly simplified) should be identical:

    SELECT * FROM `parent` LEFT JOIN `child` ON `parent`.`uid` = `child`.`parent_id` WHERE (`parent`.`title` LIKE '%Parent%' OR `child`.`title` LIKE '%Parent%') AND (`parent`.`deleted` = 0 AND `parent`.`hidden` = 0) AND ((`child`.`deleted` = 0 AND `child`.`hidden` = 0) OR `child`.`uid` IS NULL) 
    SELECT * FROM `parent` LEFT JOIN `child` ON `parent`.`uid` = `child`.`parent_id` AND `child`.`deleted` = 0 AND `child`.`hidden` = 0 WHERE (`parent`.`title` LIKE '%Parent%' OR `child`.`title` LIKE '%Parent%') AND (`parent`.`deleted` = 0 AND `parent`.`hidden` = 0)

    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?

  2. 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

  3. Benni Mack commented and changed status New Needs 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?

  4. Gerrit Code Review pushed patch set 1 for main to change 96301 status Needs Feedback Under Review <span data-processor="relativetime" data-value="2026-10-02T08:29:19+00:00" > 10/02/26, 08:29 </span>

  5. Georg Ringer commented and changed status Under Review Resolved done 0 % 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.

  6. Gerrit Code Review pushed patch set 2 for 14.3 to change 97063 status Resolved Under Review <span data-processor="relativetime" data-value="2026-10-23T14:08:44+00:00" > 10/23/26, 14:08 </span>

  7. Georg Ringer commented and changed status Under Review Resolved <span data-processor="relativetime" data-value="2026-10-23T15:30:14+00:00" > 10/23/26, 15:30 </span>

    Applied in changeset commit:ba1423e89b403b02eaa747ab34f57580ac34d29f.