Extract count of each status by month

Question

I have the following database:

Reservations
| id | status    | created_at          |
|  1 | Canceled  | 2019-12-16 11:46:11 |
|  2 | Fulfilled | 2019-11-15 23:03:24 |
|  3 | Accepted  | 2019-12-13 12:04:13 |

So I want to extract a collection by months in which there's the count of each status. I.e.:

['Month', 'Accepted', 'Fulfilled', 'Canceled'],
['Jul 2019', 4, 5, 9],
['Aug 2019', 5, 6, 7]

I have several ways of approaching that but all require additional work outside eloquent and don't look very elegant.

Answer 1

Try using the Collection::countBy() method. (https://laravel.com/docs/master/collections#method-countBy)

Reservation::countBy('status');

You can always constrain your query before calling the countBy() function