Doctrine2 Join SUM

Question

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

I have an Account object that has OneToMany for a transaction object. I need to SUMME all values ​​from a transaction filtered by account. It's like 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 gets all the 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 sure if it will work in a TransactionRepository or AccountRepository.

Method 3:

Using a service template

 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 this is happening in a TransactionService or AccountService.

Method # 4: (by Guilherme Blanco )

denormalization of tables, maintaining balance in the accounting / entity table.

Method #n:

Please let me know your suggestion.

Answer 1

Remember that not all the time you can have a fully normalized database. This is a situation where I recommend denormalization having a balance in your account.

Then, when you save, just remember that all new transactions should be added to the balance and update the balance value at the end.

This way, you never need to create SUM (), which is better for your application.