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 ?
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