php - Merge loop of SQL queries into one SQL query -

i built loop in php makes 500 sql queries merge 500 sql queries 1 , same return (the companies , count of users on each company)

example of php code

$companies =  array(); foreach ($fortune500service->listall() $c ){   $count = $entitymanager   ->createquerybuilder()   ->select("count(u)")   ->from("appbundle\entity\user","u")   ->where("u.email :d")   ->setparameter("d", "%@" . $c["domain"])   ->getquery()->getsinglescalarresult();    if ($count == 0) {     continue;   }    $companies[] = array(       "domain" => $c["domain"],       "company" => "{$c["company"]} ({$count})",   ); } return $companies; 

example of 2 sql queries want merge

query 1

select    count(u0_.id)     user u0_     u0_.email '%@company1.com'  

query 2

select    count(u0_.id)    user u0_     u0_.email '%@company2.com'  

i prefer solution using createquerybuilder http://symfony.com/doc/current/book/doctrine.html#querying-for-objects-using-doctrine-s-query-builder happy sql native query.

use conditional aggregation:

select count(case when u0_.email '%@company1.com' 1 end) first_cnt,         count(case when u0_.email '%@company2.com' 1 end) first_cnt user u0_