TYPO3 QueryBuilder multiple JOINs, but "alias is not unique" error

Question

With TYPO3 11.5.32 I try to do a select by using QueryBuilder.

$assigned = $queryBuilder
            ->select('fe_users.uid')
            ->from('fe_users')
            ->leftJoin(
                'fe_users',
                'tx_crewoffice2_domain_model_getback',
                'gb',
                $queryBuilder->expr()->eq(
                    'fe_users.uid',
                    $queryBuilder->quoteIdentifier('gb.frontenduser')
                )
            )
            ->leftJoin(
                'gb',
                'tx_crewoffice2_domain_model_festival',
                'f',
                $queryBuilder->expr()->eq(
                    'gb.festival',
                    $queryBuilder->quoteIdentifier('f.uid')
                )
            )
            ->where(
                $queryBuilder->expr()->and(
                    $queryBuilder->expr()->inSet(
                        'fe_users.usergroup',
                        $queryBuilder->createNamedParameter($crewgroup_uid, Connection::PARAM_INT)
                    ),
                    $queryBuilder->expr()->eq(
                        'gb.festival',
                        $queryBuilder->createNamedParameter($festival_uid, Connection::PARAM_INT)
                    )
                )
            )
            ->executeQuery()
            ->fetchAll();

This throws an error

Doctrine\DBAL\Query\QueryException The given alias 'gb' is not unique in FROM and JOIN clause table. The currently registered aliases are: fe_users, gb, f.

But the resulting statement ($queryBulder->getSQL()) works as expected:

SELECT `fe_users`.`uid` 
FROM `fe_users` 
LEFT JOIN `tx_crewoffice2_domain_model_getback` `gb` 
  ON (`fe_users`.`uid` = `gb`.`frontenduser`) 
  AND ((`gb`.`deleted` = 0) 
  AND (`gb`.`hidden` = 0) 
  AND (`gb`.`starttime` <= 1700213760) 
  AND ((`gb`.`endtime` = 0) OR (`gb`.`endtime` > 1700213760))) 
LEFT JOIN `tx_crewoffice2_domain_model_festival` `f` 
  ON (`gb`.`festival` = `f`.`uid`) 
  AND ((`f`.`deleted` = 0) 
  AND (`f`.`hidden` = 0) 
  AND (`f`.`starttime` <= 1700213760)
  AND ((`f`.`endtime` = 0) OR (`f`.`endtime` > 1700213760))) 
WHERE 
  (FIND_IN_SET(1, `fe_users`.`usergroup`)) 
  AND (`gb`.`festival` = 2) 
  AND ((`fe_users`.`deleted` = 0) 
    AND (`fe_users`.`disable` = 0) 
    AND (`fe_users`.`starttime` <= 1700213760) 
    AND ((`fe_users`.`endtime` = 0) OR (`fe_users`.`endtime` > 1700213760))
  );

The alias "gb" can't be unique, because I have to use him inside the second LEFT JOIN. The query looks like the second examples at TYPO3 QueryBuilder documentation - using the alias from first JOIN in second JOIN. But it will not work for me.

Any hints, ideas or something better?

Answer 1

TBH I have not tested your code, but your code looks pretty good and not the the issue itself regarding the exception message.

Due to experiences and knowledge of the procedure and code, the exception message:

Doctrine\DBAL\Query\QueryException The given alias 'gb' is not unique in FROM and JOIN clause table. The currently registered aliases are: fe_users, gb, f.

tells us, that there is a try to reuse a already used alias.

The usual cases this exception message occures are:

1. a alias is reused for another join or from clause
2. The QueryBuilder instance and therefore the internal parts are reused.

Reading the provided code, I would rule (1) out. That leads us to option (2).

It's clearly documented that a QueryBuilder instance must not be reused. Instead create a new instance before creating the query.

Sadly, the provided code does not show the creation of the QueryBuilder instance.

Please create a new QueryBuilder instance in $queryBuilder each time before building a query. That means, instead of

$assigned = $queryBuilder
  ->select('fe_users.uid')
  ->from('fe_users')
    // ... further query builder code
  ->executeQuery()
  ->fetchAll();            

do following:

$connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);

// ALWAYS create a new QueryBuilder instance before creating a instance,
// NEWER reuse a existing one. Saves from headaches.
$queryBuilder = $connectionPool->getQueryBuilderForTable('fe_users');

$assigned = $queryBuilder
  ->select('fe_users.uid')
  ->from('fe_users')
    // ... further query builder code
  ->executeQuery()
  ->fetchAll();            

But the resulting statement ($queryBulder->getSQL()) works as expected:

The first time it works, but not on the second time. I would bet on a not reconized execution disorder here due to not having insight into the full code implementation.

Answer 2

All the joins are actually joining with fe_users not with any of the aliases. So the SQL-query is correct, but the doctrine code won't produce that query.

Try this one instead:

$assigned = $queryBuilder
        ->select('fe_users.uid')
        ->from('fe_users')
        ->leftJoin(
            'fe_users',
            'tx_crewoffice2_domain_model_getback',
            'gb',
            $queryBuilder->expr()->eq(
                'fe_users.uid',
                $queryBuilder->quoteIdentifier('gb.frontenduser')
            )
        )
        ->leftJoin(
            'fe_users',
            'tx_crewoffice2_domain_model_festival',
            'f',
            $queryBuilder->expr()->eq(
                'gb.festival',
                $queryBuilder->quoteIdentifier('f.uid')
            )
        )
        ->where(
            $queryBuilder->expr()->and(
                $queryBuilder->expr()->inSet(
                    'fe_users.usergroup',
                    $queryBuilder->createNamedParameter($crewgroup_uid, Connection::PARAM_INT)
                ),
                $queryBuilder->expr()->eq(
                    'gb.festival',
                    $queryBuilder->createNamedParameter($festival_uid, Connection::PARAM_INT)
                )
            )
        )
        ->executeQuery()
        ->fetchAll();