Bug #75822

Epic #75555: Further Doctrine Development

Wrong backticks when using a doctrine select on count(), max(), min() etc.

Added by Christoph Kratz about 4 years ago. Updated over 1 year ago.

Must have
Database API (Doctrine DBAL)
Target version:
Start date:
Due date:
% Done:


TYPO3 Version:
PHP Version:
Is Regression:
Sprint Focus:


While migrating the old SQL to the new doctrine I found the following issue. You will find the original query under typo3/sysext/filelist/Classes/FileList.php.


/** @var QueryBuilder $queryBuilder */
            $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages','sys_template');
            $result = $queryBuilder
                ->select('pages.uid', 'count(*)', 'max(sys_template.root)', 'min(sys_template.root)')
                    $queryBuilder->expr()->eq('pages.uid', 'sys_template.pid')


Wrong result:

SELECT `pages`.`uid`, `count(*)`, `max(sys_template`.`root)`, `min(sys_template`.`root)` FROM `pages`, `sys_template` WHERE (`pages`.`uid` = sys_template.pid) AND (pages.deleted=0) AND ((pages.t3ver_state <= 0 OR pages.t3ver_wsid = 0)) AND (sys_template.deleted=0) AND ((sys_template.t3ver_state <= 0 OR sys_template.t3ver_wsid = 0)) AND ((`pages`.`hidden` = 0) AND (`pages`.`starttime` <= 1461168600) AND ((`pages`.`endtime` = 0) OR (`pages`.`endtime` > 1461168600)) AND (`pages`.`deleted` = 0) AND (`sys_template`.`hidden` = 0) AND (`sys_template`.`starttime` <= 1461168600) AND ((`sys_template`.`endtime` = 0) OR (`sys_template`.`endtime` > 1461168600)) AND (`sys_template`.`deleted` = 0)) GROUP BY `pages`.`uid` ORDER BY `pages`.`pid` ASC, `pages`.`sorting` ASC LIMIT 1

It should be possible to get something like:

SELECT `pages`.`uid`, count(*), max(`sys_template`.`root`), min(`sys_template`.`root`) FROM `pages`, `sys_template` WHERE ...

Related issues

Related to TYPO3 Core - Bug #75824: Doctrine: migrate ext:tstemplate/TypoScriptTemplateModuleController Closed 2016-04-20

Associated revisions

Revision 8f601d53 (diff)
Added by Morton Jonuschat about 4 years ago

[TASK] Doctrine: Add support for aggregate SQL functions

This patch extends the SQL Expressionbuilder with support for generating
expressions for MIN, MAX, AVG, SUM and COUNT aggregate functions, as
well as adding support for selecting literal (unquoted) SQL expressions
with the QueryBuilder using ->selectLiteral() and ->addSelectLiteral().

Releases: master
Resolves: #75822
Change-Id: Id1043ec8a60be1e6d3c273278a776e92bb8c973e
Reviewed-on: https://review.typo3.org/47826
Reviewed-by: Wouter Wolters <>
Tested-by: Wouter Wolters <>
Reviewed-by: Frank Naegler <>
Tested-by: Frank Naegler <>


#1 Updated by Morton Jonuschat about 4 years ago

  • Category set to Database API (Doctrine DBAL)
  • Status changed from New to Accepted
  • Assignee set to Morton Jonuschat
  • Target version set to 8.1
  • PHP Version set to 7.0

#2 Updated by Gerrit Code Review about 4 years ago

  • Status changed from Accepted to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/47826

#3 Updated by Benni Mack about 4 years ago

  • Target version changed from 8.1 to 8.2

#4 Updated by Morton Jonuschat about 4 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#5 Updated by Stephan Bauer over 2 years ago

I still have this problem with 8.7.7:

        $latestUpdate = $queryBuilder

creates this SQL:

SELECT `MAX(tstamp)`
FROM `tx_decoderdb_domain_model_manufacturer` 
WHERE (`tx_decoderdb_domain_model_manufacturer`.`deleted` = 0) AND (`tx_decoderdb_domain_model_manufacturer`.`hidden` = 0) 
AND (`tx_decoderdb_domain_model_manufacturer`.`starttime` <= 1505941920) 
AND ((`tx_decoderdb_domain_model_manufacturer`.`endtime` = 0) OR (`tx_decoderdb_domain_model_manufacturer`.`endtime` > 1505941920))

#6 Updated by Morton Jonuschat over 2 years ago

Please read the documentation on how to use aggregate functions: https://docs.typo3.org/typo3cms/CoreApiReference/ApiOverview/Database/ExpressionBuilder/Index.html#aggregate-functions
Trying to use a SQL function/fragment in a method that expects a field/table name results in the observed quoting issues.

#7 Updated by Riccardo De Contardi over 2 years ago

  • Status changed from Resolved to Closed

#8 Updated by Harald Holzmann over 1 year ago

Should also possible to use aggregate function ins order by clause. E.g. order by max(tstmp)

#9 Updated by Harald Holzmann over 1 year ago

Here is the full example (get the last 10 edited pages from log):
SELECT MAX, `event_pid`
FROM `sys_log` WHERE (`tablename` = 'pages') OR (`tablename` = 'tt_content')
GROUP BY `event_pid`
ORDER BY max(`tstamp`)

Also available in: Atom PDF