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.