Return foreign key using a DQL query

Question

I have an InvoiceItem object similar to this:

/** * @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; [..] } 

I would like to return an array where the key is the invoice identifier and the value is the sum of qty * unitPrice.

I can return the amount using a DQL query, for example:

 SELECT SUM(I.qty * I.unitPrice) AS amount FROM Entities\\InvoiceItem I WHERE I.invoice IN (..) GROUP BY I.invoice 

Result:

 array( 0 => array('amount' => '46.7'), 1 => array('amount' => '32.5') ) 

But I do not know how to return the invoice foreign key. I tried

 SELECT SUM(I.qty * I.unitPrice) AS amount, I.invoice FROM Entities\\InvoiceItem I WHERE I.invoice IN (..) GROUP BY I.invoice 

But this does not work (error: Invalid PathExpression. Must have StateFieldPathExpression.)

How can I return the account ID? And I would like to use ID as the key of my result array:

 array( 1005 => '46.7', 1250 => '32.5' ) 

where 1005 and 1250 are invoice identifiers.

Update 2011-06-15

Native query works:

  $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(); 

result:

 array( 0 => array( 'invoiceId' => '1005', 'amount' => '46.7' ) 1 => array( 'invoiceId' => '1250', 'amount' => '32.5' ) ) 

But I need to do a loop for indexing using the account id.

Answer 1

You can use the setHint () method to return a foreign key with the remaining values ​​for your object. You apply the method to the query (and not to the query builder):

 $q = $qb->getQuery(); $q->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, true);