CODEWITHSUNDEEP
phpsymfonydoctrine-ormdql
user4785882
user4785882

Reputation: 91

Doctrine2 how to create aggregate subquery

I have next table

id int not null auto icrement
email_name varchar
event_type varchar

email type - possible values(sent, open, click, unsubscribed) I need to create query like this in doctrine2

SELECT COUNT(ee.id) as total, (SELECT COUNT(e.id) from email_statistic as e where e.event_type = 'open') as opened FROM email_statistic as ee

I want to select total amount, and how many opened emails for statistics How can I do this wia doctrine?

   $qb = $this->createQueryBuilder('ee')
            ->select('count(ee) As total');

$qb2 = $this->createQueryBuilder('e')
            ->select('count(e) As opened');

        $qb2
            ->andWhere('es.eventType = :eventType')
            ->setParameter('eventType', 'open');

$qb->addSelect($qb2) --- this does not allowed by doctrine

Should I use NATIVE QUERY? Can I do this with dql?

Upvotes: 1

Views: 608

Answers (1)

Ivan Kalita
Ivan Kalita

Reputation: 2287

First, you should create an entity, for example EmailStatistic:

/**
 * @ORM\Entity()
 * @ORM\Table()
 */
class EmailStatistic
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id()
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

    /**
     * @var string
     *
     * @ORM\Column(name="email_type", type="string", length=255)
     */
    protected $emailType;

    /**
     * @var string
     *
     * @ORM\Column(name="email_name", type="string", length=255)
     */
    protected $emailName;
}

Then you can use GeneralCaseExpression (http://www.doctrine-project.org/api/orm/2.4/class-Doctrine.ORM.Query.AST.GeneralCaseExpression.html).

$em = $this->get('doctrine')->getEntityManager();
$qb = $em->createQueryBuilder();
$result = $qb
    ->select('SUM(CASE WHEN (e.emailType = \'open\') THEN 1 ELSE 0 END) as opened')
    ->addSelect('SUM(e.id) as total')
    ->from('AppBundle:EmailStatistic', 'e')
    ->getQuery()
    ->getArrayResult();
// $result[0]['opened'] contains number of opened emails
// $result[0]['total'] contains total number of emails

Upvotes: 1

Related Questions