用DQL查询返回外键 [英] Return foreign key with DQL query

问题描述

我有一个这样的InvoiceItem实体：

  / ** 
 * @Entity 
 * / 
 class InvoiceItem 
 {
 [...] 
 
 / ** 
 * @ManyToOne（targetEntity =Invoice，inversedBy =项目）
 * @JoinColumn（name =invoice_id，referencedColumnName =id）
 * @var发票
 * / 
 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，并且值是数量*的总和。单位价格

我可以用DQL查询返回总和，如下所示：

  SELECT SUM（I.qty * I.unitPrice）AS金额FROM实体\\InvoiceItem I 
 WHERE I.invoice IN（..）GROUP BY I.invoice 
  

结果：

  array（
 0 => array（'amount'=> '46 .7'），
 1 =>数组（'amount'=> '32 .5'）
）
  

知道如何退回发票外键。我试过

  SELECT SUM（I.qty * I.unitPrice）AS金额，I.invoice 
 FROM实体\\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（'，'，$ id）。'）GROUP BY invoice_id'，
 $ rsm 
）; 
 $ result = $ q-> getResult（）; 
  

结果：

  array（
 0 => 
数组（
'invoiceId'=>'1005'，
'amount'=> '46 .7'
）
 1 => 
数组（
'invoiceId'=>'1250'，
'amount'=> '32 .5'
） 
）
  

但是，我需要通过发票ID进行循环索引。 p>

解决方案

您可以使用setHint（）方法使外键与您的实体的其余值一起返回。您将该方法应用于查询（而不是查询构建器）：

  $ q = $ qb-> getQuery（）; 
 $ q-> setHint（\Doctrine\ORM\Query :: HINT_INCLUDE_META_COLUMNS，true）; 
  

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.

解决方案

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

这篇关于用DQL查询返回外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！