TYPO3 11.5.32 QueryBuilder多JOIN报'gb别名不唯一'错误求助

问题分析 #

你遇到的别名gb不唯一错误，却伴随合法的生成SQL语句，这大概率是TYPO3 11.5版本QueryBuilder在链式调用关联时的内部别名跟踪bug，或是QueryBuilder实例被重复复用导致的。

解决方案 #

1. 使用全新的QueryBuilder实例 #

确保每次查询都从连接池获取全新的QueryBuilder对象，避免复用已添加过JOIN的实例：

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use Doctrine\DBAL\Connection;

// 初始化全新的QueryBuilder
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('fe_users');

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

2. 拆分链式调用为分步赋值 #

避免连续链式调用JOIN方法，改用分步赋值，可能绕过QueryBuilder内部跟踪问题：

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use Doctrine\DBAL\Connection;

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('fe_users');

// 分步构建查询逻辑
$queryBuilder->select('fe_users.uid');
$queryBuilder->from('fe_users');

// 第一个LEFT JOIN
$queryBuilder->leftJoin(
    'fe_users',
    'tx_crewoffice2_domain_model_getback',
    'gb',
    $queryBuilder->expr()->eq(
        'fe_users.uid',
        $queryBuilder->quoteIdentifier('gb.frontenduser')
    )
);

// 第二个LEFT JOIN
$queryBuilder->leftJoin(
    'gb',
    'tx_crewoffice2_domain_model_festival',
    'f',
    $queryBuilder->expr()->eq(
        'gb.festival',
        $queryBuilder->quoteIdentifier('f.uid')
    )
);

// 添加WHERE条件
$queryBuilder->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)
        )
    )
);

// 执行查询
$assigned = $queryBuilder->executeQuery()->fetchAll();

3. 替代方案：直接执行原生SQL #

如果上述方法无效，可直接使用生成的合法SQL执行查询：

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('fe_users');

// 生成目标SQL语句
$sql = $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, \Doctrine\DBAL\Connection::PARAM_INT)
            ),
            $queryBuilder->expr()->eq(
                'gb.festival',
                $queryBuilder->createNamedParameter($festival_uid, \Doctrine\DBAL\Connection::PARAM_INT)
            )
        )
    )
    ->getSQL();

// 执行原生SQL并传入参数
$assigned = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getConnectionForTable('fe_users')
    ->executeQuery($sql, [$crewgroup_uid, $festival_uid])
    ->fetchAll();

内容的提问来源于stack exchange，提问作者Johannes C. Schulz