Symfony2, Doctrine2 query - select with IF and COU

Question

I have a table for activity of user on page (Fields: id, user_id, page_id, day, votes, comments)

"SELECT COUNT(DISTINCT m.user_id) as cnt, 
COUNT( DISTINCT IF( m.votes > 0, m.user_id, NULL)) as vote , 
COUNT( DISTINCT IF( m.comments > 0, m.user_id, NULL)) as comment, 
COUNT( DISTINCT IF( m.votes + m.comments > 0, m.user_id, NULL)) as votecomment
FROM mytable m
WHERE m.page_id = ".$user_id."
AND m.day > '".$day."'"

I want to convert it to my Doctrine2 Query

//in EntityRepository class:
$selectArr = array(
    'cnt' => "COUNT(DISTINCT m.user_id) as cnt",
    'vote' => "COUNT(DISTINCT CASE WHEN (m.votes > 0) THEN m.user_id ELSE NULL END) as votes",
    'comment' => "COUNT(DISTINCT CASE WHEN (m.comments > 0) THEN m.user_id ELSE NULL END) as comment",
    'votecomment' => "COUNT(DISTINCT CASE WHEN (m.votes + m.comments > 0) THEN m.user_id ELSE NULL END) as votecomment",
);

$query = $this->createQueryBuilder('m')
->select($selectArr)
->where('m.page_id = :id')
->andWhere('m.day > :from')
->setParameter('id', $user_id)
->setParameter('from', $day)
->getQuery();
try {
    return $query->getSingleResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);
} catch (\Doctrine\ORM\NoResultException $e) {
    return null;
}

But it don't work. Why? How to fix it?

Answer 1

For complex queries you can use nativeQuery with ResultSetMapping.

    $rsm = new ResultSetMapping();
    $rsm->addScalarResult('cnt', 'cnt', 'integer');
    $rsm->addScalarResult('vote', 'vote', 'integer');
    $rsm->addScalarResult('comment', 'comment', 'integer');
    $rsm->addScalarResult('votecomment', 'votecomment', 'integer');

    $query = $this->getEntityManager()->createNativeQuery("SELECT COUNT(DISTINCT m.user_id) as cnt, 
        COUNT( DISTINCT IF( m.votes > 0, m.user_id, NULL)) as vote , 
        COUNT( DISTINCT IF( m.comments > 0, m.user_id, NULL)) as comment, 
        COUNT( DISTINCT IF( m.votes + m.comments > 0, m.user_id, NULL)) as votecomment
        FROM mytable m
        WHERE m.page_id = :uid
        AND m.day > :day", $rsm);

    $query->setParameters([
        'uid' => $user_id,
        'day' => $day,
    ]);

    $rows = $query->getResult();