Addition and Subtraction with Query Builder Doctrine ORM 2

Question

I have a transaction table with a list of transactions (deposits) made by my users.

ID | user_id | credit | debit | status

1 | 1 |5 |1

2 | 1 |12 |1

3 | 1 |8 |1

To figure out the current balance of each user, I need to take the sum of all the credits and subtract the sum of all the debits.

The MySql statement would be something like this:

SELECT SUM(Credit) - SUM(Debit) as Balance FROM Transactions WHERE user_id = 1 ANS status = 1;

How would this be done in QB?

Answer 1

This was relatively simple to solve, here is my final function that returns my users account balance using DQL:

public function getUserBalance($userObject)
{
    return $this->transactionRepository->createQueryBuilder('u')
        ->add('select','SUM(u.credit) - SUM(u.debit)')
        ->where('u.status = :status')
        ->andWhere('u.user = :userObject')
        ->setParameter('status' , 1)
        ->setParameter('userObject' , $userObject)
        ->getQuery()
        ->getSingleScalarResult();

}