Better way to get the SUM of the field of an associated entity in Doctrine 2?

How can I get the sum of the fields of an associated entity? For example, Invoice and Bill has OneToMany and ManyToOne relationship respectively, that is an Invoice can have many Bills. The Bill entity has an Amount column. How can an Invoice get the SUM of it开发者_JS百科s related Bills' Amount?

You're asking for a "better way", but it's not clear what method you're using now?

Unless you need to optimize, I'd just have a method in your Invoice entity class like:

public function getTotal(){
    $total = 0;
    foreach($this->bills as $b){
        $total += $b->amount;
    } 
    return $total;
}

It's not particularly optimized, but it's nice and clear.

If you decide you need to optimize it, you could:

A) create some service class that knows about the entitymanager, that has a method like getInvoiceTotal(Entity\Invoice $invoice) that performs a DQL query to fetch the SUM() from the database

or

B) Keep a running total as a property of Invoice. Use the lifecycle callbacks to update the Invoice's total when bills are added/removed/updated.

The Doctrine Aggregate Fields page outlines a few possible solutions to this problem. They are similar to what @timdev described but provide more implementation detail and point out the caveats of each.

public function getTotal(){
$total = 0;
foreach($this->bills as $b){
    $total += $b->amount;
} 
return $total;
}

Personally, I don't like the above solution and doctrine aggregate field solution and is suitable/optimized if you are looking for a single record. If you have to aggregate for several records as shown below:

|---invoice_id----|---bill total---| 
|       1         |      100       |
|       2         |      200       |
|       3         |      300       |

this will fetch all the associated bills for the related invoices. So for n number of invoice records, it will fetch all the related invoices for each invoice and thus hurting the optimization.

For such situation I did something like this:

 <?php
class InvoiceDTO
{
    public function __construct($id, $total)
    {
    // Bind values to the object properties.
         $this->invoiceId=$id;
         $this->total= $total;
    }
}
  $query = $em->createQuery('SELECT NEW InvoiceDTO(i.id,SUM(b.amount)) FROM Invoice i JOIN i.bill b  GROUP BY i');
  $users = $query->getResult(); // array of InvoiceDTO

Only one query will be executed with this solution.