当前位置: 动力学知识库 > 问答 > 编程问答 >

php - Doctrine 2 select count groupBy

问题描述:

I'm trying to retrieve a number of rows with unique uids.

$qb->select('COUNT() as cnt')

->from($type, 'c')

->groupBy('c.organization, c.process_role, c.domain, c.year')

->getQuery()->getSingleScalarResult()

But it returns an array of group counts. How should I write this correct?

Finally, that works, but it's kinda ugly

$count = $this->_em->createQuery( 'SELECT COUNT(c.id) FROM '.$type.' as c WHERE c.id IN ('

. 'SELECT c1.id FROM ' . $type . ' c1 '

. 'GROUP BY c1.organization, c1.process_role, c1.domain, c1.year)')

->getSingleScalarResult();

网友答案:

Try breaking up your single groupBy into addGroupBy functions:

$qb->select('COUNT(c) as cnt')
            ->from($type, 'c')
            ->groupBy('c.organization')
            ->addGroupBy('c.process_role')
            ->addGroupBy('c.domain')
            ->addGroupBy('c.year')
            ->getQuery()->getSingleScalarResult();

However this does in fact return the same thing since it will just group results into unique sets by those 4 variables. You should instead use a DISTINCT selection method and count the resulting rows.

$rows = $this->getDoctrine()->getManager()
    ->createQuery(
        'SELECT DISTINCT c.organization, c.process_role, c.domain, c.year FROM ' . $type . ' c'
    )
    ->getArrayResult();
$count = count($rows);

This is the only known method to me that will work since DQL won't support any alternative strategies (such as SELECT COUNT(*) FROM (SELECT DISTINCT ... ))

网友答案:

Insted of $qb->select(); use $qb->distinct();. I hope it works.

分享给朋友:
您可能感兴趣的文章:
随机阅读: