如何在Symfony中使用sql aggregat SUM

Question

从表Invoice (id, invoice_id, product, price).

我正尝试在Symfony中使用doctrine做类似的事情:

SELECT SUM(price) AS price_total
FROM Invoice
WHERE invoice_id = 1

以下是我在InvoiceRepository.php中的请求:

$qb = $this->_em->createQueryBuilder("i");
$qb->select('SUM(i.price)')
->from('AppBundle:Invoice','i')
->where('i.invoice_id = :invoice')
->setParameter('invoice',$invoice);
return $qb->getQuery()->getSingleScalarResult();

但是这个请求总是返回第一个值，而不是总和。

有人知道怎么了吗?

Answer 1

要使用SUM, AVG, COUNT等聚合函数，您需要指定groupby条件。这样的:

$qb = $this->_em->createQueryBuilder("i");
$qb->select('SUM(i.price)')
->from('AppBundle:Invoice','i')
->where('i.invoice_id = :invoice')
->groupBy('i.invoice_id') //<- here
->setParameter('invoice',$invoice);
return $qb->getQuery()->getSingleScalarResult();