我正在使用Doctrine的QueryBuilder构建一个查询，并且我想从查询中获得结果的总数。
$repository = $em->getRepository('FooBundle:Foo');
$qb = $repository->createQueryBuilder('n')
->where('n.bar = :bar')
->setParameter('bar', $bar);
$query = $qb->getQuery();
//this doesn't work
$totalrows = $query->getResult()->count();
我只想在这个查询上运行一个计数，以获得总的行数，但不返回实际结果。(在这个计数查询之后，我将使用maxResults进一步修改查询以进行分页。)
当前回答
使用分组，联合和其他东西的例子。
问题:
$qb = $em->createQueryBuilder()
->select('m.id', 'rm.id')
->from('Model', 'm')
->join('m.relatedModels', 'rm')
->groupBy('m.id');
为了实现这一点，可能的解决方案是使用定制水合器和这个奇怪的东西 称为'自定义输出步行者提示':
class CountHydrator extends AbstractHydrator
{
const NAME = 'count_hydrator';
const FIELD = 'count';
/**
* {@inheritDoc}
*/
protected function hydrateAllData()
{
return (int)$this->_stmt->fetchColumn(0);
}
}
class CountSqlWalker extends SqlWalker
{
/**
* {@inheritDoc}
*/
public function walkSelectStatement(AST\SelectStatement $AST)
{
return sprintf("SELECT COUNT(*) AS %s FROM (%s) AS t", CountHydrator::FIELD, parent::walkSelectStatement($AST));
}
}
$doctrineConfig->addCustomHydrationMode(CountHydrator::NAME, CountHydrator::class);
// $qb from example above
$countQuery = clone $qb->getQuery();
// Doctrine bug ? Doesn't make a deep copy... (as of "doctrine/orm": "2.4.6")
$countQuery->setParameters($this->getQuery()->getParameters());
// set custom 'hint' stuff
$countQuery->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, CountSqlWalker::class);
$count = $countQuery->getResult(CountHydrator::NAME);
其他回答
使用分组，联合和其他东西的例子。
问题:
$qb = $em->createQueryBuilder()
->select('m.id', 'rm.id')
->from('Model', 'm')
->join('m.relatedModels', 'rm')
->groupBy('m.id');
为了实现这一点，可能的解决方案是使用定制水合器和这个奇怪的东西 称为'自定义输出步行者提示':
class CountHydrator extends AbstractHydrator
{
const NAME = 'count_hydrator';
const FIELD = 'count';
/**
* {@inheritDoc}
*/
protected function hydrateAllData()
{
return (int)$this->_stmt->fetchColumn(0);
}
}
class CountSqlWalker extends SqlWalker
{
/**
* {@inheritDoc}
*/
public function walkSelectStatement(AST\SelectStatement $AST)
{
return sprintf("SELECT COUNT(*) AS %s FROM (%s) AS t", CountHydrator::FIELD, parent::walkSelectStatement($AST));
}
}
$doctrineConfig->addCustomHydrationMode(CountHydrator::NAME, CountHydrator::class);
// $qb from example above
$countQuery = clone $qb->getQuery();
// Doctrine bug ? Doesn't make a deep copy... (as of "doctrine/orm": "2.4.6")
$countQuery->setParameters($this->getQuery()->getParameters());
// set custom 'hint' stuff
$countQuery->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, CountSqlWalker::class);
$count = $countQuery->getResult(CountHydrator::NAME);
对于只使用Doctrine DBAL而不使用Doctrine ORM的人来说，他们将无法访问getQuery()方法，因为它不存在。他们需要做以下事情。
$qb = new QueryBuilder($conn);
$count = $qb->select("count(id)")->from($tableName)->execute()->fetchColumn(0);
最好将所有使用数据库的逻辑都转移到存储库中。
在控制器中
/* you can also inject "FooRepository $repository" using autowire */
$repository = $this->getDoctrine()->getRepository(Foo::class);
$count = $repository->count();
在Repository/FooRepository.php中
public function count()
{
$qb = $repository->createQueryBuilder('t');
return $qb
->select('count(t.id)')
->getQuery()
->getSingleScalarResult();
}
最好移动$qb =…来分隔行，以防你想写出复杂的表达式，比如
public function count()
{
$qb = $repository->createQueryBuilder('t');
return $qb
->select('count(t.id)')
->where($qb->expr()->isNotNull('t.fieldName'))
->andWhere($qb->expr()->orX(
$qb->expr()->in('t.fieldName2', 0),
$qb->expr()->isNull('t.fieldName2')
))
->getQuery()
->getSingleScalarResult();
}
还要考虑缓存查询结果—http://symfony.com/doc/current/reference/configuration/doctrine.html#caching-drivers
public function count()
{
$qb = $repository->createQueryBuilder('t');
return $qb
->select('count(t.id)')
->getQuery()
->useQueryCache(true)
->useResultCache(true, 3600)
->getSingleScalarResult();
}
在一些简单的情况下，使用EXTRA_LAZY实体关系是很好的 http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/tutorials/extra-lazy-associations.html
将以下方法添加到存储库中应该允许您从控制器调用$repo->getCourseCount()。
/**
* @return array
*/
public function getCourseCount()
{
$qb = $this->getEntityManager()->createQueryBuilder();
$qb
->select('count(course.id)')
->from('CRMPicco\Component\Course\Model\Course', 'course')
;
$query = $qb->getQuery();
return $query->getSingleScalarResult();
}
如果你需要计数一个更复杂的查询，使用groupBy，有etc…你可以借用Doctrine\ORM\Tools\Pagination\Paginator:
$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
$totalRows = count($paginator);
