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
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() ;
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.
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
max are not possible.
The second approach is much more flexible:
$queryBuilder ->select('type') ->addSelectLiteral('COUNT(*) AS ' . $queryBuilder->quoteIdentifier('count')) ->from('table') ->groupBy('type') ->execute() ;
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.