I have this table:
+----+--------+------------+-----------+------------------+
| id | amount | period | player_id | payment_category |
+----+--------+------------+-----------+------------------+
| 3 | 100 | 2017-06-01 | 1 | 1 |
| 10 | 100 | 2017-06-01 | 2 | 1 |
| 6 | 100 | 2017-08-01 | 2 | 1 |
| 5 | 100 | 2017-10-01 | 3 | 1 |
| 2 | 100 | 2017-11-01 | 2 | 1 |
| 4 | 100 | 2017-11-01 | 1 | 1 |
| 1 | 100 | 2017-12-01 | 1 | 1 |
| 7 | 100 | 2017-12-01 | 1 | 1 |
| 9 | 100 | 2017-12-01 | 2 | 1 |
| 11 | 200 | 2017-12-01 | 1 | 3 |
+----+--------+------------+-----------+------------------+
Current result:
2017-06-01 2
2017-08-01 1
2017-10-01 1
2017-11-01 2
2017-12-01 3
Desired result:
2017-06-01 2
2017-07-01 0
2017-08-01 1
2017-09-01 0
2017-10-01 1
2017-11-01 2
2017-12-01 3
How can I do that? I tried with coalesce and ifnull. No luck.
I started with DQL:
$query = $em->createQuery('
SELECT COUNT(p.id) as total, p.period
FROM AppBundle:Payment p
WHERE p.period >= :fromTime
AND p.period <= :toTime
AND p.paymentCategory = 1
GROUP BY p.period
')
Of course this does not return the results for missing months ;) I'm looking for a method (doctrine or native mysql) which will somehow fill in the missing months. They can't be hardcoded - I need the results from last x months. Is it even possible or I should do it in PHP?
