Doctrine2 DQL Syntax error when ordering by count

Question

This is Doctrine Repository function

public function mostReadArticleByUser($userId){
    $total = $this->createQueryBuilder('ar')
        ->select('ar.articleId', 'COUNT(ar)')
        ->where('ar.authorId = :userId')
        ->groupBy('ar.articleId')
        ->orderBy('COUNT(ar)', 'DESC')
        ->setMaxResults(1)
        ->setParameter('userId', $userId)
        ->getQuery()
        ->getResult();

    return $total;
}

which should be equivalent to this query

SELECT article_id, count(id)
    FROM profile_article_reads
    WHERE author_id = 2
    GROUP BY article_id
    Order by count(id) DESC
    LIMIT 1;

When I execute this code I get error

Error: Expected end of string, got '('

QueryException: SELECT ar.articleId, COUNT(ar) FROM SciProfileBundle\Entity\ProfileArticleReads ar WHERE ar.authorId = :userId GROUP BY ar.articleId ORDER BY COUNT(ar) DESC

Answer 1

THe count funtion accept a field, so try with

COUNT(ar.id)

instead of:

COUNT(ar)

Probably for sorting is better using an alias, as example:

public function mostReadArticleByUser($userId){
    $total = $this->createQueryBuilder('ar')
        ->select('ar.articleId', 'COUNT(ar.id) as total')
        ->where('ar.authorId = :userId')
        ->groupBy('ar.articleId')
        ->orderBy('total', 'DESC')
        ->setMaxResults(1)
        ->setParameter('userId', $userId)
        ->getQuery()
        ->getResult();

    return $total;
}

Hope this help