Use SQL aggregate functions (like count) in TYPO3's query builder
Sometimes it can be tricky to create an SQL query with the query builder of TYPO3 — especially when using aggregate functions like count
, sum
, min
or max
. Let's look at an example with pure SQL:
SELECT type, COUNT(*) AS count
FROM table
GROUP BY type;
If you want to implement the above statement using the query builder:
/** @var \TYPO3\CMS\Core\Database\Query\QueryBuilder $queryBuilder */
$queryBuilder
->select('type', 'COUNT(*) AS count')
->from('table')
->groupBy('type')
->execute()
;
The exception Doctrine\DBAL\Exception\InvalidFieldNameException
with the message is thrown:
An exception occurred while executing 'SELECT `type`, `COUNT(*)` AS `count` FROM `table` GROUP BY `type`: Unknown column 'COUNT(*)' in 'field list'
As you can see in the embedded SQL query of the exception message, the COUNT(1)
is quoted (look at the backticks). The TYPO3 query builder does this before passing the field to the underlying Doctrine DBAL library.
Using the count() method with addSelect()
When you want to count some numbers, you can use the count()
method of the query builder and add additional fields afterwards:
$queryBuilder
->count('*')
->addSelect('type')
->from('table')
->groupBy('type')
->execute()
;
This variant lacks the possibility to add an alias for the count — this would be nice when accessing the count later with an appropriate name. Also other aggregate functions like min
or max
are not possible.
Using addSelectLiteral()
The second approach is much more flexible:
$queryBuilder
->select('type')
->addSelectLiteral('COUNT(*) AS ' . $queryBuilder->quoteIdentifier('count'))
->from('table')
->groupBy('type')
->execute()
;
The addSelectLiteral()
method passes the expression unquoted to the concrete query builder, so you have to quote an identifier on your own. The method selectLiteral()
is also available.
That's it — quite easy, if you know how to do it.