Multiple COUNT query on the same Entity type using Symfony 2.1 and Doctrine 2

Question

For a project I need to give a load of diverse data in JSON format. All the information will be used on the same page so a single call would result in the least overhead. The information all concerns the same database object and is all necessary on the page. It basically is a collection of counts of the amount of objects that are of one or more of a certain type (the types are all booleans) and we require to know a lot of different variations of this. I used the code below but my co-worker believes the way I put it in the JSON list is a bit clunky and the code could has a greater performance. How could I improve this code?

public function getContactsStatisticsAction()
{
    $response = new Response();
    $json = array();
    $em = $this->getDoctrine()->getEntityManager();
    $cr = $em->getRepository('BlaCoreBundle:Company');

    $json['numberOfCompanies'] = $cr->numberOfCompanies();
    $json['numberOfAccounts'] = $cr->numberOfCompanies(array("typeAccount" => true));
    $json['numberOfCompetitors'] = $cr->numberOfCompanies(array("typeCompetitor" => true));
    $json['numberOfSuppliers'] = $cr->numberOfCompanies(array("typeSupplier" => true));
    $json['numberOfOthers'] = $cr->numberOfCompanies(array("typeOther" => true));
    $json['numberOfUnassigned'] = $cr->numberOfCompanies(array("typeAccount" => false, "typeCompetitor" => false,"typeSupplier" => false,"typeOther" => false));

    $json['numberOfJustAccounts'] = $cr->numberOfCompanies(array("typeAccount" => true, "typeCompetitor" => false, "typeSupplier" => false));
    $json['numberOfJustCompetitors'] = $cr->numberOfCompanies(array("typeAccount" => false, "typeCompetitor" => false, "typeSupplier" => false));
    $json['numberOfJustSuppliers'] = $cr->numberOfCompanies(array("typeAccount" => false, "typeCompetitor" => false, "typeSupplier" => false));

    $json['numberOfCompetitorAndAccounts'] = $cr->numberOfCompanies(array("typeAccount" => true, "typeCompetitor" => true, "typeSupplier" => false));
    $json['numberOfCompetitorAndSuppliers'] = $cr->numberOfCompanies(array("typeAccount" => false, "typeCompetitor" => true, "typeSupplier" => true));
    $json['numberOfSupplierAndAccounts'] = $cr->numberOfCompanies(array("typeAccount" => true, "typeCompetitor" => false, "typeSupplier" => true));
    $json['numberOfCompaniesAndAccountsAndSuppliers'] = $cr->numberOfCompanies(array("typeAccount" => true, "typeCompetitor" => true, "typeSupplier" => true));

    $response->setContent(json_encode($json));
    return $response;
}


public function numberOfCompanies($filters = array())
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('count(c.id)');
    $qb->from('BlaCoreBundle:Company', 'c');
    $sizeFilters = count ($filters);
    $keys = array_keys($filters);
    if($sizeFilters >= 1){
        $qb->where('c.' . $keys[0] . ' = ' . (int) $filters[$keys[0]]);
    }
    for($i = 1; $i < $sizeFilters; $i++){
        $qb->andWhere('c.' . $keys[$i] . ' = ' . (int) $filters[$keys[$i]]);
    }
    return $qb->getQuery()->getSingleScalarResult();
}

Answer 1

Your colleagues are right. You should get all that scalar results in a single query. You will minimize the number of connections in this way.

The topic is solved in this answer for a non-Doctrine case.

Such a user also made this interesting question here, but none answered.

Actually I think that there is no way to solve this kind of query with QueryBuilder or with DQL. Also in the official docs for Doctrine2.2 there are no examples of a JOIN on a SELECT.

What you can try is something like the following DQL query:

return $this->getEntityManager()->createQuery(
  SELECT COUNT(c1) AS C1, COUNT(c2) AS C2, COUNT(c3) AS C3 FROM 
    BlaCoreBundle:Company c1, BlaCoreBundle:Company c2, BlaCoreBundle:Company c3
     WHERE c1.prop1 = 'xxx' AND c2.prop2 > '100' AND c3.prop3 LIKE '%XYZ%')               
  ->getResult();

where of course the WHERE clauses are general examples. This query will return a one sized array, with C1, C2 and C3 as the key for the values you counted. Of course, it becomes difficult to use JOIN and whatever you need, but you can always use WHERE IN (SELECT...) and WHERE EXISTS (SELECT...), e.g.

  SELECT COUNT(c1) AS C1, COUNT(c2) AS C2, COUNT(c3) AS C3 FROM 
    BlaCoreBundle:Company c1, BlaCoreBundle:Company c2, BlaCoreBundle:Company c3
     WHERE c1.prop1 = 'xxx' AND c2.prop2 > '100' AND c3.prop3 LIKE '%XYZ%'
 AND EXISTS (SELECT x FROM BlaCoreBundle:Entity x JOIN x.company comp WHERE x.prop = "valye" AND comp = c1)