我有一个像这样的 InvoiceItem 实体:
/**
* @Entity
*/
class InvoiceItem
{
[..]
/**
* @ManyToOne(targetEntity="Invoice", inversedBy="items")
* @JoinColumn(name="invoice_id", referencedColumnName="id")
* @var Invoice
*/
private $invoice;
/**
* @Column(type="decimal", scale="10", precision="3")
* @var float
*/
private $qty;
/**
* @Column(name="unit_price", type="decimal", scale="10", precision="3")
* @var float
*/
private $unitPrice;
[..]
}
我想返回一个数组,其中键是发票的 id,值是 qty * unitPrice 的总和。
我可以使用如下 DQL 查询返回总和:
SELECT SUM(I.qty * I.unitPrice) AS amount FROM Entities\\InvoiceItem I
WHERE I.invoice IN (..) GROUP BY I.invoice
结果 :
array(
0 => array('amount' => '46.7'),
1 => array('amount' => '32.5')
)
但我不知道如何返回发票外键。我试过了
SELECT SUM(I.qty * I.unitPrice) AS amount, I.invoice
FROM Entities\\InvoiceItem I
WHERE I.invoice IN (..) GROUP BY I.invoice
但它不起作用(错误:无效的 PathExpression。必须是 StateFieldPathExpression。)
如何返回发票 ID?我想使用 ID 作为结果数组的键:
array(
1005 => '46.7',
1250 => '32.5'
)
其中 1005 和 1250 是发票的 ID。
更新2011-06-15
原生查询的工作原理:
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('invoice_id', 'invoiceId');
$rsm->addScalarResult('amount', 'amount');
$q = $this->getEntityManager()->createNativeQuery(
'SELECT invoice_id, SUM(qty * unit_price) AS amount FROM invoices_items'
.' WHERE invoice_id IN ('.implode(',', $ids).') GROUP BY invoice_id',
$rsm
);
$result = $q->getResult();
结果:
array(
0 =>
array(
'invoiceId' => '1005',
'amount' => '46.7'
)
1 =>
array(
'invoiceId' => '1250',
'amount' => '32.5'
)
)
但我需要创建一个循环来按发票 ID 进行索引。