CODEWITHSUNDEEP
sqlsymfonydoctrine-orm
ncrocfer
ncrocfer

Reputation: 2570

Doctrine : merge 4 counts in 1

I would like to make a count for 4 entities, for example Dog, Cat, Fish and Turtle.

In a same view, I want to display the number of dogs, the number of cats etc. For the moment, each entity has a count() method in its repository, but this solution generates 4 queries.

Do you have a solution to make a single query ? For example something like this (which doesn't work) :

return $this->getEntityManager()->createQuery(
    SELECT COUNT(d) AS nbDogs,
           COUNT(c) AS nbCats,
           COUNT(f) AS nbFishes,
           COUNT(t) as nbTurtles
    FROM AcmeBundle:Dog d,
         AcmeBundle:Cat c,
         AcmeBundle:Fish f,
         AcmeBundle:Turtle t)
    ->getResult();

Upvotes: 0

Views: 70

Answers (2)

nixoschu
nixoschu

Reputation: 544

Actually i dont think it matters if you do one select from 4 different tables or create 4 queries for each table.

you could create a service gathering the different information and use this inside of your twig template by wrapping a custom twig extension around your service for example

Anyway ... if you really want to do this in one query i think you need to execute a custom sql query because with doctrine dql you always need to reference one entity to "start" with, so doctrine can hydrate this object

little example with native sql and doctrine 

$query  = 'SELECT * FROM MYTABLE';
$stmt   = $this->em->getConnection()->prepare($query);
$stmt->execute();

return $stmt->fetchAll();

Upvotes: 1

sroes
sroes

Reputation: 15053

I think the following should work:

return $this->getEntityManager()->createQuery('
    SELECT COUNT(DISTINCT d) AS nbDogs,
           COUNT(DISTINCT c) AS nbCats,
           COUNT(DISTINCT f) AS nbFishes,
           COUNT(DISTINCT t) as nbTurtles
    FROM AcmeBundle:Dog d,
         AcmeBundle:Cat c,
         AcmeBundle:Fish f,
         AcmeBundle:Turtle t
    ')
    ->getResult();

Upvotes: 2

Related Questions