StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODoctrine2 Join SUM
text
Body
copied!<p>I'm trying to figure out what is the best way to do this in Doctrine 2.</p> <p>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: <code>SELECT a.*, SUM(t.amount) AS balance FROM account a INNER JOIN transaction t ON t.account_id = a.id</code></p> <h2>Method #1:</h2> <p>Directly on Entity</p> <pre><code>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; } } </code></pre> <p>I think this is the worst way, since it fetches all related transactions to get their amount.</p> <h2>Method #2:</h2> <p>Using Repositories</p> <pre><code>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(); } } </code></pre> <p>I'm not pretty sure if it goes on TransactionRepository or AccountRepository.</p> <h2>Method #3:</h2> <p>Using Service pattern</p> <pre><code>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(); } } </code></pre> <p>Again, I'm not sure if it goes on TransactionService or AccountService.</p> <h2>Method #4: (by <a href="https://stackoverflow.com/a/9894372/430989">Guilherme Blanco</a>)</h2> <p>Table denormalization, saving balance into account table/Entity.</p> <h2>Method #n:</h2> <p>Please let me know your suggestion.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload