Doctrine2 Join SUM

Question

I'm trying to figure out what is the best way to do this in Doctrine 2.

I have Account entity which have OneToMany to Transaction entity. I need to SUM all values from Transaction filtered by account. Is like this in SQL: SELECT a.*, SUM(t.amount) AS balance FROM account a INNER JOIN transaction t ON t.account_id = a.id

Method #1:

Directly on Entity

class Account {
    // some other definitions

    /**
     * @OneToMany(targetEntity="Transaction", mappedBy="account")
     */
    private $transactions;

    public function getBalance() {
        $balance = 0;
        foreach ($this->transactions as $transaction){
            $balance += $transaction->getAmount();
        }
        return $balance;
    }
}

I think this is the worst way, since it fetches all related transactions to get their amount.

Method #2:

Using Repositories

class TransactionRepository {
    public function getBalanceByAccount(Account $account){
        $query = $this->em->createQuery("SELECT SUM(t.amount) FROM Transaction t INNER JOIN t.Account a WHERE a.id = ?");
        // ...
        return $query->getSingleScalarResult();
    }
}

I'm not pretty sure if it goes on TransactionRepository or AccountRepository.

Method #3:

Using Service pattern

class TransactionService {
    public function getBalanceByAccountId($accountId){
        $query = $this->em->createQuery("SELECT SUM(t.amount) FROM Transaction t INNER JOIN t.Account a WHERE a.id = ?");
        // ...
        return $query->getSingleScalarResult();
    }
}

Again, I'm not sure if it goes on TransactionService or AccountService.

Method #4: (by Guilherme Blanco)

Table denormalization, saving balance into account table/Entity.

Method #n:

Please let me know your suggestion.

Answer 1

Remember that not all the times you can have a fully normalized Database. This is a situation where I'd recommend a denormalization, having a balance in your Account entity.

Then, whenever you are saving, just remember that all new Transactions should be sum'ed to the balance and update the balance value at the end.

By doing this, you never need to generate a SUM(), which is way better for your application.