Bug #86385

QueryBuilder restrictions break leftJoin

Added by Marco Pfeiffer about 2 years ago. Updated 5 months ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2018-09-26
Due date:
% Done:

0%

TYPO3 Version:
8
PHP Version:
7.2
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

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

Example:

SELECT *
FROM pages
LEFT JOIN tt_content ON tt_content.pid = pages.uid
# added by restrictions
WHERE pages.deleted = 0 AND tt_content.deleted = 0

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

SELECT *
FROM pages
LEFT JOIN tt_content ON tt_content.pid = pages.uid AND tt_content.deleted = 0
WHERE pages.deleted = 0

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


Related issues

Related to TYPO3 Core - Bug #88919: QueryBuilder with DefaultRestrictionContainer effectivly transforms outer joins to inner joins because the restrictions are applied to all join tables unconditionally Under Review 2019-08-05
Duplicated by TYPO3 Core - Bug #32539: Using a constraint or ordering on a property on the right side of a m:n relation causes records from the left side with no related elements (0 cardinality) to not show Closed 2011-12-14

History

#1 Updated by Marco Pfeiffer about 2 years ago

  • Priority changed from Should have to Must have

This issue seems to exist in typo3 9 too.

#2 Updated by Marco Pfeiffer about 2 years ago

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();
    }

#3 Updated by Florian Rival about 2 years ago

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();

#4 Updated by ondro no-lastname-given over 1 year ago

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

#5 Updated by Matthias Meusburger about 1 year ago

  • Related to Bug #88919: QueryBuilder with DefaultRestrictionContainer effectivly transforms outer joins to inner joins because the restrictions are applied to all join tables unconditionally added

#6 Updated by Nicolai Schirawski 9 months ago

I can confirm the issue.

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

#7 Updated by Tobias Doll 8 months ago

  • Related to deleted (Bug #88919: QueryBuilder with DefaultRestrictionContainer effectivly transforms outer joins to inner joins because the restrictions are applied to all join tables unconditionally)

#8 Updated by Tobias Doll 8 months ago

  • Related to Bug #88919: QueryBuilder with DefaultRestrictionContainer effectivly transforms outer joins to inner joins because the restrictions are applied to all join tables unconditionally added

#9 Updated by Susanne Moog 8 months ago

  • Status changed from New to Closed

Thanks for all your analysis and reports. I'm going to close this issue here, let's continue in #88919 - the pending patch there will also fix this issue.

If you are able to, please test the patch attached to that issue and give feedback.

#10 Updated by Susanne Moog 8 months ago

  • Duplicated by Bug #32539: Using a constraint or ordering on a property on the right side of a m:n relation causes records from the left side with no related elements (0 cardinality) to not show added

Also available in: Atom PDF