Symfony2，Doctrine2查询 - 选择IF和COUNT [英] Symfony2, Doctrine2 query - select with IF and COUNT

问题描述

我有一个页面上的用户活动表（Fields：id，user_id，page_id，day，votes，comments）

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."'"

我想将其转换为我的Doctrine2查询

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?

推荐答案

对于复杂的查询，您可以使用nativeQuery与ResultSetMapping。

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();

这篇关于Symfony2，Doctrine2查询 - 选择IF和COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！