According to our club's membership database, some members are in arrears.
In the simple example below, in the first year, Marc and Paul paid their membership dues, while Denis credited his account.
In the second year, Marc and Paul have not paid their dues, while Denis is still in credit.
In the third year, Marc accrues his debt, Paul regularizes his account and Denis is up to date.
| id | firstname | member_id | object | debit | credit |
-------------------------------------------------------
| 1 | Marc | 1 | dues | NULL | 20 |
| 2 | Paul | 2 | dues | NULL | 20 |
| 3 | Denis | 3 | dues | NULL | 60 |
| 36 | Marc | 1 | dues | 20 | 0 |
| 38 | Paul | 2 | dues | 20 | 0 |
| 39 | Denis | 3 | dues | 0 | 40 |
| 63 | Marc | 1 | dues | 40 | 0 |
| 64 | Paul | 2 | dues | 40 | 0 |
| 65 | Paul | 2 | dues | 0 | 40 |
| 66 | Denis | 3 | dues | 0 | 20 |
So, to find out how many members are in arrears, I have to count those whose total debits exceed their total credits.
Something like this:
->select( COUNT(member_id) )
->where ( SUM(debits) > SUM(credits) )
->getQuery()
->getSingleScalarResult();
But of course, I can't use aggregate functions in a WHERE clause.
I thought of using HAVING and GROUP BY clauses, but since I need to count and get a single result, not an array, that doesn't work.
Any ideas how to do using DQL?
After spending some time searching Google and various forums, I couldn't find a correct and precise answer to my question, so I asked it here.
Then, after studying the only useful answer from @Shadow, and making several attempts, I finally managed to write my query using DQL. And it works!
$members_in_arrears = count(
$accountRepository->createQueryBuilder('a')
->select('distinct(a.member_id) as ids','SUM(a.debit) as sum_debit', 'SUM(a.credit) as sum_credit')
->having('sum_debit > sum_credit')
->groupby ('ids')
->getQuery()
->getResult();
);
Hope this will help