Typo3 query builder

Question

I just create a SQL Statement and want to put this in a valid query builder request in my Typo3 Extension.

The SQL is

SELECT FROM_UNIXTIME(tx_operations_domain_model_operation.begin, '%Y') as year,
    count(tx_operations_operation_type_mm.`uid_foreign`) as cont,
    tx_operations_domain_model_type.`title`
    FROM tx_operations_domain_model_operation

    INNER JOIN
    tx_operations_operation_type_mm ON (`tx_operations_operation_type_mm`.`uid_local` = tx_operations_domain_model_operation.uid)
    INNER JOIN
    tx_operations_domain_model_type ON (`tx_operations_domain_model_type`.`uid` = `tx_operations_operation_type_mm`.`uid_foreign`)

    GROUP BY FROM_UNIXTIME(tx_operations_domain_model_operation.begin, '%Y'), tx_operations_operation_type_mm.`uid_foreign`;

This is what I tryed in my Controller

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_operations_domain_model_operation');
    $statement = $queryBuilder
        ->select('tx_operations_domain_model_operation.begin, \'%Y\') as year', 'count(tx_operations_operation_type_mm.`uid_foreign`) as cont')
        ->from('tx_operations_domain_model_operation')
        ->join(
            'tx_operations_domain_model_operation',
            'tx_operations_operation_type_mm',
            $queryBuilder->expr()->eq('tx_operations_operation_type_mm.uid_local', $queryBuilder->quoteIdentifier('tx_operations_domain_model_operation.uid'))
        )
        ->join(
            'tx_operations_operation_type_mm',
            'tx_operations_domain_model_type',
            $queryBuilder->expr()->eq('tx_operations_domain_model_type.uid_local', $queryBuilder->quoteIdentifier('tx_operations_operation_type_mm.uid_foreign'))
        )
        ->groupBy('UNIXTIME(tx_operations_domain_model_operation.begin, \'%Y\')', 'tx_operations_operation_type_mm.`uid_foreign')
        ->execute();

Any Idea how the query builder representation has to look like?

You should post a snippet of your code to show what you have tried so far. This way people can give you a more specific help. — Mathias Brodala
Are you sure that you can start database queries in the controller? I would recommend to handle this in a repository. This is at least better practice, and may result in working code. — Stefan Padberg

Utheron Utheron · Accepted Answer · 2018-06-11T12:13:14

I have been playing with QueryBuilder during my internship and I think you are missing an alias :

   ->join(
      'tt_content_orig',
      'sys_language',
      'sys_language', /** Insert alias name, in this case it's the same name **/
      $queryBuilder->expr()->eq(
         'tt_content_orig.sys_language_uid',
         $queryBuilder->quoteIdentifier('sys_language.uid')
      )

And I've been working in the repository file.

Typo3 query builder

1 Answers