MySQL 在 Symfony2 QueryBuilder 中使用计数和分组的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17642665/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query with count and group by in Symfony2 QueryBuilder
提问by 13bonobo
I need your help please. I have this SQL query :
我需要你的帮助。我有这个 SQL 查询：
SELECT * , COUNT( * ) AS count FROM mytable GROUP BY email ORDER BY id DESC LIMIT 0 , 30
But I would like to do this in Symfony2 with Doctrine and the createQueryBuilder(). I try this, but didn't work :
但我想在 Symfony2 中使用 Doctrine 和 createQueryBuilder() 来做这件事。我试过这个，但没有用：
$db = $this->createQueryBuilder('s');
$db->andWhere('COUNT( * ) AS count');
$db->groupBy('s.email');
$db->orderBy('s.id', 'DESC');
Can you help me please ? Thanks :)
你能帮我吗 ？谢谢 ：）
回答by Serhii Topolnytskyi
You need to run 2 queries:
您需要运行 2 个查询：
$db = $this->createQueryBuilder();
$db
->select('s')
->groupBy('s.email')
->orderBy('s.id', 'DESC')
->setMaxResults(30);
$qb->getQuery()->getResult();
and
和
$db = $this->createQueryBuilder();
$db
->select('count(s)')
->groupBy('s.email')
//->orderBy('s.id', 'DESC')
->setMaxResults(1);
$qb->getQuery()->getSingleScalarResult();
回答by wasbaiti
$db = $this->createQueryBuilder('mytable');
$db
->addSelect('COUNT(*) as count')
->groupBy('mytable.email')
->orderBy('mytable.id', 'DESC')
->setFirstResult(0)
->setMaxResults(30);
$result = $db->getQuery()->getResult();
Hope it helps even if it's an old stack
希望它有帮助，即使它是一个旧堆栈
回答by antongorodezkiy
I tried to use
我试着用
$query->select('COUNT(DISTINCT u)');
and it's seem to work fine so far.
到目前为止它似乎工作正常。
