Bug #80752
open
Extbase Repository: No results when joining deleted (hidden, ...) child records
0%
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 ...)
Files
Updated by Morton Jonuschat over 7 years ago
- Status changed from New to Needs Feedback
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?
Updated by Christian Chabek over 7 years ago
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
Updated by Alexander Opitz almost 7 years ago
- Status changed from Needs Feedback to New