Return foreign key with DQL query

Question

I've got an InvoiceItem entity like 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 will be the id of the invoice and the value the sum of qty * unitPrice.

I can return the sum with a DQL query like this :

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

The result :

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

But I don't know how to return the invoice foreign key. I've tried

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

But it doesn't work (error: Invalid PathExpression. Must be a StateFieldPathExpression.)

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

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

where 1005 and 1250 are the id of the invoices.

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

the result:

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

But I need to make a loop to index by the invoice ID.

Answer 1

You can use the setHint() method to make the foreign key get returned with the rest of the values for your entity. You apply the method to the query (not the querybuilder):

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