CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
matthew
matthew

Reputation: 2254

How would I do MySQL count(*) in Doctrine2?

I have the following Doctrine2 query:

$qb = $em->createQueryBuilder()
      ->select('t.tag_text, COUNT(*) as num_tags')
      ->from('CompanyWebsiteBundle:Tag2Post', 't2p')
      ->innerJoin('t2p.tags', 't')
      ->groupBy('t.tag_text')
;
$tags = $qb->getQuery()->getResult();

When run I get the following error: 

[Semantical Error] line 0, col 21 near '*) as num_tags': Error: '*' is not defined.

How would I do MySQL count(*) in Doctrine2?

Upvotes: 16

Views: 38380

Answers (3)

SudarP
SudarP

Reputation: 936

As $query->getSingleScalarResult() expects at least one result hence throws a no result exception if there are not result found so use try catch block

try{
   $query->getSingleScalarResult();
}
catch(\Doctrine\ORM\NoResultException $e) {
        /*Your stuffs..*/
}

Upvotes: 7

Boris Guéry
Boris Guéry

Reputation: 47624

You're trying to do it in DQL not "in Doctrine 2".

You need to specify which field (note, I don't use the term column) you want to count, this is because you are using an ORM, and need to think in OOP way.

$qb = $em->createQueryBuilder()
      ->select('t.tag_text, COUNT(t.tag_text) as num_tags')
      ->from('CompanyWebsiteBundle:Tag2Post', 't2p')
      ->innerJoin('t2p.tags', 't')
      ->groupBy('t.tag_text')
;
$tags = $qb->getQuery()->getResult();

However, if you require performance, you may want to use a NativeQuery since your result is a simple scalar not an object.

Upvotes: 16

Asciiom
Asciiom

Reputation: 9975

You should be able to do it just like this (building the query as a string):

$query = $em->createQuery('SELECT COUNT(u.id) FROM Entities\User u');
$count = $query->getSingleScalarResult();

Upvotes: 30

Related Questions