Merge loop of SQL queries into one SQL query

Question

I built a loop in PHP that makes 500 SQL queries but I would like to merge the 500 SQL queries into one and get the same return (the companies and the count of the users on each company)

Example of PHP code

$companies =  array();
foreach ($fortune500Service->listAll() as $c ){
  $count = $entityManager
  ->createQueryBuilder()
  ->select("count(u)")
  ->from("AppBundle\Entity\User","u")
  ->where("u.email LIKE :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 that I want to merge

Query 1

SELECT 
  count(u0_.id) 
FROM 
  user u0_ 
WHERE 
  u0_.email LIKE '%@company1.com'

Query 2

SELECT 
  count(u0_.id)
FROM 
  user u0_ 
WHERE 
  u0_.email LIKE '%@company2.com'

I prefer a solution using createQueryBuilder http://symfony.com/doc/current/book/doctrine.html#querying-for-objects-using-doctrine-s-query-builder but I am happy also with an SQL native query.

Put all the u0_.email LIKE '%@somecompany.com' conditions into an array and join them with OR. — syck
– syck, Commented Apr 6, 2016 at 13:18
I am not sure but If I put all the u0_.email LIKE '%@somecompany.com' conditions into an array and join them with OR. The result that I will get is the number of users in any company. For example 4 users in total. I want this result: 2 Users Company1 and 2 Users Company2. — Adolfo Luzardo Cabrera
– Adolfo Luzardo Cabrera, Commented Apr 6, 2016 at 13:25
Possible dublicate of stackoverflow.com/questions/15230350/… — WebInsight
– WebInsight, Commented Apr 6, 2016 at 13:27

sagi · Accepted Answer · 2016-04-06 13:24:36Z

1

Use conditional aggregation:

SELECT COUNT(CASE WHEN u0_.email LIKE '%@company1.com' THEN 1 END) as First_cnt,
        COUNT(CASE WHEN u0_.email LIKE '%@company2.com' THEN 1 END) as First_cnt
FROM user u0_

answered Apr 6, 2016 at 13:24

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge loop of SQL queries into one SQL query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related