Search code examples
doctrinetypo3query-builder

QueryBuilder Doctrine Left Join

Need some help to translate this correctly.

Old:

$categories = $GLOBALS["TYPO3_DB"]->exec_SELECTgetRows(
    'c.uid, c.id, c.name AS categoryName, c.tstamp', 
    'tx_registration_domain_model_product AS p LEFT JOIN ' 
      . 'tx_registration_domain_model_category AS c ON p.category = c.uid', 
    "p.hidden = 0 AND p.deleted =0 AND c.hidden = 0 "
      . "AND c.deleted =0 AND c.name != '' AND p.material = '"
      . $materialUid . "'", 
    'p.category'
);

New:

$catConstraints = [
    $queryBuilder->expr()->neq(
        'c.name',
        $queryBuilder->createNamedParameter('')
    ),
    $queryBuilder->expr()->eq(
        'p.material',
        $queryBuilder->createNamedParameter($materialUid, \PDO::PARAM_INT)
    )
];

$categories = $queryBuilder
    ->select('c.uid', 'c.id', 'c.name', 'c.tstamp')
    ->from($productsTable, 'p')
    ->leftJoin(
        'p',
        $categoriesTable,
        'c',
        $queryBuilder->expr()->eq(
            'p.category',
            $queryBuilder->quoteIdentifier('c.uid')
        )
    )
    ->where(...$catConstraints)
    ->groupBy('p.category')
    ->executeQuery();

The given alias 'c' is not unique in FROM and JOIN clause table. The currently registered aliases are: tx_registration_domain_model_material, p, c.

Solution

  • The syntax looks correct so far, which can also be looked up here:

    https://docs.typo3.org/m/typo3/reference-coreapi/main/en-us/ApiOverview/Database/QueryBuilder/Index.html#join-innerjoin-rightjoin-and-leftjoin

    
use TYPO3\CMS\Core\Database\Connection;

// SELECT `sys_language`.`uid`, `sys_language`.`title`
// FROM `sys_language`
// INNER JOIN `pages` `p`
//     ON `p`.`sys_language_uid` = `sys_language`.`uid`
// WHERE
//     (`p`.`uid` = 42)
//     AND (
//          (`p`.`deleted` = 0)
//          AND (
//                  (`sys_language`.`hidden` = 0)
//              AND (`overlay`.`hidden` =  0)
//          )
//          AND (`p`.`starttime` <= 1475591280)
//          AND (
//                  (`p`.`endtime` = 0)
//               OR (`overlay`.`endtime` > 1475591280))
//     )
$queryBuilder = $this->connectionPool
    ->getQueryBuilderForTable('sys_language');
$result = $queryBuilder
    ->select('sys_language.uid', 'sys_language.title')
    ->from('sys_language')
    ->join(
        'sys_language',
        'pages',
        'p',
        $queryBuilder->expr()->eq(
            'p.sys_language_uid',
            $queryBuilder->quoteIdentifier('sys_language.uid')
        )
    )
    ->where(
        $queryBuilder->expr()->eq(
            'p.uid',
            $queryBuilder->createNamedParameter(42, Connection::PARAM_INT)
        )
    )
    ->executeQuery();

    From doctrine/dbal code, this should also work with your code:

    $qb = $conn->createQueryBuilder()
    ->select('u.id', 'p.id')
    ->from('users', 'u')
    ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');

    You are using two variables, one for the fromTableName ($productsTable) and one for the joinTableName ($categoriesTable). Reading the error message:

    The given alias 'c' is not unique in FROM and JOIN clause table. The currently registered aliases are: tx_registration_domain_model_material, p, c.

    and the fact that there is only one table and two aliases listed, that you either have:

    • reused the same QueryBuilder instance $queryBuilder to do a second query, along with a second from statement (which is not supported)
    • either $productsTable or $categoriesTable or both is/are an empty string

    So ensure that you really create a new QueryBuilder instance for the new query, e.g. like so:

    $queryBuilder = $connection->createQueryBuilder();
// or $queryBuilder = $connection->getQueryBuilder();
// depending on the TYPO3 version and doctrine/dbal version
$catConstraints = [ /*...*/ ];

    and recheck the contents of the two variables.

    Edit:

    Using the \PDO constants should be avoided. Instead use the Connection::PARAM_* class constants instead. That would be more future proof, because doctrine/dbal 4.0 would not support integer types anymore and these are replaced with PHP Enums. From TYPO3 core side, it's planned to redefine the Connection::PARAM_* constants with the enums in TYPO3 v13 together with the doctrine/dbal 4.0 upgrade.

    Or you can use the doctrine ParamType::* contants in TYPO3 v12 and doctrine v12 which are replaced by PHP Enums with doctrine/dbal 4.0. Would advice to use the TYPO3 wrapping constants.