How to select all months of different years one time in Laravel?

Question

Laravel

For the archive list on the blog, you need to select all the months, one at a time, that have at least one entry. I store the date in the database in the published_at field of the timestamp() type. The
query retrieves completely different dates (date and time of publication) one time, but I need different months. I think that you can see the solution to the problem in one request ... but somehow I can’t guess. Thanks in advance for help.
Post::select('published_at')->distinct()->get();

Answer 1

Maybe groupBy is for you?

Post::select(DB::raw("MONTH(published_at) as month, YEAR(published_at) as year"))->groupBy(DB::raw("MONTH(published_at), YEAR(published_at)"))->distinct()->get();

Answer 2

To do this, you will at least need to turn the date into a month...
In SQL, it will look like this:
You just have to finish the query to suit your needs and convert it to Laravel syntax or use DB::RAW