TYPO3 Core

I have build a first workaround and prove of concept.
This method will apply all restrictions to their respective join and remove them from the restriction container.
It will be hard to undo this like the exec method does right now but the query builder could simply be cloned in exec.

    public static function resolveRestriction(QueryBuilder $qb)
    {
        $restrictions = $qb->getRestrictions();

        // there is no access to this outside the query builder so i'll have to make myself one
        $unquoteMethod = new \ReflectionMethod(get_class($qb), 'unquoteSingleIdentifier');
        $unquoteClosure = $unquoteMethod->getClosure($qb);

        $fromTables = array_combine(
            array_map($unquoteClosure, array_column($qb->getQueryPart('from'), 'alias')),
            array_map($unquoteClosure, array_column($qb->getQueryPart('from'), 'table'))
        );
        $fromCondition = $restrictions->buildExpression($fromTables, $qb->expr());
        if ($fromCondition->count() > 0) {
            $qb->andWhere($fromCondition);
        }

        $joins = $qb->getQueryPart('join');
        $qb->resetQueryPart('join');
        foreach ($joins as $fromAlias => $furtherJoins) {
            foreach ($furtherJoins as $join) {
                $joinCondition = $qb->expr()->andX();
                if ($join['joinCondition'] !== null) {
                    $joinCondition->add($join['joinCondition']);
                }

                $joinTables = [$unquoteClosure($join['joinAlias']) => $unquoteClosure($join['joinTable'])];
                $joinExpression = $restrictions->buildExpression($joinTables, $qb->expr());
                if ($joinExpression->count() > 0) {
                    $joinCondition->add($joinExpression);
                }

                if ($joinCondition->count() === 0) {
                    $joinCondition = null;
                }

                $join['joinCondition'] = $joinCondition;
                $qb->getConcreteQueryBuilder()->add('join', [$fromAlias => $join], true);
            }
        }

        $restrictions->removeAll();
    }

Same problem for me, restrictions for left join (and for right join) are set in where clause and not in join as it should be.

A workaround, is :

. remove restrictions
. add restriction manually
. set where clause in join with restrictions

With Marco example :

$queryBuilder->getRestrictions()->removeAll();
$events = $queryBuilder->select('*')
    ->from('pages')
    ->leftJoin(
        'pages',
        'tt_content',
        'tt_content',
        'tt_content.pid = pages.uid AND tt_content.deleted = 0'
    )
    ->where(
        $queryBuilder->expr()->eq('pages.deleted', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
        $queryBuilder->expr()->eq('pages.hidden', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT))
        // Also add restrictions with startdate and endate if required
    )
    ->execute()
    ->fetchAll();

Marco Pfeiffer wrote:

All QueryBuilder restrictions append their restriction as "WHERE". This breaks left joins if a joined row is deleted/hidden.

Example:
[...]

If there is a page with only deleted content elements than the entire page is removed from the result. The query should look like this

[...]

In that example, if all content elements of a page are deleted, than the page is still selected but without content elements.

Can confirm. It's quite heavy bug

I can confirm the issue.

Another suggestion to solve this issue: create the possibility to add a tablename to the RestrictionBuilder.

Solved with extension https://extensions.typo3.org/extension/cc_dbal_leftjoin/
@see https://forge.typo3.org/issues/88919

I just had this issue too, but not with v8 but v10 LTS of the core.
In v8 its actually working which is the reason i detected the non-functionality in v10.