QueryBuilder with DefaultRestrictionContainer effectivly transforms outer joins to inner joins because the restrictions are applied to all join tables unconditionally
In an outer join (left or right join) one of the sides can be null.
But when using the QueryBuilder with it's DefaultRestrictionContainer it adds all conditions to all tables. Which is fine for inner joins but breaks outer joins (by efectivley kicking out rows that are fetched by the outer join, leaving one with teh same result as the inner join).
Consider this example (scenario: content inline in content):
SELECT c1.* FROM tt_content AS c1 LEFT JOIN tt_content AS c2 on c1.parent_field = c2.uid WHERE (c2 IS NULL OR c2.hidden = 0);
This would fetch all content rows but leave out the children whose parent is hidden.
Another example would be a join on pages to find pages by some parent page property.
Now you want this query with correctly respected TYPO3 fields (enable fields, deleted) and use the QueryBuilder. The QueryBuilder will add the DefaultRestriction container. So far so good. It will add the Restrictions to all join tables however, no matter if they're inner or outer joined. So it adds
c1.hidden = 0 AND c2.hidden = 0
which implcitly removes all join rows where the outer side is NULL. Leaving you implicitly with the inner join result.
As soon as an explicit outer join is requested the DefaultRestrictionContainer should only be applied to the from-table, not to the join-tables - or it should also add a
OR join_table.uid IS NULL to the RestrictionContainer in case of outer joins.
For single-table or inner-join queries nothing has to change.
I found this on TYPO3 9, but assume this is true for 8 and 10 as well.
In case this is intended (can't believe this) this should at least be documented prominently - found this only by manually debugging the generated SQL statement to this that my left join condition was "short-circuited" by the DefaultRestrictionContainer.
#3 Updated by Gerrit Code Review 15 days ago
- Status changed from New to Under Review
Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/61880