主义2选择count groupBy [英] Doctrine 2 select count groupBy

问题描述

  $ qb-> select（'COUNT （）as cnt'）
  - > from（$ type，'c'）
  - > groupBy（'c.organization，c.process_role，c.domain，c.year'）
  - > getQuery（） - > getSingleScalarResult（）
  

但是它返回一个组数。我应该如何写这个正确的？

最后，这有用，但它有点丑陋

 code> $ 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（）; 
  

解决方案

尝试分解单个 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（）; 
  

然而，这实际上返回相同的东西，因为它只会将结果分组为4变量。您应该使用 DISTINCT 选择方法，并计算结果行。

  $ rows = $ this-> getDoctrine（） - > getManager（）
  - > createQuery 
'SELECT DISTINCT c.organization，c.process_role，c.domain，c.year FROM'。$ type。'c'
）
  - > getArrayResult（）; 
 $ count = count（$ rows）; 
  

这是唯一可以使用的已知方法，因为DQL将不支持任何替代策略例如 SELECT COUNT（*）FROM（SELECT DISTINCT ...））

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 ... ))

这篇关于主义2选择count groupBy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！