How to order by count in Doctrine 2?

Question

I'm trying to group my entity by a field (year) and do a count of it.

Code:

public function countYear()
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('b.year, COUNT(b.id)')
        ->from('\My\Entity\Album', 'b')
        ->where('b.year IS NOT NULL')
        ->addOrderBy('sclr1', 'DESC')
        ->addGroupBy('b.year');
    $query = $qb->getQuery();
    die($query->getSQL());
    $result = $query->execute();
    //die(print_r($result));
    return $result;
}

I can't seem to say COUNT(b.id) AS count as it gives an error, and I do not know what to use as the addOrderby(???, 'DESC') value?

Extol

Answer 1

what is the error you get when using COUNT(b.id) AS count? it might be because count is a reserved word. try COUNT(b.id) AS idCount, or similar.

alternatively, try $qb->addOrderby('COUNT(b.id)', 'DESC');.

what is your database system (mysql, postgresql, ...)?

Remark

I don't think the database matters with DBAL? Anyway, got it working with: $qb->select('b.year, COUNT(b.id) AS mycount')->from('\My\Entity\Album', 'b')->where('b.year IS NOT NULL')->orderBy('mycount', 'DESC')->groupBy('b.year'); Thanks for pointing out the keyword issue. – Extol

Your alternative method doesn't work. Also, if you want to reuse your results as a subquery - (eg. orderBy) adding COUNT to select doesn't work. – Sulemasulf

Answer 2

There are many bugs and workarounds required to achieve order by expressions as of v2.3.0 or below:

1. The order by clause does not support expressions, but you can add a field with the expression to the select and order by it. So it's worth repeating that Tjorriemorrie's own solution actually works:

   $qb->select('b.year, COUNT(b.id) AS mycount')
      ->from('\My\Entity\Album', 'b')
      ->where('b.year IS NOT NULL')
      ->orderBy('mycount', 'DESC')
      ->groupBy('b.year');
   

2. Doctrine chokes on equality (e.g. =, LIKE, IS NULL) in the select expression. For those cases the only solution I have found is to use a subselect or self-join:

   $qb->select('b, (SELECT count(t.id) FROM \My\Entity\Album AS t '.
                   'WHERE t.id=b.id AND b.title LIKE :search) AS isTitleMatch')
      ->from('\My\Entity\Album', 'b')
      ->where('b.title LIKE :search')
      ->andWhere('b.description LIKE :search')
      ->orderBy('isTitleMatch', 'DESC');
   

3. To suppress the additional field from the result, you can declare it AS HIDDEN. This way you can use it in the order by without having it in the result.

   $qb->select('b.year, COUNT(b.id) AS HIDDEN mycount')
      ->from('\My\Entity\Album', 'b')
      ->where('b.year IS NOT NULL')
      ->orderBy('mycount', 'DESC')
      ->groupBy('b.year');
   

Tomahawk

+1 for HIDDEN. Fantastic tip. Doctrine kept messing my object mapping in the returned result. – Tupi

Like the others said, AS HIDDEN was very helpful to me today – Selfinduction

Unfortunately HIDDEN will not work when used inside an IN subquery. Please refer to my example: gist.github.com/webdevilopers/aa6a0ea06d7b5d4f0b04 – Ammonify

Answer 3

If you want your Repository method to return an Entity you cannot use ->select(), but you can use ->addSelect() with a hidden select.

$qb = $this->createQueryBuilder('q') ->addSelect('COUNT(q.id) AS HIDDEN counter') ->orderBy('counter'); $result = $qb->getQuery()->getResult();

$result will be an entity class object.

Bethsaida

The HIDDEN statement is very useful to request entity of a single class only :+1: – Boutte

In addition if You are counting from related entity You have to do join and group by: $qb = $this->createQueryBuilder('item')->join('item.features', 'feature')->addSelect('COUNT(feature.id) AS HIDDEN counter')->orderBy('counter', 'DESC'); – Steinberg

Answer 4

Please try this code for ci 2 + doctrine 2

$where = " ";

$order_by = " ";
$row = $this->doctrine->em->createQuery("select a from company_group\models\Post a " 
            .$where." ".$order_by."")
            ->setMaxResults($data['limit'])
            ->setFirstResult($data['offset'])
            ->getResult();`

Weasel