CODEWITHSUNDEEP
phpsymfonyormdoctrine-orm
gstprz
gstprz

Reputation: 1

How to use subquery selects

I'm having some trouble making a query. First I'll give a fast explanation of what I need to do with these tables:

users (id, username) and calls (id, user_id[fk], result)

The result field refers to what happened with that call (error, timeout, busy, talk...).

I want to make a bar chart for each user, showing total calls, error calls, timeout calls, etc.

Here is my code:

$em = $this->get('doctrine')->getManager();

$calls = $em->getRepository('CRMBundle:Call\Call')
            ->createQueryBuilder('e')
            ->addSelect('o.username as user')
            ->addSelect('COUNT(DISTINCT e.id) as total_calls')
            ->addSelect('SUBQUERY for error_calls')
            ->addSelect('SUBQUERY for timeout_calls')
            ....
            ->join('e.user', 'o');

            $calls = $calls->groupBy('o.username')
                           ->orderBy('e.id', 'ASC')
                           ->getQuery()
                           ->getResult();

The problem I'm stuck with is writing that subquery syntax. I'm trying with ->addSelect('(SELECT COUNT e.id AS error_calls WHERE e.result = "ERROR")') but it's not working.

Hope you have some advice for me here, thanks in advance!

Upvotes: 0

Views: 46

Answers (2)

Peter Bailey
Peter Bailey

Reputation: 105878

Your sub-queries need to join w/the outer query. Let's think about that in normal SQL terms firs

SELECT count(c.id) as total_calls
     , (SELECT count(*)
          FROM calls
         WHERE user_id = u.id -- Here's the join w/the outer query
           AND result = 'ERROR'
       ) as error_calls
     , (SELECT count(*)
          FROM calls
         WHERE user_id = u.id
           AND result = 'TIMEOUT'
       ) as timeout_calls
  FROM calls c
  LEFT JOIN user u
    ON u.id = c.user_id
 GROUP BY u.id
;

So in DQL terms, a single subquery would look like this

SELECT count(errors)
  FROM CRMBundle:Call\Call error
 WHERE error.user = o // Again, here is the join w/the outer query
   AND error.result = 'whatever'

Back to your query-builder context, we have this

->addSelect('(SELECT count(errors) FROM CRMBundle:Call\Call error WHERE error.user = o AND error.result = :error_result) AS error_calls')
->setParameter('error_result', 'ERROR')

Upvotes: 1

costa
costa

Reputation: 1087

Can you just add on the query the other groupBy types?

       ->groupBy('o.username')
       ->addGroupBy('e.result')

Upvotes: 0

Related Questions