Doctrine2 error "Expected known function, got 'COUNT'" when in order by clause

Question

I'm using doctrine2

I try to get the number of "Events" ordered by number of tickets sold

$manager = $this->getDoctrine()->getManager();
$builder = $manager->createQueryBuilder();

return $builder
    ->select('e')
    ->from('AppBundle:Event', 'e')
    ->leftJoin('e.tickets', 't')
    ->orderBy('COUNT(t)')
    ->groupBy('e.id')
    ->setMaxResults(10)
    ->getQuery()
    ->getResult()
;

This produce the error

[Syntax Error] line 0, col 80: Error: Expected known function, got 'COUNT'

With the following DQL produce

QueryException: SELECT e FROM AppBundle:Event e LEFT JOIN e.tickets t GROUP BY e.id ORDER BY COUNT(t.id) ASC

However if I put the COUNT in the SELECT part, it does work correctly (but then I have to do additional treatment to remove the unwanted field of the SELECT)

return $builder
    ->select('e')
    ->from('AppBundle:Event', 'e')
    ->addSelect('COUNT(t) AS nbrTicketsSold')
    ->leftJoin('e.t', 't')
    ->orderBy('nbrTicketsSold')
    ->groupBy('e.id')
    ->setMaxResults(10)
    ->getQuery()
    ->getResult()
;

Is using COUNT in an ORDER BY clause a known limitation of DQL ?

Answer 1

The EBNF in the doctrine docs actually state what's possible in the ORDER BY clause:

OrderByClause       ::= "ORDER" "BY" OrderByItem {"," OrderByItem}*
OrderByItem ::= (SimpleArithmeticExpression | SingleValuedPathExpression | ScalarExpression | ResultVariable | FunctionDeclaration) ["ASC" | "DESC"]

With FunctionDeclaration containing these functions:

FunctionsReturningNumerics: LENGTH LOCATE ABS SQRT MOD SIZE DATE_DIFF BIT_AND BIT_OR

FunctionsReturningDateTime: CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DATE_ADD DATE_SUB

FunctionsReturningStrings: CONCAT SUBSTRING TRIM LOWER UPPER IDENTITY

COUNT is part of the AggregateExpression group (Link), those are not allowed in the ORDER BY clause.

Furthermore, the docs actually have an example further up using AS HIDDEN to order by count:

Retrieve user entities with Arithmetic Expression in ORDER clause, using the HIDDEN keyword:

$query = $em->createQuery('SELECT u, u.posts_count + u.likes_count AS HIDDEN score FROM CmsUser u ORDER BY score');
$users = $query->getResult(); // array of User objects